topic Re: How to perform set expression aggregations ignoring filters in QlikView

How to perform set expression aggregations ignoring filters

TranquilToucan — Fri, 02 Aug 2024 11:51:14 GMT

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!

Re: How to perform set expression aggregations ignoring filters

Kushal_Chawda — Fri, 02 Aug 2024 14:12:18 GMT

@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

Re: How to perform set expression aggregations ignoring filters

TranquilToucan — Fri, 02 Aug 2024 14:33:53 GMT

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.

Re: How to perform set expression aggregations ignoring filters

Kushal_Chawda — Fri, 02 Aug 2024 14:34:13 GMT

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

Re: How to perform set expression aggregations ignoring filters

TranquilToucan — Fri, 02 Aug 2024 14:43:51 GMT

@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?

Re: How to perform set expression aggregations ignoring filters

Kushal_Chawda — Fri, 02 Aug 2024 14:45:38 GMT

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

Re: How to perform set expression aggregations ignoring filters

TranquilToucan — Fri, 02 Aug 2024 16:53:31 GMT

@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.