Why does AGGR() return different results than direct aggregation for percentage calculations in pivot table hierarchy?

[akmalquamri]

Hi Experts,

I have dataset looks like below table:

There is another table having columns Region > Zone > Country.

The problem I am facing is that in pivot table I have hierarchy Region > Zone > Country > Shopkeeper. With this hierarchy, my below two codes were working fine

Code1:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Region', 'Zone', 'Country', 'Shopkeeper'),

Sum(global shop distribution)/sum(Total <region> global shop distribution),

Sum(global shop distribution)/sum(Total <zone> global shop distribution),

Sum(global shop distribution)/sum(Total <country> global shop distribution),

Sum(global shop distribution)/sum(Total <country> global shop distribution),)

code2:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Region', 'Zone', 'Country', 'Shopkeeper'),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Region> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Zone> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Country> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Country> global shop distribution), Region, Zone, Country, Shopkeeper))

)

As I change the hierarchy to shopkeeper > region > zone > country first code is running properly but second code is showing different answers.

Code1:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Shopkeeper', 'Region', 'Zone', 'Country'),

Sum(global shop distribution)/sum(Total < Shopkeeper> global shop distribution),

Sum(global shop distribution)/sum(Total <Region> global shop distribution),

Sum(global shop distribution)/sum(Total <Zone> global shop distribution),

Sum(global shop distribution)/sum(Total <Zone> global shop distribution))

code2:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'Shopkeeper', 'Region', 'Zone', 'Country'),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Shopkeeper> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Region> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Zone> global shop distribution), Region, Zone, Country, Shopkeeper)),

Sum(AGGR(Sum(global shop distribution)/sum(Total <Country> global shop distribution), Region, Zone, Country, Shopkeeper))

)

I need this because I want to get sum of these calculations at above level plus multiplying these with master item of another calculation. I want to fix this AGGR calculating then my problem will get resolve.

If any suggestions of different approaches will be highly appreciated.

Thanks for your attention.

[Daniel_Castella]

Hi @akmalquamri 

Is not there a discrepancy on your code?

In the first code 1 and code 2, the totals are done by Region, Zone, Country, Country. 

In the second code 1, the totals are done by Shopkeeper, Region, Zone, Zone; but code 2 is Shopkeeper, Region, Zone, Country.

The second code 2 doesn't seem to follow the same pattern as the others. Is that a typo issue or is it done on purpose?

Kind Regards

Daniel

[akmalquamri]

There will be 2 pivot table

First will show the hierarchy of categories starts from Region > Zone > Country > Shopkeeper

In other pivot we want to show Shopkeeper first followed by Region , Zone, Country.

Basically my requirement is that I have module calculations till shopkeeper level. Now I want to multiply my weights at each level for eg. Whatever global distribution is at zone level it will be considered as 100% by sum of all country level

Country level = Sum(global distribution)/ Sum(total <Zone> global distribution)

Zone level = Sum(global distribution)/ Sum(total <Region> global distribution) and so on

[marcus_sommer]

You need to simplify the approach to ensure that the business logic as well as the implementation approaches are going in the expected direction. This means not to combine n calculations into a single expression else using n parallel expressions in a table chart - for each part an own. In a first attempt the may 9 expressions - one with the pure sum(), then 4 with the different TOTAL and then 4 with the aggr() without any TOTAL. And then playing with selecting this and that, switching through the hierarchy and/or showing/hiding the dimensions in parallel.

Before considering the hierarchy or calculating rates the base-results must be comprehended. In this regard be aware that TOTAL is ignoring all dimensions unless the <specified> ones (only of included/visible dimensions) and aggr() adds an extra virtual table to the calculation - in your case by adding all 4 dimensions to it it effects as if all of them were included as dimensions.

ps: your final expression may be skipping the pick(match()) approach by querying the hierarchy directly like:

Sum(global shop distribution) /
sum(Total <$(=GetObjectDimension(Dimensionality()-1))> global shop distribution)

[Daniel_Castella]

Hi @akmalquamri 

But then the formulas set in the description are not correct. They should be:

Pick(Match(GetObjectDimension(Dimensionality()-1), 'region', 'zone', 'country', 'shopkeeper'),
sum(aggr(Sum(GSD)/sum(Total GSD), region, zone, country, shopkeeper)),
sum(aggr(Sum(GSD)/sum(Total <region> GSD), region, zone, country, shopkeeper)),
sum(aggr(Sum(GSD)/sum(Total <zone> GSD), region, zone, country, shopkeeper)), 
sum(aggr(Sum(GSD)/sum(Total <country> GSD), region, zone, country, shopkeeper)))

Pick(Match(GetObjectDimension(Dimensionality()-1), 'shopkeeper', 'region', 'zone', 'country'), 
sum(aggr(Sum(GSD)/sum(Total GSD), region, zone, country, shopkeeper)), 
sum(aggr(Sum(GSD)/sum(Total <shopkeeper> GSD), region, zone, country, shopkeeper)),
sum(aggr(Sum(GSD)/sum(Total <region> GSD), region, zone, country, shopkeeper)),
sum(aggr(Sum(GSD)/sum(Total <zone> GSD), region, zone, country, shopkeeper)))

(I used GSD as "global shop distribution" to simplify)

The Total needs to be a granularity level less for each case. Maybe it could be easier if you provide us a data sample of the incoming data and the results you expect to obtain.

Kind Regards

Daniel