*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.
Let’s get started!
There are basically two ways to add calculations to 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.
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.
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.
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:
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
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.
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:
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.
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...
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
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:
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.
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.
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.
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:
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.
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.
Advanced Formulas – How they work: https://blogs.sap.com/2021/05/03/advanced-formulas-how-they-work/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
8 | |
7 | |
7 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 |