Skip to Content

How to calculate a cumulative total in HANA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Apr 14, 2012 at 10:37 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 13, 2015 at 03:53 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 13, 2012 at 03:18 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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?