Set analysis to filter all except one value

[#1]

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.

[MK_QSL]

May be something like this?

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

[#3]

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.

[MK_QSL · Accepted solution]

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

[#5]

Perfect. Thanks for the idea.