topic Re: Set analysis to add selection to pivot table in App Development

Set analysis to add selection to pivot table

Lauri — Wed, 22 Aug 2018 02:42:03 GMT

I am trying to present, in a table, the percentage of patients who have the selected Condition AND each of the other possible Conditions. So, as displayed below, the user has selected 'Asthma' and therefore the table should display the percentage of all patients who have Asthma AND Other mental disorders, Asthma AND Depression, etc. (The percentages in my sample are incorrect.)

Is there set analysis to do this? Qlik doesn't accept this:

=Count({<[MedDX.Condition]=+p([MedDX.Condition]) >} distinct Recipient_Unique_ID)

/Count({<[MedDX.Condition]=+p([MedDX.Condition]) >} TOTAL distinct Recipient_Unique_ID)

Thanks for your help.

Re: Set analysis to add selection to pivot table

ogautier62 — Wed, 22 Aug 2018 06:47:04 GMT

Hi,

If I've well understood the model,

you could try something like this :

count({1 <Recipient_Unique_ID =P( {$}) >} distinct MedDX.Condition)

you take all patient selected by current selection ($)

and you count all deseases ( {1}) for those patients

regards

Re: Set analysis to add selection to pivot table

Lauri — Wed, 22 Aug 2018 13:49:06 GMT

Perfect! So simple, so elegant. Thank you, Olivier.

Is it also possible to hide the row for the Condition that the user selects? In the below example, the user has selected 'Obesity' so it is unnecessary to show it in the table (because of course 100% of patients with Obesity have Obesity!). I tried an IF statement in different ways, but all values go to zero or null.

Re: Set analysis to add selection to pivot table

ogautier62 — Wed, 22 Aug 2018 14:21:01 GMT

I didn't test,

but it could be :

count({1 <Recipient_Unique_ID =P( {$}),MedDX.Condition = E({$}) >} distinct MedDX.Condition)

to add exclusion on desease selected,

regards

good dev

Re: Set analysis to add selection to pivot table

Lauri — Wed, 22 Aug 2018 14:30:44 GMT

Bravo, you are correct again.

(I have trouble understanding why it works; I would expect the e() function to take the selection out of the calculation for all rows in the table.)

Re: Set analysis to add selection to pivot table

ogautier62 — Wed, 22 Aug 2018 14:40:06 GMT

because it's an AND with id = P($) so it takes the intersect and not all rows !