- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set Analysis Not Working Unless Selection is Made
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny.
Thanks, but that just made the second expression blank with or without an expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Which selection is causing this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not sure, but see if this works
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This worked!