IBP Key Figure expression for Average value for the next five weeks

[former_member642421]

Hi IBP Experts ,

We have a requirement of deriving a key figure1 value based on next 5 weeks average of other key figure 2 and then multiple it by Key Figure 3 .

Example

KF1@PERPRODLOCCUST = ((AVG(KF2@PERPRODLOCCUST of next five weeks)*(KF3@PERPRODLOCCUST))

Can this be achieved using available KF expression or do we need L code for the same .

Please provide your inputs

[gabor_mittweg · Accepted solution]

Hi Anand,

In IBP 1905, there is a new aggregation type called cumulative aggregation that should be considered to realize your business requirement. Cumulative aggregation makes it easier to model some cross-period calculations. Please consider to split your calculation to a cumulative aggregation and multiply the result of it in the next calculation step.

The cumulative aggregation could look like this:

KF1@PERPRODLOCCUST = IBP_CAGGR ( “KF2@PERPRODLOCCUST” , ‘’AVG’’ , ‘’FORWARD’’ , ‘’FUTURE’’ , Time profile level at which the cumulative aggregation should restart, e.g.: 6)

Restarting can be a bit tricky, as 5 weeks is not a standard time profile level. In addition, this function will not calculate only one value, but will go through the entire future time horizon.

For detailed description of the cumulative aggregation function and for some examples please see the IBP 1905 Model Configuration Guide > Key Figures > Key Figure Calculations > Cumulative Aggregation chapter.

Best regards,

Gabor

[Saisamrat]

Hello Anand,

Below approach will give expected result

1. Create a helper KF (HKF1) and put conditions using PERIODIDn and populate value KF2 from current to current + 5 weeks and beyond this populate value 0

2. KF1@PERPRODLOCCUST = IBP_CAGGR ( “HKF1@PERPRODLOCCUST” , ‘’AVG’’ , ‘’FORWARD’’ , ‘’FUTURE’’ , Specifies a time profile level where cumulative aggregation should restart.). For example, you aggregate monthly values, and want to restart the aggregation at the start of the year. In this case, provide the time profile level of the year as the value of this parameter.

This way we are actually keeping values in HKF1 in entire future buckets i.e. with actual values till c+5 weeks and value 0 for later buckets and doing IBP_CAGGR with AVG function will result needed result.

Thanks
Sai Samrat

[former_member642421]

Thanks Piyush and Gabor for your quick turnaround.

Will try out both the options recommended by Piyush on using helper key figure and by Gabor on using the IBP_CAGGR cumulative expression available from 1905 , to achieve the result

Regards

Anand

[piyush_parekh]

Hi Anand,

You might get an error while activating planning area as a key figure should not have aggregation of some other key figure. Consider creating a helper key figure and utilizing it for AVG(KF2@PERPRODLOCCUST of next five weeks). Use that helper key figure in KF1.

Please share more details on how avg. should be calculated (static avg. of 5 periods or moving avg.etc). if you need further inputs.

Regards,

Piyush