Average on Pivot Table - Including columns with no value in average calculation

QlikMo — Thu, 10 Nov 2022 09:50:22 GMT

I have a pivot table with an average at the end using the Secondarydimensionality() function (thank you Rubenmarin) however I am having issues with the way it is calculated. Ruben did provide another expression which did a division by 12 but this will not work for the current year

At the moment the expression is only calculating by the months which have a value e.g. Supplier A - (2+1+3+1+1) / 5 = 1.6 but it should be (2+1+3+1+1) / 11 = 0.7

However when the Year selection is a previous year it should be divided by 12 so A average should be 0.6

I just can't figure out how to do this without doing the average calculation in the script, which I don't want to do as I need the data to be linked to other fields so users can make selections.

Any help would be appreciated, qvd attached.

Thanks

Re: Average on Pivot Table - Including columns with no value in average calculation

rubenmarin — Thu, 10 Nov 2022 10:36:36 GMT

Hi, you can try with:

If(SecondaryDimensionality()=0 ,Num(sum(aggr(sum(Counter), Supplier, Month))/Count(DISTINCT TOTAL Year&Month),'0.0') ,sum(Counter) )

Re: Average on Pivot Table - Including columns with no value in average calculation

QlikMo — Thu, 10 Nov 2022 12:23:59 GMT

Thank you so much, I understand now

topic Average on Pivot Table - Including columns with no value in average calculation in QlikView

Average on Pivot Table - Including columns with no value in average calculation

Re: Average on Pivot Table - Including columns with no value in average calculation

Re: Average on Pivot Table - Including columns with no value in average calculation