topic Re: Set analysis with alternate state in QlikView

Set analysis with alternate state

baylor2016 — Thu, 01 Feb 2018 18:59:50 GMT

I am trying to generate a dynamic set analysis string based upon users' selections. The two list boxes in fact are the same data with different field names, and one is in NOT state. Is this ok to do it? I know data in Qlik are linked. A set analysis string for default state (it might not be necessary) and Not state are generated respectively and merged in one used in default state.

It looks like the generated set analysis string is correct, but the numbers returned is wrong. I am expecting the following expression returns 3, but it return 4 meaning the E() function is not working. Please advise. Thanks

=count(distinct {(<PatientID=p({<[Disease]={'Diabetes'}>} PatientID)>)*(<PatientID=E({<[Disease]={'CVD'}>} PatientID)>)} PatientID)

Sample project is attached.

Longmatch

Re: Set analysis with alternate state

sunny_talwar — Thu, 01 Feb 2018 19:13:35 GMT

May be this

=Count(DISTINCT {<PatientID=p({<[Disease]={'Diabetes'}>} PatientID)-p({NOT<[Disease]={'CVD'}>} PatientID)>} PatientID)

Re: Set analysis with alternate state

baylor2016 — Thu, 01 Feb 2018 19:51:09 GMT

Thank you for your help. It works fine.

If multiple conditions are selected in both list boxes (one in inherited state and the other in NOT state), I thought the following expression should work, but it doesn't. What I did wrong?

Thanks

=Count(distinct

{

PatientID=

p({

<[Disease]={'Diabetes'}>+

<[Disease]={'Depression'}>+

<[Disease]={'CHF'}>

} PatientID)

p({NOT

<[Disease]={'Cancer'}>+

NOT<[Disease]={'COPD'}>

} PatientID)

}

PatientID)

Re: Set analysis with alternate state

sunny_talwar — Thu, 01 Feb 2018 19:53:37 GMT

I believe this should work

=Count(distinct

{<PatientID=

p({<[Disease]={'Diabetes', 'Depression', 'CHF'}>} PatientID)

p({NOT<[Disease]={'Cancer', 'COPD'}>} PatientID)>}

PatientID)

Re: Set analysis with alternate state

baylor2016 — Thu, 01 Feb 2018 20:09:36 GMT

Logically this one is simpler. I will change the dynamic part to make it work. Thanks!!!

Do I need explicitly create set analysis string for Inherited state? I tried to use

=Count(distinct

{<PatientID=

p({NOT<[Disease]={'Cancer', 'COPD'}>} PatientID)>}

PatientID)

It returns zero.

Thanks.

Re: Set analysis with alternate state

sunny_talwar — Thu, 01 Feb 2018 20:13:46 GMT

With selection or without selections?

Re: Set analysis with alternate state

baylor2016 — Thu, 01 Feb 2018 20:15:35 GMT

With selection.

Re: Set analysis with alternate state

sunny_talwar — Thu, 01 Feb 2018 20:32:29 GMT

May be like this

=Count(distinct

{<PatientID = p({<Disease = p(Disease)>})

p({NOT<[Disease]={'Cancer', 'COPD'}>} PatientID)>}

PatientID)

and to make the second part dynamic, may be this

=Count(DISTINCT {<PatientID = p({<Disease = p(Disease)>})-p({NOT<[Disease]=p(Disease)>} PatientID)>} PatientID)

=Count(DISTINCT {<PatientID = p({<Disease = p(Disease)>})-p({NOT<[Disease]=p({NOT}Disease)>} PatientID)>} PatientID)

Re: Set analysis with alternate state

baylor2016 — Thu, 01 Feb 2018 20:34:35 GMT

I will try it and let you know the results. Thanks

Re: Set analysis with alternate state

sunny_talwar — Thu, 01 Feb 2018 20:35:23 GMT

Attaching an image and a sample

Re: Set analysis with alternate state

baylor2016 — Thu, 01 Feb 2018 20:38:43 GMT

Genius! Your solution is much simpler, and more efficient!!! Thank a billion.

Re: Set analysis with alternate state

sunny_talwar — Thu, 01 Feb 2018 20:39:38 GMT

No problem at all

Re: Set analysis with alternate state

baylor2016 — Thu, 01 Feb 2018 21:13:58 GMT

One more question, Is this possible to filter conditions in NOT state based upon selections in the Inherited state? For example, when Cancer (Only patient 1 and 3 have cancer) is selected, I only want all other conditions (Diabetes and Hypertension) show up. If there is a easy solution for this, please let me know. Thanks

Re: Set analysis with alternate state

sunny_talwar — Fri, 02 Feb 2018 12:35:30 GMT

Not sure I understand, would you be able to elaborate on your point above?

Re: Set analysis with alternate state

baylor2016 — Fri, 02 Feb 2018 14:00:26 GMT

There are two list boxes for Disease - inherited date and NOT state. Right now all the possible diseases are listed in NOT state. What I want is to limit the diseases in the listbox of NOT state to only include the related disease. Only patient 1 and 3 have Cancer, when Cancer is selected, I only want patient 1's and 3's other conditions (Hypertension and Diabetes) are listed in NOT state listbox. I hope you understand my point now. Thanks

Re: Set analysis with alternate state

sunny_talwar — Fri, 02 Feb 2018 14:29:28 GMT

Like this?

Used this expression for the second list box

=Aggr(Only({1<PatientID = p({$}PatientID)>} Disease), Disease)

Re: Set analysis with alternate state

baylor2016 — Fri, 02 Feb 2018 14:31:50 GMT

This is exactly what I want. Thanks.

Re: Set analysis with alternate state

sunny_talwar — Fri, 02 Feb 2018 14:33:10 GMT

Awesome

Re: Set analysis with alternate state

baylor2016 — Fri, 09 Feb 2018 16:26:51 GMT

Hi Sunny,

Could you please look into this post? Somehow the variable for set analysis string is not working. Please advice.

Thanks

Longmatch