topic Re: Aggr in Pivot Table in QlikView

Aggr in Pivot Table

Arthur_Fong — Sat, 16 Nov 2024 04:58:34 GMT

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

Re: Aggr in Pivot Table

uacg0009 — Thu, 13 Dec 2018 06:56:00 GMT

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

Re: Aggr in Pivot Table

Arthur_Fong — Thu, 13 Dec 2018 07:08:49 GMT

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

Re: Aggr in Pivot Table

jaibau1993 — Thu, 13 Dec 2018 08:22:04 GMT

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.

Re: Aggr in Pivot Table

Arthur_Fong — Thu, 13 Dec 2018 08:53:35 GMT

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?

Re: Aggr in Pivot Table

jaibau1993 — Thu, 13 Dec 2018 09:30:32 GMT

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.

Re: Aggr in Pivot Table

jaibau1993 — Thu, 13 Dec 2018 09:32:58 GMT

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))
)