Set analysis on two levels

[Lauri]

I have hit a wall with trying to solve this... The goal: Create a KPI that counts the number of patients whose most recent blood test had a specific result.

My data has fields named ObservationDate, [HbA1C Group], Patient.

I need to find each patient's most recent ObservationDate, then determine if the value of [HbA1C Group] on that date was '5.7 to 6.4'. If yes, then include the patient in the count.

This doesn't work because it gets the max date for the entire population, not each patient:

=Count({<[HbA1C Group]={'5.7 to 6.4'}, ObservationDate={"$(=max(ObservationDate))"}>} distinct Patient))

I've tried enclosing it in Aggr in various ways, with and without an If() function. No luck.

Thanks for any advice.

[mikaelsc · Accepted solution]

i'd suggest you create/calculate a field in your datamodel "_latest_test_result" 

and use it in your set analysis instead. (indeed, expressions are evaluated once per chart - not per dimension)

(<_latest_test_result={1}>...)

Observations: 

..

..

latestTestResults: 

load

PatientID,

max(ObservationDate)                                                           as latestDate,

firstsortedvalue(ObservationID,-ObservationDate)   as ObservationID

1                                                                                                        as _latest_test_result

resident Observations

group by PatientID;

drop field PatientID from latestTestResults;  OR left join the entire latestTestResultTable? 

[Lauri]

Thanks! That makes good sense and works in my situation. How would you handle it if you wanted it to be dynamic - so the user could choose the year within which the latest ObservationDate had to be?

[Lauri]

In case others come looking, here is what I ended up doing for the dynamic situation. The earlier solution was good for counting patients based on their most recent result (ever!). But I needed to have a different set of results for each of many time periods.

My load script creates an As-Of table containing every month. I created a bar chart with AsOfMonth as the dimension. The measure is this:

Count({<IsRolling12={1}, AsOfMonth=, [Diabetic Ever]={'Yes'}, 
 Patient={"=FirstSortedValue({<ObservationName={'HEMOGLOBIN A1C'}>} 
ObservationValue, -ObservationDate)>9"}>} distinct Patient)

The valuable part here is the FirstSortedValue function:

Patient={"=FirstSortedValue({<ObservationName={'HEMOGLOBIN A1C'}>} 
ObservationValue, -ObservationDate)>9"}

It returns members of the Patient dimension whose most recent ObservationValue  is greater than 9. The other set analysis statements limit it to a rolling 12 months, among patients diagnosed with diabetes.

So no need for Aggr.