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

[QlikMo]

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

[rubenmarin · Accepted solution]

Hi, you can try with:

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

[QlikMo]

Thank you so much, I understand now