Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MichaS
Contributor
Contributor

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

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

Labels (3)
2 Replies
pedrobergo
Employee
Employee

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
Contributor
Contributor
Author

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