- SAP Managed Tags:
*Last updated: 2024.03.20 Add "Y(Q,M)TD" function in Chapter 2.1
Cumulative sum, running total, accumulative sum, running sum… Yes, we have many ways to call this calculation: A summation of a sequence of numbers across a certain period or dimension.
No matter the name, it always gives us a headache: You have many ways to calculate it but it seems you can never fit your customized needs. We used this calculation so often that we can see the question “How to calculate running/cumulative sum/total in XX?” EVERYWHERE.
Therefore, I would like to write a “cumulative” blog of different ways to calculate “cumulative sum” in SAP Analytics Cloud.
- If it helps you get your desired “cumulative sum”, give it a Like!
- If you still can’t get your “sum”, describe your question in the comments, and let’s take a look together!
- If you have another brilliant way to calculate the “cumulative sum”, share it in the comments and I will keep updating the blog!
Let’s get started!
There are basically two ways to add calculations to your Story:
- Create real-time calculations in the Story
- Create the calculations in the Modeler, then use it in your story
Depending on where you would like to add the calculation and the type of model you used, you have multiple options:
Unless mentioned, all actions below are performed in the Optimized Story Experience Mode. Read this blog to learn more.
1. Story Level
1.1 Create Cumulative Sum from a chart
- you have an imported model and used a DATE type dimension as the time dimension
- or you have a Live Hana model with the enriched time dimension
- you want to calculate the cumulative sum across TIME, e.g.YOY, YTD, Previous Year, etc
If this is the case, you can click the measure and use “Add time Calculation” as a shortcut to create time-related accumulated sum and display them directly on the chart.
This is equivalent to using the “Add calculation” feature for each measure(account) to create this time-related accumulative sum manually in the builder panel.
1.2 Create a Cumulative Sum from a calculation or table
The most convenient way to add cross-time cumulative calculations is to use the “Add Calculation” function associated with the measure. However, depending on the type of model you use, there are some limitations.
1.2.1 Imported model: Add calculation⇒Restricted account, Difference from
- you have an imported model
- you used a DATE type dimension as the time dimension to calculate the cross-time accumulative sum
In this case, simply click the three dots of the account and use add a calculation to add YOY, YTD, and PY calculations.
YEARTODATE:
Previous Year:
Year over Year:
1.2.2 Imported model: Resultlookup function
- you have an imported model
- you want to first aggregate a measure across a dimension (eg, get all quality sold per quarter across three years), then calculate running totals.
In case, we will use the calculated measure in the “add calculation” feature to complete a series of calculations to achieve it. Some manual work is needed. We will take this “3-year total quality sold per quarter and running total” as an example.
Step 1: first we need to sum up all quality sold per quarter for three years. We first created a calculated dimension based on the Date dimension:
Then use “RESULTLOOKUP” function to calculate the summation for each quarter:
Step 2: Create a calculation measure with If statement to calculate the running total
1.2.3 Imported/Live Hana model: Add calculation⇒Running total
- you have an imported model
- or you have a Live Hana model
- you used a DATE type dimension as the time dimension to calculate cross-time accumulative sum
- or you used a NON-DATE dimension to calculate cross-time accumulative sum/average/count, etc
Since 2023.01, we introduced a new type of function called “running total”. This calculation allows you to create a running total of SUM, COUNT, MIN/MAX, and AVERAGE. However, please note, no matter using a Date dimension or a non-date dimension, the cross-time calculation WILL NOT restart from a new year. The running total purely depends on the dimension used in the calculation and the data shown in the table.
1.2.4 Live Hana model with enriched time dimensions: Add calculation
- you have a Hana Live model
- you enrich the string-based time dimensions or date dimensions in your HANA view when creating the live model
If you enrich the string-based time dimensions or date dimensions in your HANA view, you can use time-related features to calculate YOY, PY, and YTD. Please read the help on how to maintain time dimensions:
1.2.5 Imported, Live BW, Live Hana model: Add calculation from a column
- you have an import model
- or BW Live model
- or HANA LIVE model
- you used a date dimension or non-date dimension to calculate the accumulated sum
In this case, you can use “add calculation” from your target measure column to achieve multiple types of accumulative calculation. However, please note, no matter if you use a Date dimension or non-date dimension, the cross-time calculation WILL NOT restart from a new year.
1.2.6 BW Live Model: a bit challenging
- you have a BW Live model
- you want to calculate the cross-time accumulative sum
Unfortunately, time-related calculations are not supported for BW live models from the story. We recommend creating the calculation in BW directly and using SAC to consume it. Read more:
https://blogs.sap.com/2020/06/11/analyse-your-data-live-with-sap-analytics-cloud-on-sap-bw-on-hana-s...
2. Model Level
2.1 imported model: Running totals with LOOKUP, ToPeriod or Y(Q,M)TD
- You have an imported model
- You have a date dimension and would like to calculate the cross-time accumulative sum
Formulas in the modeler can give the user extensive capability to conduct complex running totals calculations. Currently, SAC offers three functions with the "running totals" capabilities: LOOKUP, TOPERIOD, Y(Q,M)TD
2.1.1 LOOKUP
With the help of the “LOOKUP” function, it’s easy to calculate “Previous Year/ X months/X days”. Together with other dynamic time navigation functions such as “First, Last, Previous, Next, Current”, the customized time windows for accumulations can be achieved.
Below is an example of using LOOKUP to calculate the running total of the previous 12 months.
2.1.2 ToPeriod
Since SAC version 2023.19, you can use "ToPeriod" together with LOOKUP and RESTRICT functions to achieve cumulative "toDate" calculations such as "Year-to-Date", and "Month-to-Date". Details can be found in this blog.
Below is an example of using "ToPeriod" together with "LOOKUP" to calculate the "Month-to-Date" of Quantity Sold:
2.1.3 YTD, QTD and MTD
Since SAC 2024.01 version, users can now create calculated measures or calculated accounts using YTD (Year-to-Date), QTD (Quarter-to-Date), and MTD (Month-to-Date) functions at model level. These functions display running totals across year, quarter, or month levels of date granularity, respectively. This enables users to compare values against a budget, a target, or previous periods.
2.2 imported model: YOY function in models
- You have an imported model
- You have a date dimension and would like to calculate YOY
YOY is such a frequently used function that we have a shortcut for it. Using the YOY function directly in the modeler formula can generate the YOY results in seconds.
2.3 imported model: ITERATE function in models
- You have an imported model
- You have a date or non-date dimension
- You would like to calculate rolling calculations such as rolling sum, rolling growth, etc
SAC recently released the formula ITERATE in the Modeler. it is designed to create rolling calculations. With the help of the Prior function, you can also retrieve the value that was calculated for the previous member.
In this example, we have a Date dimension and we used ITERATE to calculate a rolling sum of the AMOUNT measure, it is split in years. We also use this ITERATE function in conjunction with inverse formulas, allowing inverse fill in the planning process. ITERATE can be used without an INVERSE function.
ravi.akasapu published an excellent blog on how to use the Iterate function to calculate YTD/QTD etc within model as shown below, check it out: here
If you would like to use a non-date dimension to calculate the rolling sum, e.g., the rolling sum of all countries, it can also be achieved with ITERATE.
2.4 imported model: Subtotal/ %Subtotal function in models
- You have an imported model
- You have a date or non-date dimension
- You would like to calculate individual subtotals and % subtotals for a specific Measure, broken down by a dimension or multiple dimensions.
Since 2023.07, SAC supports SUBTOTAL and %SUBTOTAL functions in the Modeler for imported models only. It is designed to create subtotals broken down by dimensions. See the example below:
2.5 Live Hana/BW model: A Bit Challenging
- You have a Live Hana/BW model
- You would like to create accumulated measures in the modeler
Unfortunately, dynamic time navigation functions (last period, current, etc), YOY function, and ITERATE functions are not available for Live Hana and BW models. We recommend creating these compute-intensive KPIs in Hana or BW and using SAC to simply consume them.
Conclusion:
So far we’ve concluded a few ways to create cumulative calculations in both Story and Modeler.
What is your way of creating cumulative calculations in SAP Analytics Cloud? Share it in the comments and I will update the article.
Future reads:
Advanced Formulas – How they work: https://blogs.sap.com/2021/05/03/advanced-formulas-how-they-work/
17 Comments
