How to calculate average of Actual History and display in past horizon?

[deveshbohre]

Hello Experts,

I have a business requirement where user wanted the past 12 month/24 months sales history average to be displayed in past horizon

as a workaround I created a forecast model with simple average algorithm and capture the value in M1 month. but this required running for stat fcst.

Do we have any IBP Function or any other more suitable option for this ?

[Praveen_Jadhav · Accepted solution]

Hi daeboo183

You can create a new KF "Avg History" using the IBP function: IBP_RAGGR. From the example given by you, the parameters which can be used are:

Input key figure = History

Aggregation mode = AVG

Start rolling agg = -100 (Assuming root time period is month, else change this value accordingly)

Duration of rolling agg. = 200 (Assuming root time period is month, else change this value accordingly)

Horizon = PASTCURRENT

Restart at = (Not provided)

For more details, refer:

https://help.sap.com/docs/SAP_INTEGRATED_BUSINESS_PLANNING/bf99e931b8d44aafb4e306ec3602cbdd/32b980b1...

BR,

Praveen Jadhav

[deveshbohre · Accepted solution]

Thanks Praveen , but somehow its showing no result

KF2@BPL= IBP_RAGGR ("KF1@BPL (Stored KF)",''AVG'', -100,200,''PASTCURRENT'')

[Praveen_Jadhav · Accepted solution]

Could you share the snapshot for both the KFs?

The above logic should work, mostly it may be issue with request level calculation.

[deveshbohre · Accepted solution]

Apologies for late response,

it was some test system data issue, layer i cleared cache as well and cleanup the data, its working fine , Thanks Praveen .

[deveshbohre · Accepted solution]

only change I made is kept the calculation limited to past using ''PAST'' and not ' ' PASTCURENT' '

[riyazahmed_ca2]

Hi Devesh,

Its just simple and there are multiple ways to resolve this. I will iterate 2 ways to you. Before that lets assume that KF1 is the History and KF2 is the Avg History and the Base Planning Level of KF1 is BPL. Now we have to create 1 Planning Level as the copy of BPL, but without periodicity in them. Eg: if BPL is MTHPRODLOCCUST, then the PL you have to create is PRODLOCCUST. I will assume that Planning level as BPL_NO_PERIOD.

1) Using your Forecast Model output along with the IBP_CURRENT_VALUE.

Lets say Your Forecast Model output is KF2@BPL and you are placing the result in the current period. You can do the below calculations in KF2.

KF2@REQUEST=AVG("KF2@BPL(calculated)")

KF2@BPL(Calculated) = "KF2@BPL_NO_PERIOD(calculated)"

KF2@BPL_NO_PERIOD(calculated) = IBP_CURRENT_VALUE("KF2@BPL(Stored)")

Note: KF2 should be changed to Calculated and Stored.

2) Using a mixture of 2 simplified calculations to keep it automated instead of depending on stored value of the KF2.

To achieve this, we have to create a copy of the Planning Level BPL. I assume the copy planning level as BPL_COPY. Once the Planning Level is available, you can use the below calculations.

KF2@REQUEST = AVG("KF2@BPL(calculated)")

KF2@BPL(Calculated) = "KF2@BPL_NO_PERIOD(calculated)"

KF2@BPL_NO_PERIOD(calculated) = IBP_CURRENT_VALUE("KF2@BPL_COPY(calculated)")

KF2@BPL_COPY(calculated) = IBP_CAGGR("KF1@BPL(Stored), "Avg","Backward","Past")

If the avg has to be populated only in the past horizon, not supposed to be in Future Horizon, Then you can use the below calculation.

KF2@BPL(Calculated) = IF("PERIODID0"<"$$PERIODID0CU$$", "KF2@BPL_NO_PERIOD(calculated)", NULL)

Let me know if this helps or if you have any queries.

Best Regards,

Riyaz

[deveshbohre]

Thanks Riyaz, I tested these options too, it's really a good alternatives, will use it in my next implementation, Thanks for helping and sharing your knowledge.