Simulating Cumulative Sum in SAC Analytic Model using LOOKUP function(for Charts)
Sometimes as a SAC developer you would receive request from your business team to build dashboards encapsulating an aggregation function like runningsum(). This blog covers a specific case wherein
- The scope is limited to SAC acquired connections involving an SAC model creation (rather than live connections). This specific case if pertaining but not limiting to OData service as source.
- We are trying to achieve the cumulative sum in a chart rather than a table (wherein we have inbuilt cumulative functions).
- The cumulative sum should be continuous and can seamlessly across year boundaries E.g. Dec 2020 through Jan 2021 etc. This would rule out the use of YTD, MTD etc. aggregation calculation leveraged in time series plots
Sample Use case: Showing daily count of defects opened during HyperCare phase of a project along with a running sum of backlog. Here:
- Defects opened = Number of defects opened per day
- Defect Backlog = A – B = sum (All tickets opened till date) – sum (All tickets closed till date)
Even though we do not have an equivalent out of the box cumulative aggregation function to leverage in a user defined variable, SAP Analytics Cloud still gives us the flexibility of simulating the function at a model level.
Here, the approach is two-fold
Step1: F1 = Calculate A-B at daily level (@SAC Model)
Step2: F2 = Apply LOOKUP() function on the above calculation i.e. F1(@SAC Model)
Navigate to the Model >Account > Add Member (under Edit) > Add Formula for the Member
The respective columns for Step1 and Step2 are then created as members of account dimension
Now, let us understand the LOOKUP function to code for step 2
Decoding the Lookup function for cumulation functionality:
Part1: Overview of LOOKUP function
The LOOKUP function is used to aggregate the account member in the context of a given point of view (POV). Click here to learn more about the function from SAP Help Portal
Syntax: LOOKUP([<account member>], [<POV>], [<Ignore Dimension>])
Part2: Writing Dynamic time navigation functions for given POV
Backlog = [ Open Incident Count] – [Resolved Incident Count]
LOOKUP([Backlog] ,[d/Week_End_Date]=Lastperiods(“Day”,365 ))
The ‘Lastperiods’ time navigation function is the key to cumulate the daily backlog values. Here, we have restricted the cumulation to 365 days of a year, Ideally this value would vary depending upon the requirement and can be changed accordingly. The output of the function is shown below.
In the above chart, the blue bar represents the opened tickets at a weekly level. The orange line represents the cumulation output of the LOOKUP function
Open Tickets for week of March 29th =6 –> (A)
Resolved tickets for week of April 5th = 0 –>(B)
Open Tickets for week of April 5th = 4 –>(C)
Backlog for Week of April 5th = (A)-(B)+(C) = 6-0+4 = 10
Here the LOOKUP function cumulates all open tickets (for 3/22 , 3/29, 4/5) then substracts all closed tickets (for 3/22 , 3/29, 4/5) to simulate the above number (10). This is because the it acts on top of the Account member Backlog = [ Open Incident Count] – [Resolved Incident Count]
In this way we can centralize the cumulation at a model level which can then be leveraged across multiple dashboards.
This is quite a goodread, I was also wondering if you came across a way to do this on a live connection?
Hello Abhijeet - Unfortunately, the only option I am aware to achieve cumulation in a live connection like SAP HANA is by pre-aggregating the values for the different expected combinations in the calculation view itself. Even though the dataset might explode, we can leverage SAC parameters to restrict the data retrieve to achieve performance gains.
Dhyaneshwaran KS Thanks for the blog! Very interesting.
We have recently added a new label to the SAC community: "SAP Analytics Cloud, data modeling". You might want to add it to your blog for better visibility!
Thank you for the feedback Anna! Appreciate your time going through the blog. I have added the required tag as per your guidance.
I need to graph cumulative values over the years, but I can't do this. Could you teach how to do it?
Hello Charles - I am assuming that you are using an acquired connection in SAC and trying to cumulate a measure (Sales / revenue) in this case across a time dimension (X axis). In such a case, you have to modify the model to include a new variable (cumulation) as explained in my post. Below would be the syntax.
Revenue Cumulation = LOOKUP([Revenue] ,[d/Date]=Lastperiods(“Day”,365 )).
Note: the above formula goes back to history by 365 days but you can tweak it according to data availability from source
Hi Dhyaneshwaran KS
thanks for this great post this is a very powerful function. I was wondering if its also possible to do this cumulation only for certain date instead of each period (365 days / 4 Months etc.)
Lets say we have a datasource that shows us changes in material stock levels.
- 01.01.2020 opening balance of 1000 Pcs
- 16.01.2020 (-)120 PCS
-05.02.2020 (- )500 PCS
-15.04.2020 (+) 250 PCS
What we now want to do is to accumulate the quantities to get the current stock levels as the specific dates. We do not want to do it for each day because otherwise the amount of data becomes overhelming and consfusing. Is there a way to achieve this in SAC?