Skip to Content
Technical Articles
Author's profile photo Thorsten Watzke

SAP Data Warehouse Cloud (DWC) and SAP Analytics Cloud (SAC): calculate an accumulative sum over periods and year (Version 2021.2.42)

This is only a short blog, but we spent a lot of time to find a solution for that. Maybe this will help one or the other.

Initial situation:

in our project we load finance data from SAP S/4HANA Cloud system into the SAP Data Warehouse Cloud (DWC) to create data views that are consumed in the SAP Analytics Cloud (SAC). In the SAP Analytics Cloud (SAC) we have the requirement to not only display data from the ledger per period, but also to cumulate the amounts over the periods of the fiscal year.

This is what the basic data looks like from the finance area. The blue values are the cumulative values over the year/period that are required in the SAP Analytics Cloud (SAC). We want to see the accumulative Values for the Amount in Company Code Currency and for the number of postings.

available%20data%20and%20required%20cumulative%20key%20figures

available data and required cumulative key figures

 

The extraction of the ledger data is made by the CDS-View G/L Account Line Item – Raw Data (I_GLAccountLineItemRawData).

In the SAP Analytics Cloud (SAC) there is also a functionality for that. There are 3 types of accumulative sums that can be displayed depending on the time granularity: Year to Date, Quarter to Date and Month to Date. But currently this is not working with a live connection. This is only working for import based connections.

Cumulation%20options%20in%20SAP%20Analytics%20Cloud%20%28SAC%29

Cumulation options in SAP Analytics Cloud (SAC)

 

Solution:

To achieve that, we built a SQL-View in the SAP Data Warehouse Cloud (DWC) with a self JOIN, the coding looks like that:

 

SELECT  a."CompanyCode",
	a."FiscalYear",
	a."FiscalPeriod",
	a."CostCenter",
	SUM("AmountInCompanyCodeCurrency") AS "Amount",
	SUM("CounterPostings") as "CounterPostings",
	MAX((SELECT sum("AmountInCompanyCodeCurrency") AS "AmountCum" 
	FROM "TEST_EV_GLAccount" AS b 
	WHERE 	b."CompanyCode" = a."CompanyCode" AND 
		b."FiscalYear" = a."FiscalYear" AND 
		b."FiscalPeriod" <= a."FiscalPeriod" AND 
		b."CostCenter" = a."CostCenter")) AS "AmountCum",
	MAX((SELECT sum("CounterPostings") AS "CounterCum" 
	FROM "TEST_EV_GLAccount" AS b 
	WHERE 	b."CompanyCode" = a."CompanyCode" AND 
		b."FiscalYear" = a."FiscalYear" AND 
		b."FiscalPeriod" <= a."FiscalPeriod" AND 
		b."CostCenter" = a."CostCenter")) AS "CounterCum"
FROM "TEST_EV_GLAccount" AS a
WHERE "FiscalYear" = '2020'
	AND "CostCenter" = 'CostCenter_1'
GROUP by "CompanyCode",
	a."FiscalYear",
	a."FiscalPeriod",
	a."CostCenter"
ORDER by a."FiscalYear", a."FiscalPeriod", a."CostCenter" 

 

Output and Result:

And this is the result in the SAP Data Warehouse Cloud (DWC) and SAP Analytics Cloud (SAC). We tested this with several 1000 records and it had no noticeable impact on performance.

Data%20Preview%20in%20SAP%20Data%20Warehouse%20Cloud%20%28DWC%29

Data Preview in SAP Data Warehouse Cloud (DWC)

 

Example%20of%20cumulative%20amount%20in%20SAP%20Analytics%20Cloud%20%28SAC%29

Example of cumulative amount in SAP Analytics Cloud (SAC)

 

 

Example%20of%20cumulative%20counter%20in%20SAP%20Analytics%20Cloud%20%28SAC%29

Example of cumulative counter in SAP Analytics Cloud (SAC)

 

This logic can of course be expanded to include additional fields. I hope this is helpful for you.

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Yuliya Reich
      Yuliya Reich

      Thank you Thorsten for sharing!!!

      Author's profile photo Ingo Hilgefort
      Ingo Hilgefort

      Hello Thorsten,

       

      wouldn't you be able to use a Restricted Measure in SAC (on the Live Connectivity) and use the Year to Date as Filter and then simply use that Restricted measure in the Chart ?

      Thanks

      Ingo Hilgefort, SAP

      Author's profile photo Thorsten Watzke
      Thorsten Watzke
      Blog Post Author

      Hey Ingo,

      yes, we tried also to do this, but YEAR TO DATE is not available when you working with live connections:

      will be set automaticly to Current Year:

      Best regards

      Thorsten

      Author's profile photo Ingo Hilgefort
      Ingo Hilgefort

      OK. Had not checked that part, as those items are available with BW Live / HANA Live.

      regards

      ingo