How calculate the average value of last 24 months for a ratio

[alexrajo]

Dear All,

I have a requirement to get the average value of the last 24 months for a ratio. I read about how to do this for a rolling sum for the last 3 months, but adapting this would mean to create lots of helper key figures. Is there a faster way?

Thanks in advanced.

Alex.

[lingaiahvanam · Accepted solution]

Hi Alex,

The simple way is run the statistical forecast model with a simple average or based on your requirement.

Best Regards,

Lingaiah

[alexrajo · Accepted solution]

Thanks Lingaiah, this is the easier way and works correctly. Thanks to both.

[Irmi_Kuntze]

do you only need one single value back, which is the average of the previous 24 rolling months?

That is easy (make your adjustments on the syntax yourself please, especially for the periodid-level to be in months)

:

Take KF1@PERPRODLOC as your KF for which you want to do the average

Have a calculated KF KF2@PERPRODLOC =

IF( ("PERIODID3"<"$$PERIODID3CU$$" AND "PERIODID3">=("$$PERIODID3CU$$"-12) ), KF1@PERPRODLOC , 0 )

And

KF2@PRODLOC = SUM(KF2@PERPRODLOC) / 24

And

KF3@PERPRODLOC = IF( "PERIODID3"="$$PERIODIDCU$$" , KF2@PRODLOC , NULL)

As in this case, the input level and output level don't match any more, you have to trick a bit, by either putting in the INPUT KEY FIGURES another KF in that is on level PERPRODLOC as well, or you have a dummy key figure on PERPRODLOC e.g. KFDUMMY@PERPRODLOC, and make a dummy code around the real logic, e.g. such as replacing the NULL with stupid stuff:

KF3@PERPRODLOC =

IF( "PERIODID3"="$$PERIODIDCU$$" , KF2@PRODLOC , IF( KFDUMMY@PERPRODLOC = 1 , NULL , NULL ) )

______

If you requirement is rolling average , so in today's month you wan to have the average of the last 24 months, and the previous period you want to have the average of the last 24 month with an offset of 1 month, and so on, this approach does not work any more and you have to really specify your requirement in a proper way

[alexrajo]

Thanks Irmhild. That is what I need. Just this unique average value back. Then I have a question: what is PERIODID3? I will try your solution and let you know. Thanks a lot.

[gurucharanscm]

PERIODID3 refers to month. By the way, when you copy the logic of Irmhild, please make sure to put PERIODID3CU wherever you see PERIODIDCU on the right hand side of the calc logic 🙂

[alexrajo]

Thanks Guru. I finally went for Lingaiah's solution and it works. Anyway, I take note of all your advices. Appreciated.