How to force IBP_CAGGR to only cumulative aggregate until positive output, then reset to 0

[vincentverheyen]

How do we force IBP_CAGGR to:
a) only continue aggregating untill its output would reach a positive value,
b) then default to output value 0 in that period (thus no longer cummulatively aggregating, that is: pausing the cummulative aggregation), and in the next time periods default to output value 0 as well, unless/untll ...
c) ... until its input KF becomes negative (thus re-starting the cummulative aggregation),
d) cummulative aggregation now continues again, loop back to a)

The INPUT for the aggregation is in row 2 (blue).
What a standard IBP_CAGGR would do can be seen in row 3.
See the Desired outcome in row 4 (green).

More information about IBP_CAGGR can be found in Cumulative Aggregation | SAP Help Portal and Projected Stock Calculations | SAP Help Portal.

P.S.: Note in SAP Help Portal, that there is a 5th parameter which can reset the cumulative aggregation, but it is based on time profile level unfortunately, instead of on a resetting cut-off value such as 0, that is the passing of negative to positive. The 5th parameter is defined as: "time profile level at which the cumulative aggregation should restart (optional parameter, not applicable in REQUEST level calculations)". It seems to be a bit daring to try and propose a solution where CI-DS overwrites the time profile level based on a Key Figure reset condition?

UPDATE (added Business Case):

Row 4 (desired) shown above, is actually only an intermediatey step, to arrive at Row 5 (Business case) in orange:

I will walk you through the idea behind the output of orange row 5, month by month.

The idea is that row 4 would act as a kind of backlog which...
• A) while row 4 it is negative and not consumated by the Cumulative Aggregation — you do not want to send out orange values ... thus 0 pieces for row 5 (January to March; June to July; September to October).
• B) As soon as you have consumed your negatives in rows 4 (see month April), you want to send out the difference IN THAT PERIOD ONLY for row 5, namely 20 pieces (it would require an extra helper key figure, during the switching of the Key Figure row 4's sign from negative in previous month to positive in current month, but it would not be difficult to create such a filter). Also note that we can not use the regular IBP_CAGGR function in row 3 (see month August below)
• C) In the periods after, while INPUT is still positive, you know that you will not go negative in row 4, hence you do not need to calculate the difference any more, you need to directly equal the output of row 5 to input INPUT, in this case that is 10 pieces (May).

• D) loop back to A), since: as soon as you have a negative value again in row 4, and for as many periods as row 4 is negative (June to July) — you do not want to send out orange values ... thus value 0 for row 5. (January to March; June to July; September to October).

• B) also happens in August. We have to take the sum of the previous month of row 4 plus the current month of INPUT, which is -52 + 60 = 8 pieces. Note that we can not rely on IBP_CAGGR in row 3 to calculate the sum: that would be -22 (which is 30 too high ... because the positives kept accumulting in April and May, contrary to what we would have liked) + 60 = 38 pieces (which is 30 too high, due to positive Cumulative Aggregation throughout April and May).

[vincentverheyen · Accepted solution]

Achieve orange (Business Case):

Please note that it is possible to achieve the orange line (Business Case) using SAP IBP's built-in "Forecast Consumption", for example using the webUI app "Forecast Consumption Profiles" (see Forecast Consumption Profiles (Help Portal - SAP IBP)) or using the newly introduced non-operator (calculated) alternative IBP_CONSUMPTION (see Configuration of the IBP_CONSUMPTION Function (Help Portal - SAP IBP)).

The "Forecast Consumption Profile", for example, can be utilised by attaching the FCSTCONSMODE (Forecast Consumption Mode) as non-root with value 103 to the Master Data Type on which you will do the Forecast Consumption (Master Data Type selected under "Forecast Consumption Mode Assignment", in congruence with the "Forecast Consumption Level").

This presupposes that you have added FCSTCONSMODE to your Planning Area, from Master Data Type xyzFORECASTCONSUMPTIONMODE, which could have the following values:

• Forecast Consumption Mode: 103
• Boundary: 0
• Direction Description: Forward only
• Direction of the Foreward consum: 2
• Number of Backwared Consumption P: 0
• Number of Forward Consumption Pe: 2 (for example)
• Sales Order Consumption Sequence: 0

You can then perform forecast consumption, depending on the Forecast Input & the Sales Orders Input you have selected in your "Forecast Consumption Profile". The same could be done using the IBP_CONSUMPTION calculation.

However: SAP IBP's built-in "Forecast Consumption" is not able to visualise green (Desired). Therefor, below, I will demonstrate a method to achieve both green (Desired) and orange (Business Case), without SAP IBP's built-in "Forecast Consumption":

Achieve green (Desired) and orange (Business Case):

Using KF3 (IBP_CAGGR on the input), and KF10 and KF11 proposed by Irmhild Kuntze as a base, I then created new Key Figures KF30, KF31 and KF50, ... where KF50 is now equal to row "Desired" (Green). ✅

KF30 is defined as:

• IBP_CAGGR("KF10@PL",''MAX'',''FORWARD'',''CURRENTFUTURE'')

