Great news! We have enhanced the SAP Analytics Cloud Story Calculations by supporting Running Total - SUM, MAX, MIN, AVERAGE,COUNT as Calculated Account.
With the latest releases of 2023.01 for the SAC fast track customers (BI weekly release model) and for Quarterly release cycle with 2023 Q1 QRC, SAP Analytics cloud is improving the Story calculations by supporting Running Totals.
Note:
A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total.
Today In SAC, Running Total can be achieved by using dynamic Table calculations.
Image/data in this blog is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
Accumulative formulas are currently restricted only to tables and cannot be used as a formula in charts.
Also, Running Total will not be reset when the dimension value changes.
Ex: In this example, we can add accumulative Sum for Gross Margin only in the table and the Accumulative Sum value will not be reset when the Date (year or H1,H2) values are changed.
Running Total (Sum, Average, Min, Max, Count) Formula is added in the calculation editor at the SAC story level and the values to be reset based on the dimension context.
when you are analyzing the Sales data for different products across different locations and for over the period, we can use Running Totals for calculating the cumulative totals.
Running Total calculation provides multiple Operations such as Running SUM, COUNT, MAX, MIN, AVERAGE. Let’s see in detail about these calculations.
Running Sum is used to calculate cumulative total of a measure along with a specific one or more dimensions.
Ex: Below Table widget shows “Net Revenue” for different Products and locations. Now all I want is to calculate Running Sum along with Product for different Locations.
Navigate to Add Calculated Accountà Choose “Running Total” type in the Calculation Editor and choose “Net Revenue” as Account and “Product” as Running Total Dimensions.
Now you can see “Running SUM – Location” is added in the table and it results the cumulative SUM of NET Revenue.
Also, you can notice when the Location changes from California to Nevada, the Running Total calculation value is Reset.
Here, Location acts as Partition Dimension.
Note: Dimensions added in the axis will become partition dimensions (Resets the dimension values).
Running Average or Rolling Average returns the moving average of the current value and previous values.
Running Average is calculated by adding current value with Previous values then dividing the total by the number of data points.
Running Average = Running Sum/COUNT
Ex: Below chart provides the “Net Revenue for the year 2016 for all months/quarters.
Now we will use Running Total – Average to calculate the Moving Average of Net Revenue across all months of 2016.
For Ex: Moving Average for May 2016 is 28.62 million, ie, Running Sum/Count = 143.10/5=28.62 Million.
Running Count returns the Total no. of occurrences in the list of values.
In the below table shows the Net Revenue Generated for different stores contributed by different sales managers.
Now we can use Running Count to find out Total count of Stores contributed by each sales Managers.
Now you can See Gary Dumin contributes total 8 stores whereas James Frank has 4 stores.
Returns the running maximum and minimum of a measure. Running Max and Minimum is used to calculate the Maximum and minimum values for a specific time.
In the below table shows Net Revenue for 2014 for different quarters and all months.
Now, we can use Running Max to calculate the Maximum Net Revenue contributed across all months and quarters.
For Q1 2014 March Month has Maximum Net Revenue contributed is 22.83 Million which is the highest across all the year.
Key points to remember:
References:
https://blogs.sap.com/2023/02/04/whats-new-in-sap-analytics-cloud-and-sap-digital-boardroom-q1-2023/
https://blogs.sap.com/2022/12/21/whats-new-in-sap-analytics-cloud-release-2023.01/#1
https://help.sap.com/doc/00f68c2e08b941f081002fd3691d86a7/2023.1/en-US/81549704db144beab6ff4baff5900...
With the latest releases of 2023.01 for the SAC fast track customers (BI weekly release model) and for Quarterly release cycle with 2023 Q1 QRC, SAP Analytics cloud is improving the Story calculations by supporting Running Totals.
Note:
- It is supported for the following data models: Acquired, BI Dataset, HANA Live and New Model Type (Account dimensions).
- Live HANA requires EPM versions 2.00.202219.00 or above. Refer : 3274058
- This feature is only available in the optimized story experience.
A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total.
Today In SAC, Running Total can be achieved by using dynamic Table calculations.
Image/data in this blog is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
Problem with the current behavior:
Accumulative formulas are currently restricted only to tables and cannot be used as a formula in charts.
Also, Running Total will not be reset when the dimension value changes.
Ex: In this example, we can add accumulative Sum for Gross Margin only in the table and the Accumulative Sum value will not be reset when the Date (year or H1,H2) values are changed.
Running Total:
Running Total (Sum, Average, Min, Max, Count) Formula is added in the calculation editor at the SAC story level and the values to be reset based on the dimension context.
when you are analyzing the Sales data for different products across different locations and for over the period, we can use Running Totals for calculating the cumulative totals.
Running Total calculation provides multiple Operations such as Running SUM, COUNT, MAX, MIN, AVERAGE. Let’s see in detail about these calculations.
Running SUM:
Running Sum is used to calculate cumulative total of a measure along with a specific one or more dimensions.
Ex: Below Table widget shows “Net Revenue” for different Products and locations. Now all I want is to calculate Running Sum along with Product for different Locations.
Navigate to Add Calculated Accountà Choose “Running Total” type in the Calculation Editor and choose “Net Revenue” as Account and “Product” as Running Total Dimensions.
Now you can see “Running SUM – Location” is added in the table and it results the cumulative SUM of NET Revenue.
Also, you can notice when the Location changes from California to Nevada, the Running Total calculation value is Reset.
Here, Location acts as Partition Dimension.
Note: Dimensions added in the axis will become partition dimensions (Resets the dimension values).
Running Total – Average:
Running Average or Rolling Average returns the moving average of the current value and previous values.
Running Average is calculated by adding current value with Previous values then dividing the total by the number of data points.
Running Average = Running Sum/COUNT
Ex: Below chart provides the “Net Revenue for the year 2016 for all months/quarters.
Now we will use Running Total – Average to calculate the Moving Average of Net Revenue across all months of 2016.
For Ex: Moving Average for May 2016 is 28.62 million, ie, Running Sum/Count = 143.10/5=28.62 Million.
Running Total – Count:
Running Count returns the Total no. of occurrences in the list of values.
In the below table shows the Net Revenue Generated for different stores contributed by different sales managers.
Now we can use Running Count to find out Total count of Stores contributed by each sales Managers.
Now you can See Gary Dumin contributes total 8 stores whereas James Frank has 4 stores.
Running Max and Running Min:
Returns the running maximum and minimum of a measure. Running Max and Minimum is used to calculate the Maximum and minimum values for a specific time.
In the below table shows Net Revenue for 2014 for different quarters and all months.
Now, we can use Running Max to calculate the Maximum Net Revenue contributed across all months and quarters.
For Q1 2014 March Month has Maximum Net Revenue contributed is 22.83 Million which is the highest across all the year.
Key points to remember:
- Dimensions that you use in your running calculations become required dimensions. without this Calculation results with an Exception.
- The Running Total Calculation does not respect sorting order in SAP Analytics Cloud (SAC). Refer : 3294430
References:
https://blogs.sap.com/2023/02/04/whats-new-in-sap-analytics-cloud-and-sap-digital-boardroom-q1-2023/
https://blogs.sap.com/2022/12/21/whats-new-in-sap-analytics-cloud-release-2023.01/#1
https://help.sap.com/doc/00f68c2e08b941f081002fd3691d86a7/2023.1/en-US/81549704db144beab6ff4baff5900...
- SAP Managed Tags:
