Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an example QVW attached. I am trying to create a variable expression to count a number of "assessments" However each "resident" has multiple "assessments", I only want to count the one for each resident with the most current date. I have tried using the max function and firstsortedvalue but it is not working.
Resident1 AssessmentA1 1/1/2011
Resident1 AssessmentB1 4/1/2011
Resident2 AssessmentA2 4/1/2011
Resident2 AssessmentB2 1/1/2011
In this case I would only have a count of 2 for AssessmentB1 and AssessmentA2.
Check out the attachment. I added a new expression.
Regards,
Kiran.
That looks great in the chart, however, when I tried to create a count of those assessments outside of the chart, it is not working. I updated the attachment.
If your expected result is 3 then try below expression in the textbox:
=sum(aggr(if(ARD_Reference_Calendar=date(MAX(total <ResidentID> ARD_Reference_Calendar)),1,0),ResidentID,ARD_Reference_Calendar))
Works for me
-Kiran.
That did work but here is a concern. Is this saying that the date of the record has to be equal to the max date of all the records? So if I were to change my example to be:
Resident1 AssessmentA1 1/1/2011
Resident1 AssessmentB1 3/1/2011
Resident2 AssessmentA2 4/1/2011
Resident2 AssessmentB2 1/1/2011
I would still want a count of 2; AssessmentB1 and AssessmentA2. I will also likely need to to add other parameters into my expression to filter down data even more. I can't figure out where to fit these in.
It does work that way. Let us know what exactly you want in the chart.
Regards,
Kiran.
Resident1 AssessmentA1 1/1/2011 X A
Resident1 AssessmentB1 3/1/2011 Y B
Resident2 AssessmentA2 4/1/2011 X A
Resident2 AssessmentB2 1/1/2011 Y A
Resident3 AssessmentA3 2/1/2011 Y B
Resident3 AssessmentB3 5/1/2011 X A
Resident4 AssessmentA4 3/1/2011 X B
Resident5 AssessmentA5 1/1/2011 X A
I need to count one assessment per resident being the most recent assessment per resident and where parameter1=X and parameter2=A.
The result with the above example should be 3: assessmentA2, B3, and A5.
Thanks in advance!
Loaded your data and got the result. Hopefully I understand your requirement.
Regards,
Kiran.