Grand Total and custom sub total not changed by app filter

[manish_gupta]

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

[JandreKillianRIC]

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/Inter...

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

[manish_gupta]

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

[manish_gupta]

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.

[diegozecchini]

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.

[manish_gupta]

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. 

[JandreKillianRIC · Accepted solution]

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

[diegozecchini]

@JandreKillianRIC should have answered you I find it solution perfect

[manish_gupta]

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. 

[JandreKillianRIC]

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