How to perform set expression aggregations ignoring filters

[TranquilToucan]

Hello together,

this is my first post and I hope it is in the right location. I have a question that revolves around calculation logic inside Qlik measures that rely on set expressions ignoring filter choices:

In my dashboard, I need to create a measure that calculates the percentage difference in revenues between last year ("LY") and current year ("CY"), but only based on those customers who have had at least some amount (smaller or larger than 0) of bonus (either type "1" or "2") within the last or current year. Please note that the measure should take the role of a "fixed" KPI, ignoring all filter selections etc. The measure in its current state looks as follows:

(

    Sum({

        1 <[Customer] = P({<

            [Customer] = E({<

                [Bonus 1 CY] = {"=0"},

                [Bonus 2 CY] = {"=0"}

            >})

        >})

    >} [Revenue CY])

    /

    Sum({

        1 <[Customer] = P({<

            [Customer] = E({<

                [Bonus 1 LY] = {"=0"},

                [Bonus 2 LY] = {"=0"}

            >})

        >})

    >} [Revenue LY])

) – 1

 I already tried using set expression and "1" to ignore filters, however the KPI still changes whenever I filter for specific customers etc. What could be the reason for this?

Thanks in advance for your help!

[Kushal_Chawda]

@TranquilToucan  try to write set modifier inside inner set within p()

(

    Sum({

        1 <[Customer] = P({1<

            [Customer] = E({1<

                [Bonus 1 CY] = {"=0"},

                [Bonus 2 CY] = {"=0"}

            >})

        >})

    >} [Revenue CY])

    /

    Sum({

        1 <[Customer] = P({1<

            [Customer] = E({1<

                [Bonus 1 LY] = {"=0"},

                [Bonus 2 LY] = {"=0"}

            >})

        >})

    >} [Revenue LY])

) – 1

[TranquilToucan]

Thanks a lot Kushal!

The only problem is now, that it is always the "entire" revenue which is displayed, regardless of any filters. This means that we already solved the issue with deliberately ignoring the filters, however the limitation based on bonuses is now also not performed, I guess because everything is now blocked from filtering within the measure.

Any ideas on how we could solve this second issue? I will also try some more approaches in the meantime.

Edit: The problem is only that if I remove one of the set modifiers "1" within the measure, the
measure is again susceptible to filtering.

[Kushal_Chawda]

@TranquilToucan  you can remove set modifier from e() function

[TranquilToucan]

@Kushal_Chawda I have removed the set modifier inside the e() function, however the measure result does not change. I think this is because the other "1" modifiers enforce to consider all customers, regardless of selected filter choices. Maybe there is another way besides set expressions?

[Kushal_Chawda]

@TranquilToucan  how would you like to apply filter of bonus in set?

[TranquilToucan]

@Kushal_Chawda The bonuses themselves need not to be filtered. I just need to calculate the total revenue of all customers who have at least "some" amount of bonus (a bonus larger or smaller than 0) belonging to them. And this calculation should be the same regardless of the user filtering for single customers or products etc. Easier said, whenever a customer has a bonus larger or smaller than 0 (a boolean condition so to say), its entire revenue should be considered in the revenue calculation, regardless of any active filters set by the user etc. I just need the fix revenue ratio number ("CY" vs. "LY") for another measure.