SAP IBP Configuration - Use CAGGR and RAGGR functions in SAP IBP

[Peng_Lu]

In SAP Integrated Business Planning (IBP), the CAGGR and RAGGR functions are used for different types of aggregation of key figures.

CAGGR (Cumulative Aggregation)

The CAGGR function is used for cumulative aggregation, which means it sums up values over a specified period. This function is useful for calculating metrics like Year-to-Date (YTD) or Year-to-Go (YTG) values. The function takes several parameters, including the input key figure, aggregation mode (sum, average, etc.), direction (forward or backward), horizon, and an optional restart parameter.

Parameters:

• "KEY_FIGURE": The key figure you want to aggregate.
• "SUM": The aggregation method, which in this case is summation.
• "FORWARD": The direction of aggregation, indicating that the aggregation should move forward in time.
• "PASTCURRENTFUTURE": Specifies the scope of the aggregation, covering past, current, and future periods.

Formula:

AGGREGATED_KEY_FIGURE = IBP_CAGGR("KEY_FIGURE", "SUM", "FORWARD", "PASTCURRENTFUTURE")

This formula will sum up the sales values from the beginning of the year to the current period.

https://help.sap.com/docs/SAP_INTEGRATED_BUSINESS_PLANNING/bf99e931b8d44aafb4e306ec3602cbdd/c7f3218d...

RAGGR (Rolling Aggregation)

The RAGGR function is used for rolling aggregation, which means it aggregates values over a rolling window of periods. This is useful for metrics that need to consider a moving time frame, such as a rolling forecast. The function parameters include the input key figure, aggregation mode, direction, and the number of periods to consider.

Parameters:

• "KEY_FIGURE" is the key figure you want to aggregate.
• "SUM" specifies the aggregation method. Possible values are MIN, MAX, SUM, AVG, COUNT, and STDDEV. The values of the 2nd parameter must be surrounded by two pairs of single quotation marks. A double quotation mark instead of two single quotation marks results in an error during activation.
• -1: start of rolling aggregation (mandatory)

  The third parameter determines the start of the time window for which rolling aggregation is calculated for the input key figure. It specifies the starting time period in relation to the actual time period, and it uses the root time period of the input planning level. It must be an integer.

  Possible values:

  • Negative integer: rolling aggregation starts before the actual time period

  • Zero: rolling aggregation starts with the actual time period

  • Positive integer: rolling aggregation starts after the actual time period

• 3: time window or duration of rolling aggregation (mandatory)

  The fourth parameter defines the time window or duration of the rolling aggregation, that is, the number of time periods for which the input key figure is aggregated. It has to be a positive integer.

  For example, if the root time period is month, the third parameter is -1 and the fourth parameter is 3, the key figure is aggregated for the previous, actual, and the upcoming months.

• "PASTCURRENTFUTURE" indicates the scope of the aggregation

Formula:

AGGREGATED_KEY_FIGURE = IBP_RAGGR("KEY_FIGURE", "SUM", -1, 3, "PASTCURRENTFUTURE")

This function is particularly useful for scenarios where you need to analyze trends over time or perform cumulative calculations.

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

In this article, we will configure the rolling aggregation using the IBP_RAGGR function. In our example, we will calculate an average of 6-months of Actuals Quantity and then propagate that value forward using IBP_CAGGR function.

Create Configuration

1. Create a key figure called ROLLAVGACTUALSQTY at the PERPRODCUST planning level, which will display rolling average of 6 months of actual sales.
Use the following data.

Field	Value
Key Figure ID	ROLLAVGACTUALSQTY
Base Planning Level	PERPRODCUST
Name	Rolling Average Actuals
Calculated	Selected

Add the following calculation: ROLLAVGACTUALSQTY@PERPRODCUST =  IBP_RAGGR("ACTUALSQTY@PERPRODCUST", ''AVG'', -3, 3, ''PASTCURRENTFUTURE'')

