Everything You Need to Know About “Cumulative Sum” in SAP Analytics Cloud

*Last updated: 2023.10.11 Add “ToPeriod” 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!

• 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-sap-bw-4hana-part-3/

2. Model Level

2.1 imported model: LOOKUP function in models

• 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 calculations. 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.

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

Assigned Tags

You must be Logged on to comment or reply to a post.

Thanks for this compilation. Really helpful !!

what a great article. This allows us to do Pareto analyses, not ?

Yao Yao
Blog Post Author

Well, partially Pareto :). A real Pareto needs to get the items sorted and then accumulated. Currently, we can use ITERATE and PRIOR to getting them accumulated, but not sorted.

I have a requirement wherein I need to show the YTD values upto the selected period in a column as well as the monthly value of the selected month. This can be done using restricted measures in stories but I was wondering if this can be achieved at model level also as I need to use the same report in SAC addin also. I have tried with lastmonths but it doesn't work as there is no way to control the upto current period settings.

Regards,

Iqubal

Yao Yao
Blog Post Author

Hi Iqubal,

I believe this method with the "Iterate" and "prior" functions provided by Ravi  can solve your problem. Check it out!

Thanks,

Yao

Hello Yao,

How can we add a quarter in the YTD calculation, so we can get the correct number in quarters?

When we are using hierarchy in the Date (time Dimension),

NT

Yao Yao
Blog Post Author

Hi NT,

The YTD calculation should support date dimension hierarchy, which means if you select the correct the date dimension hierarchy (eg, year-quarter-month-day) and drill to the desired level of hierarchy, the YTD should show quarterly results automatically.

eg, the YTD is displayed by quarters in the image below.

Thanks,

Yao

Thank you, Yao, for your quick response.

The hierarchy view for YTD calculation uses the following calculated measure formula.

Calculcated Measure YTD: ITERATE(IF([d/Date].[p/MONTHDESC]=("Jan" ) ,0 , PRIOR () ) + [SIGNDATA],[SIGNDATA], [d/Date])

However, I am getting the correct number in the flat presentation (base level), but in the hierarchy view, QTD numbers are not rolling for YTD calculation. The screenshot is attached for your reference.

NT

Yao Yao
Blog Post Author

Hi NT,

This is the expected behavior of YTD and QTD. Since QTD is used to check numbers per quarter, so in hierarchy view, QTD numbers are not rolling for YTD calculation.

Thanks,

Yao

Hello Yao Yao:

Thank you for the clarification. I am looking for YTD to show on the quarterly level as well.

Do you know how we can enhance the formula so that each quarter shows a rolling YTD as seen below?

Here is a screenshot of what we are trying to replicate:

YTD

Yao Yao
Blog Post Author

Hi Owen Isley,

I believe it's hard to mimic the exactly display of this Excel table, but by using the hierarchy view of date dimension in SAC and ITERATE function, we can get a QTD value rolling for each Quarter:

Hi Yao Yao:

Thank you so much for the reply. Can you send the formula you created for "rollingsumbyyear"? I think that solution is acceptable as each quarter is showing a cumulative YTD.

Yao Yao
Blog Post Author

Hi Owen,

Glad it can help. The formula is below:

ITERATE(PRIOR()+[AMOUNT] ,[AMOUNT] ,[d/Date].[p/YEAR] ) | INVERSE([AMOUNT]:=[rollingsumbyyear]-PRIOR() )

Thanks,

Yao

Thank you, Yao!

Hello Yao Yao.

How can I make an accumulated sum of accounts that requires the amount of the initial balance that would be like having a month 0?