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:

umulative 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.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 shows an example of using LOOKUP to calculate the running total of the
previous 12 months.

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.

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

Assigned Tags
SAP Analytics Cloud
SAP Analytics Cloud for planning
SAP Analytics Cloud, add-in for Microsoft Office
SAP Analytics Cloud, analytics designer
SAP Analytics Cloud, augmented analytics
SAP Analytics Cloud, data modeling
Similar Blog Posts
Everything You Need to Know about SAP Analytics Cloud Connections
By Janet NguyenSep 28, 2020
Everything You Need to Know About The Latest in Cloud Analytics at SAP
By Eamon IdaOct 06, 2017
SAP Data Warehouse Cloud (DWC) and SAP Analytics Cloud (SAC): calculate an
accumulative sum over periods and year (Version 2021.2.42)
By Thorsten WatzkeDec 15, 2020
Related Questions
SAP Analytics Cloud: Cumulative sum charts
By Former MemberJul 30, 2019
Cumulative sum in analytics
By Abdelmonaam KallaliJul 15, 2021
SAP Analytics Cloud: Accumulative Sum across 0CALMONTH2
“Delu” gde je govorio i o Kosovu i Matohiji.