Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Not applicable

Set analysis to filter all except one value

Hello all,

I have tried to Google but have not found a solution, or maybe I have not searched enough but I am trying to build an expression in Qlik Sense which does a sum but a user should not be able to filter on just one value.

Ex: Say I have an expression called SUM([Sales])

This is spread across countries like Spain, India, USA etc.

I am trying to create an expression wherein a user should be able to see the total SUM([Sales]) but when he/she clicks on USA, they should not be able to see the Sales for USA.

However, if the user clicks on Spain or India, they should be able to see it separately.

So basically I need maybe a set analysis expression which filters on all values except the value "USA" for the expression SUM([Sales]).

Thanks in advance.

Cheers.

SS.

1 Solution

Accepted Solutions
MVP
MVP

Re: Set analysis to filter all except one value

=IF(SubStringCount(GetFieldSelections(Country),'USA'),

(SUM({1<Country=, Year={'Current Year'}>}[Sales]) - SUM({1<Country=, Year={'Current Year'}>}[Revenue]) - SUM({1<Country=, Year={'Current Year'}>}[Discounts])),

(SUM({<Year={'Current Year'}>}[Sales]) - SUM({<Year={'Current Year'}>}[Revenue]) - SUM({<Year={'Current Year'}>}[Discounts])))

View solution in original post

4 Replies
MVP
MVP

Re: Set analysis to filter all except one value

May be something like this?

=IF(SubStringCount(GetFieldSelections(Country),'USA'),SUM({1<Country>}Sales),SUM(Sales))

Not applicable

Re: Set analysis to filter all except one value

Hey Manish,

Thanks for helping. But I cannot fit this into my expression because of nested aggregations.

My expression in itself is complex on which I am trying to achieve what I said.

My expression is not as simple as SUM([Sales]). It is more like

(SUM({<Year={'Current Year'}>}[Sales]) - SUM({<Year={'Current Year'}>}[Revenue]) - SUM({<Year={'Current Year'}>}[Discounts]))

So I cannot exactly fit in the above expression you specified as it becomes nested aggregations which is not allowed.

Do you have any other suggestions?

Thanks.

MVP
MVP

Re: Set analysis to filter all except one value

=IF(SubStringCount(GetFieldSelections(Country),'USA'),

(SUM({1<Country=, Year={'Current Year'}>}[Sales]) - SUM({1<Country=, Year={'Current Year'}>}[Revenue]) - SUM({1<Country=, Year={'Current Year'}>}[Discounts])),

(SUM({<Year={'Current Year'}>}[Sales]) - SUM({<Year={'Current Year'}>}[Revenue]) - SUM({<Year={'Current Year'}>}[Discounts])))

View solution in original post

Not applicable

Re: Set analysis to filter all except one value

Perfect. Thanks for the idea.