Cumulative and Rolling Aggregation

Objective

After completing this lesson, you will be able to explain Principles of Cumulative and Rolling Aggregation

Cumulative Aggregation

The cumulative aggregation function can be used at REQUEST.

Cumulative aggregation represents a chain of successive aggregations across periods. As such, there are several use cases for this function.

Cumulative Aggregation with Periodical Restart

For example, you want to aggregate monthly values and need to model restart of aggregation at the start of each year, or each quarter. This is the case, when optional parameter for time profile level for restart is used, so time profile level of a year or a quarter, must be specified.

Projected Stock with Negative Values Carry-Over

Rolling Aggregation

Rolling aggregation is used to aggregate key figures across several time periods, for a specified time window. You can use the IBP_RAGGR function to configure rolling aggregation in one step.

Mandatory and Optional Parameters of Rolling Aggregation Function

  1. Input key figure at the input planning level (mandatory parameter)
  2. Aggregation mode (mandatory parameter)
    1. Possible values: SUM, AVG, MIN or MAX.
  3. Start of rolling aggregation (mandatory parameter)
    1. Determines the start of the time window for which rolling aggregation is calculated for the input key figure. Has to be an integer.
  4. Duration of rolling aggregation (mandatory parameter)
    1. This is a number of time periods for which the input key figure will be aggregated. It has to be a positive integer.
  5. Calculation horizon (mandatory parameter)
    1. Possible values: PAST, PASTCURRENT, PASTCURRENTFUTURE, CURRENT, CURRENTFUTURE, and FUTURE.
    2. If you use one key figure for rolling aggregation, regardless of the horizon, use the PASTCURRENTFUTURE value for this parameter.
  6. Restart of rolling aggregation (optional parameter)
    1. For example, if you enter 6 (year), rolling aggregation will always restart at the first root time period of the next year.

Rolling aggregation can now be used at REQUEST.

Demonstration: How to Use CAGGR and RAGGR functions

ExerciseStart Exercise

Use CAGGR and RAGGR functions

In one of the previous exercises, you configured disaggregation of one key figure by another. We will increase the complexity of this by assuming that the business requirement calls for disaggregation based on the previous six months of sales.

In this exercise, 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.

Task 1: Create Configuration

Steps

  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 IDROLLAVGACTUALSQTY
    Base Planning LevelPERPRODCUST
    NameRolling Average Actuals
    StoredNot Selected
    CalculatedSelected

    1. Select your T## planning area and navigate to Key Figures tab at the top.

    2. Choose New.

    3. Enter the Key Figure ID, followed by other values from the table.

    4. Validate the request level calculation that will be generated automatically.

  2. Add the following calculation:

    ROLLAVGACTUALSQTY @PERPRODCUST = IBP_RAGGR("ACTUALSQTY@PERPRODCUST",''AVG'', -6, 6, ''PASTCURRENTFUTURE'')

    1. While in your ROLLAVGACTUALSQTY key figure navigate to Add Calculation Definition tab.

    2. Enter the PERPRODCUST level in the window.

    3. Enter the right side of the equation in the space.

    4. Click on the Inputs and validate that ACTUALSQTY@PERPRODCUST is stored.

    5. Validate the calculation

    6. If shown a warning, choose to proceed.

    7. Click Save.

  3. 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)

    1. Select your T## planning area and navigate to Key Figures tab at the top.

    2. Choose New → Helper Key Figure.

    3. Enter the Key Figure ID HROLLAVGACTUALSQTY.

    4. Create the calculation at request level calculation.

    5. Navigate to Add Calculation Definition tab.

    6. Enter the PERPRODCUST level in the window.

    7. Enter the right side of the equation in the space.

    8. Click on the Inputs and validate that ROLLAVGACTUALSQTY@PERPRODCUST is calculated

    9. Validate the calculation

    10. Select Save.

  4. 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 IDROLLAVGPROPAGATED
    Base Planning LevelPERPRODCUST
    NameRolling Average Propagated
    StoredNot Selected
    CalculatedSelected

    1. Select your T## planning area and navigate to Key Figures tab at the top.

    2. Choose New.

    3. Enter the Key Figure ID, followed by other values from the table.

    4. Validate the request level calculation that will be generated automatically.

      You may see a warning message. Choose to proceed, we will define the calculation in the next step.

  5. Add the following calculation:

    ROLLAVGPROPAGATED @PERPRODCUST = IBP_CAGGR("HROLLAVGACTUALSQTY@PERPRODCUST",''SUM'',''FORWARD'', ''CURRENTFUTURE'')

    1. While in your ROLLAVGPROPAGATED key figure navigate to Add Calculation Definition tab.

    2. Enter the PERPRODCUST level in the window.

    3. Enter the right side of the equation in the space.

    4. Click on the Inputs and validate that HROLLAVGACTUALSQTY@PERPRODCUST is calculated.

    5. Validate the calculation

    6. If shown a warning, choose to proceed.

    7. Choose Save.

Task 2: Activate Your Planning Area

Steps

  1. Activate your planning area.

    1. Choose Activate → Full Scope with Dependencies.

    2. Refer to the activation logs to ensure that your planning area is activated.

Task 3: Test Your Solution in the Planner Workspace

Steps

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

    FieldValue
    Time
    Time PeriodMonthly
    FromCurrent Month -12
    ToCurrent Month + 12
    RollingYes
    Planning Level
    Product ID

    Selected

    Customer ID

    Selected

    Key Figures 
    Actuals Qty

    Selected

    Rolling Average Actuals

    Selected

    Rolling Average PropagatedSelected
    Filters
    Product IDx100 Phone
    Customer ID111

    1. Choose Planner Workspaces app in the General Planner group.

    2. Open the workspace 300_##.

    3. On the top right, choose Workspace → Design.

    4. Choose Settings on the top right of your existing planning view.

    5. Under the More dialog (three dots), choose Manage Workspace Content.

    6. Select your component and choose Copy.

    7. Name your new planning view as RAGGR and CAGGR.

    8. Return to the Planner Workspaces app and choose Planning View.

    9. Select the copied content (you might need to refresh the workspace).

    10. Complete the dialog box with the information provided in the table.

    11. To display the planning view, choose Apply.

    12. When you will observe how the formula calculates the aggregation, choose Workspace → Save on the top right.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn