Select the latest dated record for each group

[#1]

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.

[#2]

Check out the attachment. I added a new expression.

Regards,

Kiran.

[#3]

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.

[#4]

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.

[#5]

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.

[#6]

It does work that way. Let us know what exactly you want in the chart.

Regards,

Kiran.

[#7]

                                               Date           Parameter1     Parameter2

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!

[#8]

Loaded your data and got the result. Hopefully I understand your requirement.

Regards,

Kiran.