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

[D039507]

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 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 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 Preview in SAP Data Warehouse Cloud (DWC)

 

Example of cumulative amount in SAP Analytics Cloud (SAC)

 

 

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.