How to avoid supressed null values to be considerated in the total of the other dimension?

[#1]

Hi Have a pivot table that shows a total value of 700 when expanded and the value of 1000 when collapsed. How can I have the same value when collapsed of expanded?

I Have three simple tables as show in the first image. Note that not all the regions include all the cities. Then I have a very simple pivot table (see second image). In the dimesion of Region i Check the option supress when value is null. Finally when I collapse this simple pivot table I get a diferent total. How Can I avoid this behaivor?

Thanks in advance for your help.

I'm attaching my qlikview file.

[swuehl]

Neither 700 nor 1000 is correct, right?

The total is calculated as expression total and I believe you should use sum of rows using advanced aggregation:

=sum(aggr(sum(Sales),Country,Region,City))

Use above as expression, this results in 800, in any collapsing state.

Hope this helps,

Stefan

[#3]

swuehl: I Change the formula but now when complete expanded the value is 800 and whe is completed collapsed the value is 1100...

Thanks in advance for your help...

[swuehl]

I am sorry, my expression is not working as I thought it would (Country total is 1100)

Hm, IMHO your data model is a bit unusual, shouldn't regions be linked to countries in country table?

If above is correct, you indeed get different totals depending if you total on country or region level, don't you?

[#5]

Thank you swuehl for your help. I try changing the model and is the same problem... I want to have the same total when the pivot table is collapsed or expanded...Is logical for the user have one value when is collapsed and other when is expanded...

This is very inusual because the city_1 correspond to two groups and City3 doesnt correspond to any region...

This sample is a simplification of a vefy complex model based in "groups" like this...

[swuehl]

I understood that you want to show the sum of rows as shown in fully expanded view also in collapsed view, i.e. the sum of rows in your sample is 800. This number should also be the correct number for sales by regions, while sales by country will miss sales for city_4.

In my opinion, there is a problem with your grouping / dependencies, since your pivot is implying that countries is grouped by regions which are grouped by cities (which I think your data model does not tell). City_4 is not linked to a region but linked to country, so City_4 is somekind of breaking the grouping hierarchy.

But anyway, if you are looking for the result 800 (which I would do If I look at the pivot fully expanded, summing up the most detailed results) for all collapsing states, you could add a set expression which selects all possible regions, thus disregarding cities with no region link, like

=sum(aggr( sum( {<Region=p(Region)>}Sales),Country,Region,City))

Hope this helps,

Stefan