Aggr - Sum of average of last 3 months average

[Anonymous]

Hi,

I have an expression that is avg of 3 months for the current month, e.g. July = avg(Jul,Jun,May)

I already have that one ussing rangeavg and aggr. The problem is thaat I have 2 dimensions at header in pivot (month and quarter)

When the Quarter is collapsed, it should sum the previously calculated average for each quarter's month, here the example

Quarter = Avg(Jul,Jun,May) + Avg(Jun,May,Apr) + Avg(May,Apr,Mar)

I'm right now ussing dimensionality() and secondarydimensionality() functions to use the right variable/formula depending the collapsed/expanded columns/rows, for the quarter I'm using the offset parameter in rangeavg, to calculate previous month's avg, but it only works when the quarter is not collapsed, I'm not really sure what it is doing when it is collapsed.

See attached application, as an example, Q3-2016 should return 196,835 in the Den column for the first one but it's returning 199,062 instead.

Commercial Direct

PC

Notebooks

Thnks for your help!

Regards

[Jason_Michaelides]

64,033 + 65,633 + 65,914 = 195,580. Shouldn't this be the correct answer?

[Jason_Michaelides · Accepted solution]

Try Sum($(vInv_Den)) anyway

[Anonymous]

Yeah... I don't know where those numbers came from, just the  64033 is the right one, anyways, it worked! thanks a lot! I was overcomplicating it, I never thinked of that. Thanks again