Aggr in Pivot Table

[Arthur_Fong]

Hi all,

I would like to perform aggr in pivot table where the sub-total should be averaged out accordingly.

In the screenshot below for Feb, it seems that it is taking (200+200+300)/3 =233.3333

But I would like to have the sub-total (200+300) to be averaged out, which returns 250.

Any idea how can i achieve this?

Attached is a sample qvw for this scenario.

Thanks and regards,

Arthur Fong

[uacg0009]

Hi Jkfong93,
The "total" that you want is 250 or 350?
Because I saw in Feb, B1 + B2 + B3 = 200 + 200 + 300, then divide 2(Group B and C) = 350
Or do you also want to distinct the value then the value is same?
Thanks.
Aiolos

[Arthur_Fong]

Hi,

After using avg with aggr function, I want to sum the sub-total(Total by Branch) highlighted in yellow (200+300), then perform avg again to get the Final Total(Total by Group).

In this case, it will be (200+300)/2 = 250.

Thanks and regards,

Arthur Fong

[jaibau1993 · Accepted solution]

Hi Arthur!

In your expression you aggregate by Group, branch and month. Your Total is incorrect because, in total, you actually want to aggregate by group and month (not branch!). I suggest you use the following expression:

if(SecondaryDimensionality() <> 0,
	//Non total cells
	avg({<Month=>}AGGR(AVG({<Month=>}Value),Group,Branch,Month))
,
	//total cells
	avg({<Month=>}AGGR(AVG({<Month=>}Value),Group,Month))
)

Hope it helps!

Bests,

Jaime.

[Arthur_Fong]

It works! Thanks a lot!!

What if we have more than 3 dimensions with the same situation?
How do we counter this kind of scenario?

[jaibau1993]

I'm glad it worked! 😄

If you have N dimensions, you have to aggregate in the corresponding TOTAL by the N dimensions except those you want to totalize.

The (possible) tricky thing is that you have to locate where the TOTAL cell is in your table using Dimensionality() and SecondaryDimensionality() in IF statements. I suggest you to create two new expressions in your table with Dimensionality() and SecondaryDimensionality() respectively so you can check where is your total cell.

Bests,

Jaime.

[jaibau1993]

By the way, note that your original expression is equivalent to

AVG({<Month=>}Value)

so a simpler version of my answer would be

if(SecondaryDimensionality() <> 0,
	//Non total cells
	AVG({<Month=>}Value)
,
	//total cells
	avg({<Month=>}AGGR(AVG({<Month=>}Value),Group,Month))
)