topic Re: Grand Total and custom sub total not changed by app filter in App Development

Grand Total and custom sub total not changed by app filter

manish_gupta — Fri, 07 Feb 2025 00:49:14 GMT

Hi Qlik Community,

We have a requirement where we need to design an app in such a way that Grand Total and custom sub total not changed by app filters. For example, I have below hierarchy,

Region----->State----->Store

By Default this is how app would look like

Now when someone select a state from State filter, it would change the data in table including subtotal and grand total, like below

But we have requirement that subtotal for level above the selected filter shouldn't change and show the data as below

I am looking for ideas to implement this solution. Also we have more then 20+ KPIs so any solution which is easy to implement, flexible and extendable would be great for maintenance purpose.

Thank you

Re: Grand Total and custom sub total not changed by app filter

JandreKillianRIC — Fri, 07 Feb 2025 08:05:11 GMT

Hi @manish_gupta

I would use the dimensionality() function to accomplish this.

https://help.qlik.com/en-US/sense/November2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecordFunctions/dimensionality.htm

My whole table

Applying a filter only on "One State"

Here is my measure

IF(Dimensionality() = 0, Sum(Total {<State = >} Sales), IF(Dimensionality() = 1, Sum(Total <Region> {<State = , Region = P(Region)>} Sales), IF(Dimensionality() = 2, Sum(Total <Region, State> Sales))))

Let me know
Regards Jandre

Re: Grand Total and custom sub total not changed by app filter

manish_gupta — Fri, 07 Feb 2025 17:18:14 GMT

Thank you @JandreKillianRIC . Let me try this and see if it works for all the scenarios.

Re: Grand Total and custom sub total not changed by app filter

manish_gupta — Fri, 07 Feb 2025 20:33:59 GMT

Hi @JandreKillianRIC

I tried to replicate what you have but running into 2 issues

1. When I select a state, totals are correct but I can see other state in same region also displayed.

2. When I select a region, subtotals are changed to only that region. I need solution to work across different selection.

Let me know your thoughts. I attached qvf file for your reference.

Re: Grand Total and custom sub total not changed by app filter

diegozecchini — Sat, 08 Feb 2025 15:24:48 GMT

Hi!
You can create a Pivot with the hierarchy Region → State → Store.
Then modify the Sales Expression:
Use Sum({1} Sales) for Grand Total. ({1} ensures that the total sales value is not affected by any filters)
Use Sum({<State=>} Sales) for Region-level subtotal. (<State=> removes filtering at the State level while allowing other filters (like Store) to work)
Use Sum({<Store=>} Sales) for State-level subtotal. (same above)

Then you can test the filters:
When selecting a State, check if the Region subtotal remains the same.
When selecting a Store, check if the State subtotal remains the same.

Re: Grand Total and custom sub total not changed by app filter

manish_gupta — Sun, 09 Feb 2025 02:05:56 GMT

Hi @diegozecchini

I tried your solution but its not working the way I wanted. As you can see below, I don't want to see the highlighted part below

Let me know if any other solution.

Re: Grand Total and custom sub total not changed by app filter

JandreKillianRIC — Mon, 10 Feb 2025 05:55:18 GMT

Hi @manish_gupta

For Number 1. Just remove showing zero values

For number 2, Just change the measure to this

IF(Dimensionality() = 0, Sum(Total {<State = , Region = >} [Sales $]), IF(Dimensionality() = 1, Sum(Total <Region> {<State = , Region = P(Region)>} [Sales $]), IF(Dimensionality() = 2, Sum(Total <Region, State> [Sales $]))))

Regards Jandre

Re: Grand Total and custom sub total not changed by app filter

diegozecchini — Mon, 10 Feb 2025 13:32:50 GMT

@JandreKillianRIC should have answered you I find it solution perfect

Re: Grand Total and custom sub total not changed by app filter

manish_gupta — Tue, 11 Feb 2025 02:20:59 GMT

Hi @JandreKillianRIC

This works almost for all cases 🙂 but we have some case where 0 is expected for some KPIs so is there a way to achieve the solution without checking 'remove 0 values' .

Also , is there any way to convert this into a variable so that I can use this into multiple KPIs without rewriting the same thing again and again.

Thank you.

Re: Grand Total and custom sub total not changed by app filter

JandreKillianRIC — Tue, 11 Feb 2025 06:32:50 GMT

Hi @manish_gupta
Regarding the "some" cases where the 0 should show, I would flag these in the back end and give it a value of 0.000001 - This way it will show 0 in the front end even when checking Remove 0 Values (As this is technically not 0)

If all the KPI's that you are showing is pivot tables then the only issue I can see is the levels. Currently you have Region and State - If the others have more / less the Dimensionality might need to be adjust per pivot table. Hope this answers your question.

Regards Jandre

Re: Grand Total and custom sub total not changed by app filter

Qrishna — Wed, 12 Feb 2025 19:30:47 GMT

sample data please?

Re: Grand Total and custom sub total not changed by app filter

manish_gupta — Thu, 13 Feb 2025 15:48:09 GMT

Hi @JandreKillianRIC

I have the same levels but multiple KPIs so wanted to check if there is a way to convert your expression into a variable and re use instead of writing the same things again and again.

Re: Grand Total and custom sub total not changed by app filter

manish_gupta — Thu, 13 Feb 2025 15:54:36 GMT

PFA