topic Re: exclude values in set analysis in QlikView

exclude values in set analysis

sanjujeeboy — Sat, 16 Nov 2024 00:15:12 GMT

Hi All,

I have attached excel file fro my requirement.

My requirement is , for Unit 'NF' it should always Exclude Type 'COL' and rest of values it should consider.

I am using following expression in set analysis [sum({<Unit={'NF'},Type-={'COL'}>}Amount)] but somehow its not working.

its not taking values where type is not mentioned. how to achieve this output

output should be for FR,Amount - 659

US Amount 793

Country	Unit	Type	Amount
FR	NF		160
FR	NF		110
FR	NF	COL	138
FR	NF		105
FR	NF		175
FR	NF	Video	109
US	NF	COL	254
US	NF		148
US	NF		160
US	NF		110
US	NF		160
US	NF		110
US	NF	COL	138
US	NF		105

Re: exclude values in set analysis

Lisa_P — Mon, 27 Jul 2020 23:52:55 GMT

It is because your other values are null in the Type field. You can't select null values, so you also can't see them in set analysis.

If you load them as something other than null, this set analysis will work.

LOAD
[Country],
[Unit],
If(IsNull([Type]),'None', Type) as Type,
[Amount]
FROM [lib://Community/COL.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: exclude values in set analysis

Kushal_Chawda — Tue, 28 Jul 2020 00:22:27 GMT

try below expression

sum({$*1-$<Unit={'NF'},Type={'COL'}>}Amount)

Re: exclude values in set analysis

sanjujeeboy — Tue, 28 Jul 2020 13:25:15 GMT

Hi Kush,

This solution is working fine

can you explain the logic used? i mean what is $*1-$ doing?

Thanks

Re: exclude values in set analysis

Kushal_Chawda — Tue, 28 Jul 2020 14:44:23 GMT

Well I will break down is two separate part

$ * (1-$)

First $ considers the current selections into the account while second part evaluates the set expression and gives results that has not matched the set analysis condition. So entire part works as a combination of current selections with excluding set

Re: exclude values in set analysis

Kushal_Chawda — Tue, 28 Jul 2020 14:59:00 GMT

Just to note that If you have some other unit apart from 'NF' where you have Type value as 'COL' it will not work as expected. In that case you need to use below so for safer side use below expression. Further if you add more modifier you need to include that in aggr

sum({<Type-={'COL'}>}aggr(sum({$*1-$<Unit={'NF'},Type={'COL'}>}Amount),Unit,Type))

Re: exclude values in set analysis

sanjujeeboy — Tue, 28 Jul 2020 16:28:37 GMT

Thanks Kush. Really Appreciate!!!