cancel
Showing results for 
Search instead for 
Did you mean: 

How to realize a SUM(IF( key figure calculation in IBP?

stadler
Explorer

Dear SAP Community,

Related to a customer request we are trying to realize a SUM(IF( function in the key figure calculation. This is the function which should be realized:

KF1@PERPRODLOC = SUM(IF(Attribute1=”X”;InventoryKF1@PERPRODLOC;0); IF(Attribute2=”X”;InventoryKF2@PERPRODLOC;0); IF(Attribute3=”X”;InventoryKF3@PERPRODLOC;0); IF(Attribute4=”X”;InventoryKF4@PERPRODLOC;0); IF(Attribute5=”X”;InventoryKF5@PERPRODLOC;0); IF(Attribute6=”X”;InventoryKF6@PERPRODLOC;0))

The idea is to consider in the calculation the key figures InventoryKF1 to InventoryKF6 only if there is a “X” flagged in the corresponding attribute in MDT LOCATIONPRODUCT (see example calculation in Excel):

The function can be maintained in the key figure calculation but during planning are check/activation the following error messages are thrown and activation is cancelled:

“Calculation “KF1@PERPRODLOC” is an aggregation. It must have exactly 1 input KF.”

“Calculation “KF1@PERPRODLOC” must not contain aggregation of different key figures”

So the system does not allow to use the function “SUM” in that way.

Is there a special function available which can be used in the calculation editor? Or how can this requirement be realized?

Thank you very much in advance for your support.

Best regards

Sebastian

Accepted Solutions (1)

Accepted Solutions (1)

ayanbishnu1981
Active Contributor
0 Kudos

Hello Sebastian

I get your requirement and I must say that it is a very unique requriement.

First of all the way you are trying to acheive this with KF expression using SUM() and including the IF() as a sub function with the SUM will not work, as the SUM expression in IBP is dont designed to handle sub function of child function arguments inside it.

As per the explantion that you have provided in your excel screenshot, you can acheive this using multuiple KFs and multiple helper KFs, but that solution will be a heavy solution although you can technically acheive your requirement.

At a high level what you need to do is to have equal no of helper KF Inventory KF1 .... Inventory KF6 where you have populated X. for example lets say you have created 6 helper HKF1.....HKF6. below will be the first set of expression that you need for all the helper KF from HKF1....HKF6

HKF1@PERPRODLOC= IF(Attribute1=”X,"InventoryKF1@PERPRODLOC",NULL)

.

.

HKF6@PERPRODLOC= IF(Attribute6=”X,"InventoryKF6@PERPRODLOC",NULL)

Then in the final KF the calculation will be

KF1@PERPRODLOC= "HKF1@PERPRODLOC"+"HKF2@PERPRODLOC"+....."HKF6@PERPRODLOC"

let me know if that helps

Answers (1)

Answers (1)

lingaiahvanam
Active Contributor
0 Kudos

Hi stadler

Can you elaborate the business requirement in detail?

The issue related to aggregation of data for different key figure even though same attribute is reference.

Best Regards,

Lingaiah

stadler
Explorer
0 Kudos

Hi lingaiahvanam,

Thank you for your quick feedback. I have updated the calculation example in my request. So the idea is to have a key figure called "Inventory on Hand Total" (in example above called KF1@PERPRODLOC). And for this key figure a sum is to be calculated from the already existing six inventory key figures (KFs: Inventory 1, Inventory 2, Inventory 3,...), but only if in the corresponding attributes (Inventory 1, Inventory 2, Inventory 3,...) a 'X' is maintained in MDT LOCATIONPRODUCT. So with the flagged 'X' it should be activated if a key figure is included in the calculation.

See the attributes which are available in the system and the in the calculation it refers to this flag:

In Excel calculation this can be solves easily with function SUM(IF(. And therefore I have refered to this Excel Function to make it clear.

I hope it is clearer now.

Thank you, I really appreciate your support.

Best regards

Sebastian