Need help in building an expression

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

Hi All,

I am facing an issue in building an expression in Qlik. PFB the details for the same.

The Raw data
patid	Type	cardiac	chemo	diabetes	neuro	preg
1		1	0	1	1	1
2		1	0	0	0	1
3		0	1	1	0	1
4		1	1	0	0	1
5		0	1	1	0	1

These Types need to be added in the include and exclude filter

Type	Include/Exclude
cardiac	Exclude
chemo	Include
diabetes	Include
neuro	Exclude
preg	Exclude

If selections are made in exclude filter, '0' values are selected corresponding to that flag

If selections are made in include filter, '1' values are selected corresponding to that flag

Case 1: When the user selects chemo from the Include filter

Type	IncExc	patient change when flag removed
cardiac	Ex	Not Applicable
chemo	In	2
diabetes	In	Not Applicable
neuro	Ex	Not Applicable
preg	Ex	Not Applicable

patid	Type	cardiac	chemo	diabetes	neuro	preg
1		1	0	1	1	1
2		1	0	0	0	1
3		0	1	1	0	1
4		1	1	0	0	1
5		0	1	1	0	1

For all other Type except the one selected from filter, the column will show "Not Applicable"'. Here in this case, the column will show "2" for selected type chemo because if this filter is cleared, 2 patid (1 & 2) will get added.

Case 2: When the user selects chemo from Include filter and cardiac from exclude filter

Type	IncExc	patient change when flag removed
cardiac	Ex	1
chemo	In	0
diabetes	In	Not Applicable
neuro	Ex	Not Applicable
preg	Ex	Not Applicable

patid	Type	cardiac	chemo	diabetes	neuro	preg
1		1	0	1	1	1
2		1	0	0	0	1
3		0	1	1	0	1
4		1	1	0	0	1
5		0	1	1	0	1

For all other Type except the selected ones from include & exclude filter, the column will show "Not Applicable"'. Here in this case, the column will show "0" for selected type chemo because if this filter is cleared, no new patient will get added as we already have both the patid(3,5) selected. For selected type cardiac, if this filter is cleared, 1 patid (which is 4) will get added, hence value "1" for cardiac in the table

'patient change when flag removed' needs to be created in the application attached

Thanks in advance

Regards,

Chhavi

Re: Need help in building an expression

sasikanth — Wed, 04 Jul 2018 10:46:34 GMT

HI,

I did not understand the second scenario , try below exp

=if(Type=GetFieldSelections(Include),Count({<Flag={'0'}>}Flag),

if(Type=GetFieldSelections(Exclude),Count({<Flag={'1'}>}Flag),

'Not Applicable')

Re: Need help in building an expression

amonjaras — Wed, 04 Jul 2018 15:21:28 GMT

I am kind of in a hurry right now, but this looks like one of those very rare situations where you would need to use the "previous selection" state via a set analysis expression: {$1}

Check the "Identifiers" section here: https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-analysis-expr…

I'll give it a go later; in the meantime, I hope this helps you.

Re: Need help in building an expression

chhavi376 — Thu, 05 Jul 2018 07:31:45 GMT

Hey,

This will be valid only for that flag which you just selected.

For the already selected flags it will give wrong results.

Regards,

Chhavi

topic Re: Need help in building an expression in QlikView

Need help in building an expression

Re: Need help in building an expression

Re: Need help in building an expression

Re: Need help in building an expression