I'm able to represent graphically what I'm after but I also want the ratio it represents and I'm having trouble generating that calculated field due to aggregate/non-aggregate LOD.
My data looks something like this
Participant-CustContact IDStart TimeA
1456
Discrete m/dd/YY x:xx:xxA2587Discrete m/dd/YY x:xx:xxA3697Discrete m/dd/YY x:xx:xxB8965Discrete m/dd/YY x:xx:xxC7894Discrete m/dd/YY x:xx:xxD2469Discrete m/dd/YY x:xx:xxD3674Discrete m/dd/YY x:xx:xx
So far to count the number of Contacts per Participant or to get the degree of contact I've used 'FIXED CompleteID':
{FIXED [Participant Phone Number], [Segment Start Time]:
COUNTD([CompleteID])}
And I've gotten as far as distinguishing =1 from >1
IF SUM([FIXED CompleteID])=1
THEN 'Resolved'
ELSEIF SUM([FIXED CompleteID])>1
THEN 'Unresolved'
ELSE 'Null'
END
but I am somehow stuck when it comes to writing a calculated field that takes #Contact ID where degree =1/SUM(All Contact IDs).
In this example 2/7; .29
Count-Degree#Contact IDCustomer(s)
1
2
B,C22D33A40*.........
Have I complicated things with the FIXED expression unnecessarily, would I be better served by a window_count? What do I need to do to get that degree=1 ratio to all Contact IDs? Thanks.
Best regards,
Chad
hi Chad,
Just looking at some unanswered questions and came across this one...
I might need a bit more detail to get your exact solution, but I think you can do this using your current logic
COUNTD(IIF([FIXED CompleteID]=1,[Contact ID],NULL)/{COUNTD([Contact ID])}
by using [Contact ID] and Null the COUNTD function will only countd the Contact IDs (it doesn't count NULLs)
btw {COUNTD([Contact ID])} is just short hand for FIXED when there are no Dimensions for the LoD. If you wanted this by Week, you'd do
COUNTD(IIF([FIXED CompleteID]=1,[Contact ID],NULL)/{FIXED [Date]: COUNTD([Contact ID])}
Hope this helps, and makes sense, but please post back if not.
