topic Re: Set Analysis Not Working Unless Selection is Made in QlikView

Set Analysis Not Working Unless Selection is Made

proctors — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Set Analysis Not Working Unless Selection is Made

sunny_talwar — Tue, 18 Jul 2017 12:37:09 GMT

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

Re: Set Analysis Not Working Unless Selection is Made

proctors — Tue, 18 Jul 2017 12:41:57 GMT

Hi Sunny.

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

Re: Set Analysis Not Working Unless Selection is Made

sunny_talwar — Tue, 18 Jul 2017 12:49:13 GMT

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

Re: Set Analysis Not Working Unless Selection is Made

proctors — Tue, 18 Jul 2017 12:55:08 GMT

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?

Re: Set Analysis Not Working Unless Selection is Made

sunny_talwar — Tue, 18 Jul 2017 13:06:42 GMT

Which selection is causing this?

Re: Set Analysis Not Working Unless Selection is Made

sunny_talwar — Tue, 18 Jul 2017 13:08:44 GMT

Not sure, but see if this works

Re: Set Analysis Not Working Unless Selection is Made

proctors — Tue, 18 Jul 2017 13:09:16 GMT

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.

Re: Set Analysis Not Working Unless Selection is Made

Kushal_Chawda — Tue, 18 Jul 2017 13:12:30 GMT

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

Re: Set Analysis Not Working Unless Selection is Made

proctors — Tue, 18 Jul 2017 15:05:39 GMT

This worked!