wrong values after changing the level of analysis

[#1]

Hi experts,

I try to calculate the average, absolute value of the difference between 'average value' and 'value per month'. Works well with

fabs(

    (avg(total <MATNR> consumption)-

        sum(total <MATNR, year, month> consumption)))

            /count(DISTINCT month)

as long as i stay at the same level.

So far so good, but for my application i need to have a look at the higher levels as well. The problem is that i don't get the right values after hiding column "month" in the pivot table i work in.

example is attached

Thanks in advance and best regards

Sebastian

[Gysbert_Wassenaar · Accepted solution]

Try:

avg(aggr(fabs(

          (avg(total <MATNR> Verbrauch)-

                    sum(total <MATNR, GJAHR, Monat> Verbrauch)))/count(DISTINCT Monat),MATNR,GJAHR,Monat))

See attached qvw

[#3]

Hello Gysbert,

that's awesome!

thanks a lot and best regards

Sebastian