Set Analysis Not Working Unless Selection is Made

[proctors]

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.

[sunny_talwar]

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]))

[proctors]

Hi Sunny.

Thanks, but that just made the second expression blank with or without an expression

[sunny_talwar]

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

[proctors]

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?

[sunny_talwar]

Which selection is causing this?

[sunny_talwar]

Not sure, but see if this works

[proctors]

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.

[Kushal_Chawda · Accepted solution]

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

[proctors]

This worked!