pivot table calculation - avg vertically and agg horizontally

[KennyTan]

Hi Qlik community,

Would like to get help from the community on one of my use case. There is a requirement for a pivot table, whereby to calculate measure (sales) with apply average calculation vertically, while aggregate calculation horizontally.

I'm not sure whether this use case is possible? If possible can please shed on light on this ?

To illustrate, I attached a sample qvf.  For e.g. custA:

I would need the calculated figure for year (highlighted) to be the Average of the sales, i.e. 

year 2020: (78 + 95 + 46 + 75) /4 = 73.5 (currently is 294, not what I need)

year 2021: (15 + 95)/2 = 55

Same behaviors should apply if the pivot table is collapse.

Below is the calculation formula I had tried:

SUM(AGGR( nodistinct
Sum(sales),year, month, customer, product));

AVG(AGGR( nodistinct
Sum(sales),year, month, customer, product))

But the problem here is whichever I used, it will end up applied either SUM or AVG for the whole table. What I need is to calculate Average vertically, while SUM aggregate horizontally.

Appreciate any input. Thanks.

Regards,

Kenny.

[MayilVahanan]

Hi 

Try like below

If(Dimensionality()=1,
AVG(sales),

SUM(sales))

[KennyTan]

Hi @MayilVahanan ,

Thanks for the input, this work well for calculation when break down by product, but calculated figure seem not correct when it aggregate, for e.g. for custA below: 

I expect to have the year calculation (highlighted) = (111 + 116 + 46 + 107)/4 = 95, but it calculate as 54.286 (not sure how this figure come from).

Is it possible  to have similar behavior even when the pivot table is group together?

Thanks and Regards,

Kenny.

[MayilVahanan]

Hi

Try like below

If(Dimensionality()=1,
Sum(sales)/Count(Distinct month),

SUM(sales))

It based on ur requirement. 

[KennyTan]

Hi @MayilVahanan ,

Thanks, i didn't aware the level of dimension have impact on the calculation with using Dimensionality(), so didn't provide an accurate scenario to reflect my use case, but it was close.

What if i have 3 level of dimension? year, month and week. I tried below code using example provided by you, but the total average calculated year's figure seem a bit off (see highlighted):

 

formula i had tried:

If( Dimensionality()=2, Sum(sales)/Count(Distinct week),
	If(Dimensionality()=1,Sum(sales)/Count(Distinct month),SUM(sales)))

Can you please help identify which section i getting it wrong? I would like to have the same behaviour, but with additional new "week" dimension as row.

I re-attached new set of qvf data just in case.

Thanks for your kind reply and time.

Kenny Tan.

[MayilVahanan · Accepted solution]

Hi

Its looks correct only. 

what is the expected o/p for ur latest file?

Year total: =Sum(Sales)/ no of distinct month , so total is 99 which is correct.