Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
110
Creator
Creator

Set Analysis using total fails if Categories are filtered in Pivot Chart.

Stupid question,  and a picture is probably than a wordy explaination.

 

So I have three people, and they have numbers for each Category, and there's also a flag.

 

As you can see I can get totals in the first table,  but I want to get the totals in the next table ( Filtered on Cat where Flag =0)

If I want to include ALL numbers and ignore the 'Flag' part ( in this case Alan would have 174 )  -  how can I di that?

I tried ignoring the flag field in the set analysis, but it still excludes the ones I've filtered out 

=Sum(Total <Name> {<Flag=>}Number)

Category expression :

=if(Flag=0,Cat)

 

110_0-1703248036669.png

 

Labels (1)
3 Replies
rubenmarin

Hi, you can try with calculated dimension: =Aggr(If(Flag=0,Cat),Flag,Cat)

And this expression: Sum(Total <Name> {<Cat=,Flag=>}Number)

110
Creator
Creator
Author

Thanks @rubenmarin   -  I tried that, unfortunately I get the same result. 

It'd be great if Qlik had a simpler way to either  (a) apply set-analysis to a sheet or an object   or (b) to separate visual elements ( columns ) from the data. I.E.  Hide columns A from being visible, but still incorporate the numbers.

110_0-1703254671059.png

 

rubenmarin

Hi, Number goes off when the option to exclude nulls is selected. A workaround, Cat as dimension but excluding them in the expression. PFA.