topic pivot table calculation - avg vertically and agg horizontally in App Development

pivot table calculation - avg vertically and agg horizontally

KennyTan — Mon, 30 Aug 2021 04:00:40 GMT

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.

Re: pivot table calculation - avg vertically and agg horizontally

MayilVahanan — Mon, 30 Aug 2021 04:22:09 GMT

Try like below

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

SUM(sales))

Re: pivot table calculation - avg vertically and agg horizontally

KennyTan — Mon, 30 Aug 2021 08:04:23 GMT

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.

Re: pivot table calculation - avg vertically and agg horizontally

MayilVahanan — Mon, 30 Aug 2021 09:03:32 GMT

Try like below

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

SUM(sales))

It based on ur requirement.

Re: pivot table calculation - avg vertically and agg horizontally

KennyTan — Mon, 30 Aug 2021 14:42:55 GMT

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.

Re: pivot table calculation - avg vertically and agg horizontally

MayilVahanan — Tue, 31 Aug 2021 02:41:41 GMT

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.