Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
For some reason one of my set analysis expressions works without any values selected, while the other must have something selected. How can I modify the expression to show a value regardless of what is selected.
Sample Data:
MRN | HMHK_REFERRAL_DATE | Completed Office Visit |
1 | 4/12/2017 | |
1 | 4/25/2017 | |
1 | 5/9/2017 | |
3 | 4/14/2017 | |
3 | 2/21/2017 | |
3 | 5/2/2017 | |
3 | 7/11/2017 |
Expected Result:
MRN | HMHK_REFERRAL_DATE | Completed Office Visit |
1 | 4/12/2017 | 4/25/2017 |
3 | 4/14/2017 | 5/2/2017 |
Expressions
Referral Date = aggr(Min({$<HMHK_REFERRAL_DATE>} HMHK_REFERRAL_DATE), MRN)
Next Completed Office Visit (after referral date has passed): aggr(Min({$<[Completed Office Visit]={">=$(=aggr(Min({<HMHK_REFERRAL_DATE>} HMHK_REFERRAL_DATE), MRN))"}>} [Completed Office Visit]), MRN)
The second expression (Next Completed Office Visit) does not show a value until I select a MRN and is blank otherwise.
you can try to do it in script like below
Table:
LOAD * INLINE [
MRN, HMHK_REFERRAL_DATE, Completed Office Visit
1, 4/12/2017,
1, , 4/25/2017
1, , 5/9/2017
3, 4/14/2017,
3, , 2/21/2017
3, , 5/2/2017
3, , 7/11/2017
];
Left Join (Table)
LOAD MRN,
date(max(HMHK_REFERRAL_DATE)) as HMHK_REFERRAL_DATE_Final
Resident Table
Group by MRN;
Left Join (Table)
LOAD MRN,
date(min(if([Completed Office Visit]>HMHK_REFERRAL_DATE_Final,[Completed Office Visit]))) as [Completed Office Visit final]
Resident Table
Group by MRN;
create the table box like below
I guess try this
Dimension
MRN
Expression
Min({$<HMHK_REFERRAL_DATE>} HMHK_REFERRAL_DATE)
Min({$<HMHK_REFERRAL_DATE>} Aggr(If(Min({$<HMHK_REFERRAL_DATE>} TOTAL <MRN> [Completed Office Visit]) >= Only({$<HMHK_REFERRAL_DATE>} HMHK_REFERRAL_DATE), [Completed Office Visit]), MRN, [Completed Office Visit]))
Hi Sunny.
Thanks, but that just made the second expression blank with or without an expression
Can you try this
Min({$<HMHK_REFERRAL_DATE>} Aggr(If(Min({$<HMHK_REFERRAL_DATE>} TOTAL <MRN> HMHK_REFERRAL_DATE) <= Only({$<HMHK_REFERRAL_DATE>} [Completed Office Visit]), [Completed Office Visit]), MRN, [Completed Office Visit]))
Worked for me in the sample data
Nearly there! It worked, but now the second expression's data disappears when selections are made. Is this the way it has to be or can there be a tweak to allow it to respect selections too?
Which selection is causing this?
Not sure, but see if this works
I added a new column
=COUNT(DISTINCT {<[HMHK_REFERRAL_PLACED]={'1'}>} MRN)
This basically is a field that is in the load script that gives the patient a '1' if they have ever had a referral (Referral Date is not null). Thus, in the sample data, each of those patients has a 1 in this column. I was trying to filter out those patients who do not have a referral.
you can try to do it in script like below
Table:
LOAD * INLINE [
MRN, HMHK_REFERRAL_DATE, Completed Office Visit
1, 4/12/2017,
1, , 4/25/2017
1, , 5/9/2017
3, 4/14/2017,
3, , 2/21/2017
3, , 5/2/2017
3, , 7/11/2017
];
Left Join (Table)
LOAD MRN,
date(max(HMHK_REFERRAL_DATE)) as HMHK_REFERRAL_DATE_Final
Resident Table
Group by MRN;
Left Join (Table)
LOAD MRN,
date(min(if([Completed Office Visit]>HMHK_REFERRAL_DATE_Final,[Completed Office Visit]))) as [Completed Office Visit final]
Resident Table
Group by MRN;
create the table box like below
This worked!