How use IBP WEIGHTED AVERAGE with Aggregation

[former_member703940]

Dear Experts,

I need to aggregate a calculated Key Figure KF1@WKLOCPRODCUST with IBP_WEIGHTEDAVG(). KF1 must be used in another key figure KF3 calculation on @WKLOCCUST planning level. Therefore, I use following calculation.

KF1@REQUEST=IBP_WEIGHTEDAVG(KF1@WKLOCPRODCUST, KF2@WKLOCPRODCUST, ''CALCULATEDNUMERATOR'')

KF1@WKLOCCUST = SUM(KF1@WKLOCPRODCUST)

KF3 calculation with KF1:

KF3@REQUEST = SUM (KF3@WKLOCCUST)

KF3@WKLOCCUST = KF1@WKLOCCUST * KF4@WKLOCCUST

With these calculations KF1 is displayed Weighted Average on Excel planning view, but SUM aggregated in the KF3 calculation.

How can I aggregate KF1 from @WKLOCPRODCUST to @WKLOCCUST with IBP_WEIGHTEDAVG()?

[revanchatraban · Accepted solution]

Hi,

Please check the below calculation steps:

Base Planning level of KF1 - WKLOCCUST

Calculations for the IBP weighted avg functions should be as follows:

KF1@REQUEST =SUM( KF1@WKLOCCUST)

KF1@WKLOCCUST = SUM(KF1@WKLOCPRODCUST)

KF1@WKLOCPRODCUST = IBP_WEIGHTEDAVG(Input Key Figure1, Input Key figure 2, Calculated Numerator)

You can use key figure KF1 with Base Planning level WKLOCCUCT as input in other key figure.

Regards,

Revan C

[former_member703940 · Accepted solution]

Hi,

thanks for your answer.

I got a Base Planing Level of KF1 - WKLOCPRODCUST and not WKLOCCUST,

I forgot a calculation step for KF1

KF1@REQUEST=IBP_WEIGHTEDAVG(KF1@WKLOCPRODCUST, KF2@WKLOCPRODCUST,''CALCULATEDNUMERATOR'')

KF1@WKLOCCUST =SUM(KF1@WKLOCPRODCUST)

KF1@WKLOCPRODCUST = IF(ISNULL(KF1@WKLOCPRODCUST) OR KF1@WKLOCPRODCUST=0, 0, KF1@WKLOCPRODCUST)

As you can see above calculations, KF1@WKLOCPRODCUST is already used and therefore, I can not add your calculation with IBP_WEIGHTEDAVG() there.

I tried to create a new KF KF1AGG Base Planning level - WKLOCCUST as your answer:

KF1AGG@REQUEST = SUM(KF1AGG@WKLOCCUST)

KF1AGG@WKLOCPRODCUST = IBP_WEIGHTEDAVG(KF1AGG@WKLOCPRODCUST, KF2@WKLOCPRODCUST, Calculated Numerator)

KF1AGG@WKLOCCUST = SUM(KF1AGG@WKLOCPRODCUST)

If I use key figure KF1AGG with Base Planning level WKLOCCUCT as input in other key figure, then I am getting a SUM aggreated KF1 not WEIGHTEDAVG.

KF3@REQUEST=SUM(KF3@WKLOCCUST)
KF3@WKLOCCUST = KF1AGG@WKLOCCUST * KF4@WKLOCCUST

How would it work your calculations?

[revanchatraban · Accepted solution]

Hi,

Just one basic question: How can you have the same key figure as output as well as input?

KF1AGG@WKLOCPRODCUST is used as input for weighted average as well as output to store the calculated average. Please check on this point. I guess the input should be different key figure.

KF1AGG@WKLOCPRODCUST = IBP_WEIGHTEDAVG(KF1AGG@WKLOCPRODCUST, KF2@WKLOCPRODCUST, Calculated Numerator)

Instead try like this:

KF1AGG@REQUEST=SUM(KF1AGG@WKLOCCUST)

KF1AGG@WKLOCCUST = SUM(KF1AGG@WKLOCPRODCUST)

KF1AGG@WKLOCPRODCUST = IBP_WEIGHTEDAVG(KF1@WKLOCPRODCUST, KF2@WKLOCPRODCUST, Calculated Numerator)

Regards,

Revan C

[former_member703940 · Accepted solution]

Hi,

sorry it was a typing error. It was on the system as well as you recommended. But with these calculations I have a SUM aggregated KF1AGG as input in other key figure and it give wrong results, because it should be aggregated IBP_WEIGHTEDAVG

KF1AGG@REQUEST = SUM(KF1AGG@WKLOCCUST)
KF1AGG@WKLOCPRODCUST@ IBP_WEIGHTEDAVG(KF1@WKLOCPRODCUST, KF2@WKLOCPRODCUST, Calculated Numerator )
KF1AGG@WKLOCCUST = SUM(KF1AGG@WKLOCPRODCUST)

[revanchatraban · Accepted solution]

Hi,

Then try with following option:

Introduce the 2 temporary key figures KF1TEMP and KF2TEMP with base planning level WKLOCCUST. Then copy the values from KF1 and KF2 at WKLOCCUST level.

Use aggregated values in KF1TEMP and KF2TEMP in the weighted average.

KF1AGG@REQUEST=SUM(KF1AGG@WKLOCCUST)

KF1AGG@WKLOCCUST = IBP_WEIGHTEDAVG(KF1TEMP@WKLOCCUST, KF2TEMP@WKLOCCUST, Calculated Numerator )

I don't have live examples to show the calculation. Please check calculated values with manual calculation.

Regards,

Revan C