How to avg past 26 weeks values in Key figure calculation in sap ibp.

[pravin_tikar]

Hello Expert,

We have a requirement to average past 26 weeks values and populate that avg to current week and same value to future 26 weeks.

We tried all std functions like Periodshift, CAAGR, RAAGR but nothing is fulfilling the requirement.

We have referred SAP Notes also 2240170 but it won't help.

We tried with below KF calculation:

IF( ("PERIODID4"<"$$PERIODID4CU$$" AND "PERIODID4">=("$$PERIODID4CU$$"-53) ), KF1@PERPRODCUST , 0 )

Could you please give some suggestion on this.

Regards,

Pravin Tikar

[ayanbishnu1981 · Accepted solution]

Hello Pravin

Give it a try with the below approach. I know its confusing and lengthy but that is what I could think as of now

Lets say the KF which has bucket wise value that you need to average is called STOREDKF@CWKPRODLOC.

a) Create a new KF example KFCAGGR@CWKRPODLOC. this will be a calculated KF. Use the below expression

KFCAGGR@CWKRPODLOC=IBP_CAGGR("STOREDKF@CWKPRODLOC" , ''SUM'' , ''FORWARD'' , ''PASTCURRENTFUTURE'')

b) you now need one more calculated KF let say AVG1KF@CWKPRODLOC. Use the below expression

IF("PERIODID4"="$$PERIODID4CU$$"-1,"KFCAGGR@CWKRPODLOC",NULL")

c) Create 1 more KF say AVG27KF@CWKPRODLOC. use the below expression

IF("PERIODID4"="$$$$PERIODID4CU$$"-27,"KFCAGGR@CWKRPODLOC",NULL".)

d) Create 1 more KF called OFFSETAVG27KF@CWKPRODLOC. use the below expression

IBP_PERIODSHIFT("AVG27KF@CWKPRODLOC","26")

e) Now yet 1 more KF called AVGCALC@CWKPRODLOC. use below expression

IF( "PERIODID4"="$$PERIODID4CU$$"-1,("AVG1KF@CWKPRODLOC"-"OFFSETAVG27KF@CWKPRODLOC")/26, 0)

Now you have got the average value of last 26 weeks in the above KF in week -1 bucket.

Now take a separate KF lets say SEMIFINAL@CKWPRODLOC and use the CAGGR expression using input as AVGCALC@CWKPRODLOC

IBP_CAGGR("AVGCALC@CWKPRODLOC" , ''SUM'' , ''FORWARD'' , ''PASTCURRENTFUTURE'')

Finally the last KF FINALKF@CWKPRODLOC

IF( "PERIODID4"<"$$PERIODID4CU$$"-27,"SEMIFINAL@CKWPRODLOC", 0)

let me know the outcome so that we can discuss further.

Regards

Ayan

[pravin_tikar · Accepted solution]

Thanks a lot Ayan,

It helped to achieve what I wanted but to heavy solution, I will go ahead with Simple Moving avg to do this.

Regards,

Pravin Tikar