KF31 is defined as:

• "KF11@PL"-"KF30@PL"

KF50 is defined as:

• "KF31@PL"+"KF10@PL"
  

The idea behind this is that you keep the negatives (KF11) which are necessary for your consumption, but substract (in KF31) the extra postive values of the CAGGR-SUM (those extra positive values are collected using the CAGGR-MAX in KF30), which otherwise would make our negatives too high. In this way, the negative values have already been corrected for (corrected for, that is: lowered, as in "made more negative" once again) the continous extra cumulative aggregation that would otherwise result from a standard CAGGR-SUM (which we see in KF3). This correction is performed for the positive values in the final step in KF50.

I did some initial further testing to see if this is if/where it would break with other numbers, and the result always performed as Desired (see testing below).

First I did one additional test, and the results seemed promising. I used alternative INPUT:

-100 | 10 | 28 | 128 | 127 | -101 | 50 | -200 | 200 | 200

This yields the following calculation for KF50 (which is indeed what we hope for in "Desired"): ✅
-100 | -90 | -62 | 0 | 0 | -101 | -51 | -251 | -51 | 0

In the example in the screenshot above, it would be easy to create a formula to arrive at "Business Case" (Orange), as we see that the values in KF41 are equal to those in "Business Case" (Orange), if we replace the values of KF41 by 0 where we have a non-0 value in KF50. So we could have a KF51 as well, which could have the following definition:

• IF("KF50@PL"=0,"KF41@PL",0)

And this yields the following result for KF51 in the additional test, which is indeed what we hope for in "Business Case" (Orange):
0 | 0 | 0 | 66 | 127 | 0 | 0 | 0 | 0 | 149

I then did one further extensive test, with 25 periods, namely:
-100 | 10 | 28 | 128 | 127 | -101 | 50 | -200 | 200 | 200 | 342 | -500 | 600 | -550 | 570 | 40 | -45 | 40 | 5 | 12 | 100 | -700 | 700 | -20 | 20

Also there, KF50 matches row "Desired" (Green). ✅

However, following this extensive test with 25 periods, I noticed that this KF51 fails to match "Business Case" (Orange) ❌ in the following cases:

• IF ( IBP_PERIODSHIFT(KF50@PL,1) + KF2@PL ) = 0

In those cases we would want a value 0.

Therefor, we should not use KF51. Instead, we can create a KF52 and a KF53, with the following defintions. KF52 is defined as:

• IBP_PERIODSHIFT(KF50@PL,1)

... and KF53 is defined as:

• IF("KF50@PL"=0,IF("KF52@PL"+"KF2@PL")=0,0,"KF41@PL",0).

Now KF53 always matches "Business Case" (Orange). ✅

However, there is a more elegant way to achieve "Business Case" (Orange). We can simply do KF54, with the following definition:

• MAX("KF2@PL"+"KF52@PL",0)

KF54 now also always matches "Business Case" (Orange), ✅ and it's formula is more elegant than KF53, and intermediate helper KF41 (used in KF53) is no longer required.

[Irmi_Kuntze]

Try not to use 0, but NULL: IF("KF3@PL"<0, "KF3@PL", NULL)

It still might not work, because of the difference between NULL and missing period, and I don't know how to actively enforce a missing period.

If that does not work, the only possibility I see is a customer influence request. Unless someone else comes up with a great idea.

[Irmi_Kuntze]

Assume KF2 = the Input, KF3 = the CAGGR of line 2, KF4 = the desired output and KF5 = the orange line.

KF11 = IF(KF3<0, KF3, NULL) --> then KF11 equals the input KF2 but with the values too high replaced by NULL.

CAGGR cannot handle NULL too good. Can you try the following:

KF12 = IBP_CAGGR(11 , SUM, Foreward , PASTCURRENTFUTURE) --> not sure if PAST is correct, you might just work with CURRENTFUTURE unless you really want to consider past periods. But what might happen is that the CAGGR breaks when there is a NULL value and starts over with the next real value.

And you could have interims KF10 = MAX( KF3, NULL) --> you will have the positives only which you could CAGGR in another KF13, that should only sum up the positives until the next NULL

I cannot promise, but it is worth a try. Usually I try to replace a NULL with a 0 in order to avoid the breaking.

Please post here if that works out, I am really curious...

Irmi

[vincentverheyen]

Hi irmhild.kuntze, please find an example attached. Do you see a way to proceed?

[Irmi_Kuntze]

I cannot see a possibility with normal key figure calculation.

If you would need it in Excel only, you could use VBA oder local key figure to display this result

Or you can download input via CI-DS and upload into a stored KF with the wanted result, using logic within CI-DS

But as the the time profile is a global configuration by planning area, it should not be dependent on the key figure values of one single planning combination.

What is the business use case anyhow?

[vincentverheyen]

Hi irmhild.kuntze, thank you for your reaction, I very much appreciate it. Please see my updated answer. I have added a new Business Case row which hopefully can illustrate how this could be useful. Row 4 was only an intermediary.