• ACTUALSQTY@PERPRODCUST: The key figure you want to aggregate.
• 'AVG': The aggregation method, which in this case is the average.
• -3 and 3: The time horizon, indicating that the aggregation will consider data from 3 periods in the past to current period.
• 'PASTCURRENTFUTURE': Specifies the scope of the aggregation, covering past, current, and future periods.

This function will calculate the average quantity of ACTUALSQTY@PERPRODCUST over the last 3-period range (from 3 periods before to the current period).

2. Create a helper key figure called HROLLAVGACTUALSQTY, which will have values for rolling aggregation calculated by ROLLAVGACTUALSQTY in current bucket, while all future buckets will receive a 0.

HROLLAVGACTUALSQTY@REQUEST = SUM("HROLLAVGACTUALSQTY@PERPRODCUST") HROLLAVGACTUALSQTY@PERPRODCUST = IF("PERIODID0" = "$$PERIODID0CU$$", "ROLLAVGACTUALSQTY@PERPRODCUST", 0)

• IF("PERIODID0" ="$$PERIODID0CU$$","ROLLAVGACTUALSQTY@PERPRODCUST",0): The condition and values.

  • Condition: IF("PERIODID0" ="$$PERIODID0CU$$")
    • This checks if the current period (PERIODID0) is equal to a specific period ($$PERIODID0CU$$).
  • True Value: "ROLLAVGACTUALSQTY@PERPRODCUST"
    • If the condition is true, it assigns the value of ROLLAVGACTUALSQTY@PERPRODCUST to HROLLAVGACTUALSQTY@PERPRODCUST.
  • False Value: 0
    • If the condition is false, it assigns 0 to HROLLAVGACTUALSQTY@PERPRODCUST.

$$PERIODID0CU$$ is a placeholder that represents the current period in the time profile. It is used in key figure calculations to dynamically reference the current period without hardcoding specific dates or periods. This allows for more flexible and adaptive calculations that automatically adjust based on the current period.

This formula essentially sets HROLLAVGACTUALSQTY@PERPRODCUST to the rolling average quantity (ROLLAVGACTUALSQTY@PERPRODCUST) for the current period, and to 0 for all other periods.

3. Create a key figure called ROLLAVGPROPAGATED at the PERPRODCUST planning level, which will display the value of rolling average valid for the current time bucket in all future buckets.
Use the following data.

Field	Value
Key Figure ID	ROLLAVGPROPAGATED
Base Planning Level	PERPRODCUST
Name	Rolling Average Propagated
Calculated	Selected

Add the following calculation: ROLLAVGPROPAGATED@PERPRODCUST = IBP_CAGGR("HROLLAVGACTUALSQTY@PERPRODCUST",''SUM'',''FORWARD'', ''CURRENTFUTURE'')

 IBP_CAGGR function performs cumulative aggregation on the key figure HROLLAVGACTUALSQTY@PERPRODCUST. Here's a breakdown of the parameters:

• HROLLAVGACTUALSQTY@PERPRODCUST: The key figure you want to aggregate.
• 'SUM': The aggregation method, which in this case is summation.
• 'FORWARD': The direction of aggregation, indicating that the aggregation should move forward in time.
• 'CURRENTFUTURE': Specifies the scope of the aggregation, covering the current period and future periods.

This function will calculate the cumulative sum of HROLLAVGACTUALSQTY@PERPRODCUST starting from the current period and moving forward into future periods.

4. Activate your planning area.

Test Your Solution in the Planner Workspace

Test your solution by creating a Planner Workspace using the following information.

Field	Value
Time:
Time Period	Monthly
From	Current Month - 12
To	Current Month + 12
Rolling	Yes
Attributes:
Product ID	Selected
Customer ID	Selected
Key Figures:
Actuals Qty	Selected
Rolling Average Actuals	Selected
Rolling Average Propagated	Selected
Filters
Product ID	x100 Phone
Customer ID	111

Rolling average actuals = the average of actuals qty of last 3 month.

HROLLAVGACTUALSQTY@PERPRODCUST has only value 1197.333333 in current period.

Rolling Average Propagated has value 1197.333333 since current period to future.

1197.333333 + 0 + ......+0