Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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:

   

MRNHMHK_REFERRAL_DATECompleted Office Visit
14/12/2017
1 4/25/2017
1 5/9/2017
34/14/2017
3 2/21/2017
3 5/2/2017
3 7/11/2017

Expected Result:

   

MRNHMHK_REFERRAL_DATECompleted Office Visit
14/12/20174/25/2017
34/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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Set Analysis Not Working Unless Selection is Made

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

Capture.JPG

View solution in original post

9 Replies
Highlighted

Re: Set Analysis Not Working Unless Selection is Made

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

Highlighted
Creator
Creator

Re: Set Analysis Not Working Unless Selection is Made

Hi Sunny.

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

Highlighted

Re: Set Analysis Not Working Unless Selection is Made

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

Capture.PNG

Highlighted
Creator
Creator

Re: Set Analysis Not Working Unless Selection is Made

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?

Highlighted

Re: Set Analysis Not Working Unless Selection is Made

Which selection is causing this?

Highlighted

Re: Set Analysis Not Working Unless Selection is Made

Not sure, but see if this works

Highlighted
Creator
Creator

Re: Set Analysis Not Working Unless Selection is Made

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.

Highlighted
MVP
MVP

Re: Set Analysis Not Working Unless Selection is Made

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

Capture.JPG

View solution in original post

Highlighted
Creator
Creator

Re: Set Analysis Not Working Unless Selection is Made

This worked!