Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sanjujeeboy
Creator
Creator

exclude values in set analysis

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

CountryUnitTypeAmount
FRNF 160
FRNF 110
FRNFCOL138
FRNF 105
FRNF 175
FRNFVideo109
USNFCOL254
USNF 148
USNF 160
USNF 110
USNF 160
USNF 110
USNFCOL138
USNF 105
Labels (4)
1 Solution

Accepted Solutions
Kushal_Chawda

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

 

View solution in original post

6 Replies
Lisa_P
Employee
Employee

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.

eg

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

Kushal_Chawda

try below expression

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

Hi Kush,

This solution is working fine

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

Thanks

Kushal_Chawda

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 

Kushal_Chawda

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

 

sanjujeeboy
Creator
Creator
Author

Thanks Kush. Really Appreciate!!!