How to calculate a cumulative total in HANA

[ninad_thakur]

Hello,

I have a detail table (e.g. GL line item table) which has entries for 'posting date', account number, year, month, posting amount (single key figure) and account type (whether the account is a profit & loss account or a balance sheet account.

The requirement is to create a view which shows balance of month for profit and loss accounts and cumulative balance per month for balance sheet accounts (i.e. total of all line items till end of the month, not just for the month).

Currently I am able to get only balance for the month , but not the cumulative balance.

How can I calculate a cumulative amount column/key figure - either through Analytical view or calculation view?

Thanks,

Ninad

[former_member208919]

Hello Ninad,

You can achieve a cumulative total in hana by "Over" function in your SQL Script.

ex :

select CALMONTH, sum("AMT") over (order by "CALMONTH")  as CUML_AMT

  from(  

       select CALMONTH, AMT from <TABLE_NAME>

        )

-

Thanks

Subhash

[former_member192449]

This message was moderated.

[lbreddemann]

Hi there,

to get subtotals for different grouping levels, have a look into the GROUPING SET clause of HANA SQL.

It allows you to generate result sets with the data aggregated and grouped in different group definitions in one single statement.

regards,

Lars

[former_member184768]

Hi Lars,

For the Cumulative result, I think the grouping set will not help as it is supposed to pick up the previous total and add the line item value for the next total.

I tried to search all possible sources for the cumulative property, but could not find one. Hence has been advising the use the front end reporting tool to achieve the same.

But I am sure you'd have access to additional information, beyond my access limit.

Regards,

Ravi

[lbreddemann]

Hi Ravi,

I'm sure to not have any information about GROUPING SETS that is not covered in the documentation. And maybe I just got the requirement wrong here...

But the WITH SUBTOTAL and WITH TOTAL functions for grouping sets deliver the aggregated values for each group plus the total result set (with respect to the use of limit and offset  setting).

Were you talking about something like a running total instead?

regards,

Lars

[ninad_thakur]

Hi Lars,

Yes, by cumulative total, I meant a running total i.e. calculated key figure for current row = sum of amounts of all previous rows + amount of current row.

[former_member184768]

Hi Ninad / Lars,

Due to the nature of the requirements, I had been insisting on the implementation in the front end tool.

The navigation / Sorting / Display drilldown etc impact the cumulation result and this cannot be controlled from the backend.

I understand Ninad's point of view that if the cumulative property is provided in the analytical / calculation view, it will be available in all the front end tools, but within my limited knowledge on HANA, I could not find out that possibility.

May be something for SAP to figure out.

Regards,

Ravi

[lbreddemann]

HI all,

ok, a running total is something that currently isn't directly supported by HANA.

That this is something that surely is desirable to have (and that this feature is available for other DBMS) is of course a call for a new feature.

But for now (HANA SP3) running totals need to be implemented in the front end.

best regards,

Lars

[former_member184768]

Thank you.. . I had been trying to tell Ninad the same for so many days. Good to get to hear the same from the experts. 🙂

Secondly if you can communicate to the appropriate teams on adding the features, can you please ask them to look into the RANK / RANK OVER functions. These are required for the analysis like TOP N in a given group / sub-group.

Thanks for your help.

Regards,

Ravi

[ninad_thakur]

Hi Lars, Ravi,

Thanks for the clarifications. At least we know for certain that running totals feature is not available in HANA as of today. I will try and see if we can achieve this feature in the front end tools.

[Former Member]

Hi,

I'm trying to build exactly the same HANA model as you described (with the tables faglflexa, bseg and bkps - side by side scenario ) and i'm on the same dilema - is it possible to calculate the balances in HANA view? We are on SPS 5

Thanks,

Amir

[marc_daniau]

The Window Functions should help. Here is a sample query in SPS06 using a sum over function against an SAP HANA Live standard view.

SELECT Year("BillingDocumentDate_E") as "Invoice_Year", "BillingDocumentDate_E" as "Invoice_Date", round("TotalNetAmount_E") as "Billed_Amount",

       round(Sum("TotalNetAmount_E") OVER (partition by Year("BillingDocumentDate_E") order by "BillingDocumentDate_E")) as "YTD_Amount"

FROM "_SYS_BIC"."sap.hba.ecc/BillingDocumentQuery"

WHERE "SAPClient" = '004' and "SoldToPartyName" = 'Becker Berlin' and "BillingDocumentDate_E" >= '2002-01-01'

GROUP BY Year("BillingDocumentDate_E"), "BillingDocumentDate_E", "TotalNetAmount_E"

ORDER BY 1, 2

Message was edited by: Marc Daniau

[marc_daniau]

You may also want to check the standard view "sap.hba.ecc/NewGLAccountBalance" delivered with the Business Suite on HANA. It contains a running total: "AccumulatedBalAmtInDisplayCrcy".

[Former Member]

Hi, Marc.

Can you help to write this SQL-query using ce-functions?

[justin_molenaur2]

I am looking for a similar solution, but I think your approach has a flaw in it.

Due to the fact that you have to group by a numeric column ("TotalAmount_E"), in the case that you have two transactions that occur on the same day for the same amount only ONE will be counted.

Example data

Date          Amount          YTD          
1/1/2015    100                100

1/2/2015    200                300

1/2/2015    200                300 <-- Same date, same amount NOT COUNTED

The level of uniqueness needs to be more detailed since the group by "AMOUNT" column can be duplicated and you WANT to count those too.

Regards,

Justin

[Former Member]

This message was moderated.

[former_member184768]

What reporting tool are you using. This is easily available in most of the reporting tools.

As the cumulation will depend on the filter applied on the dataset fetched in the reporting layer, I'd suggest it won't be a bad idea to use the reporting layer cumulation feature.

Regards,

Ravi

[ninad_thakur]

The idea is to have the data calculated in a single place which can then be used in various reporting tools (BOBJ universes,  Analysis for Excel, BOBJ Explorer, Excel,etc.).

[former_member184768]

Hi Ninad,

But won't the cumulative figures be dependent on the data set. If you select one set of accounts, then the cumulative result will be different than second set of accounts. Hence in BW BEx reporting it is a display property for the result set being fetched and filtered in the report.

So a filter applied in Excel might be different from filter applied in BOBJ Univ resulting in two different cumulative values for the month. That's why I think it should be applied at the individual reporting tool level.

Regards,

Ravi

[ninad_thakur]

The cumulative figures certainly will be dependent on the data set. However, for the financial P&L and Balance Sheet , the data & filters are pretty well defined/known in my case (and hence can be kind of hard-coded / pre-filtered). In BW the standard BI Content there is a key figure 0BALANCE. I was looking for a way (either through calculated measures / SQLScript) to calculate the cumulative amounts as are calculated for 0BALANCE in BW. In HANA, I have SLT replicating FAGLFLEXA / BSEG /BKPF from ERP. I could additionally add FAGLFLEXT to the replication to get the monthly balance which would give me 17 key figures (one for opening balance for a year and the rest for the monthly total). However, I would need some code / transformation to transpose the 17 key figures into a single key figure (like 0BALANCE) to get the cumulative balance. Any idea of how I can achieve this using calculated columns/calculated measures or SQLScript?