Sum with one (dynamic) pre-defined dimension and another dimension excluded at the same time

[MichaS]

Hello community,

I am building up my knowledge on Qlik Sense and get stuck with a calculation that excludes/includes selected fields at the same time and would very much appreciate your help:

I want a total sum of revenue measure Rev for the latest dim1 qtr and ignoring any selections made in dim2 Reseller.

Goal is to compare the current Reseller revenue with the total revenue.

Now if I want to see the total revenue ignoring the Reseller it works like this:

=sum( {<Reseller>} Rev )

And if I want to consider only the last quarter independent from multiple qtr selections it works like this:

=sum({<qtr={'$(=Right(GetFieldSelections(qtr),6))'}>}Rev)

(the quarter has a special 6-digit format like 'FY23Q1', that's why I need to apply Right function get the latest quarter out of the selection) 

But how can I combine these 2 rules into one? Struggling with the syntax...

Thanks a lot in advance,

Michael

[pedrobergo]

Hi @MichaS 

To ignore any sellections of Reseller dim combining with specific filtering on Set Analysis, use this:

=sum({<Reseller=,qtr={'$(=Right(GetFieldSelections(qtr),6))'}>}Rev)

Look examples on https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalys... 

[],

Pedro

[MichaS]

The = without anything does the trick...GREAT ! Thanks a lot. Worked.