My question keeps getting more complex...
we are trying to count the number of patients who receive >500 calories in their FIRST outpatient visit where the OUTPT_CALDENS field is not null (sometimes patients come in for their first outpatient visit and do not receive any calories).
here's some sample data:
PAT_MRN_ID DATE_OUTPT OUTPT_CALDENS
1 jan 1 300
1 jan 7 500
1 jan-27 700
2 march 17 650
2 march 29 400
2 april 5 325
2 april 7 800
3 jan 10 900
3 jan 14 1000
the correct count the above situation should be 2. There are two patients (ID 2 and 3 who receive >500 calories in their FIRST outpatient visit.
Here is the best proposed answer so far from Gysbert Wassenaar:
=COUNT(DISTINCT {1<[DATE_OUTPT]={"=[DATE_OUTPT]=aggr(Min([DATE_OUTPT]),PAT_MRN_ID)" },[OUTPT_CALDENS] = {">=500"}>} PAT_MRN_ID)
This works well. EXCEPT, we now realize that some patients do not receive calories on their first outpatient visit. So, we need to add a parameter that says "Minimum outpatient visit date WHERE OUTPT_CALDENS is not null"... and then start to count all thos with >500 calories...
ANY IDEAS????
