cancel
Showing results for 
Search instead for 
Did you mean: 

HANA SQL Script - Cumulate Function

Former Member
0 Kudos

Hi experts,

I have a requirement to creat a new collumn in my Calculated View to cumulate another collumn values.

The cumulated collumn should cumulate 12 months (11 months back until the current month). So I created the following SQL Script:

     (SELECT SUM(B.SALES)

            FROM "_SYS_BIC"."LSA.ADM.STR/CVSTR_FIN_REP_ACTUAL_3" AS B

                  WHERE B.CC_FISCPER_AUX <= A.CC_FISCPER_AUX

                  AND B.SEGMENT = A.SEGMENT

                  AND B.COMP_CODE = A.COMP_CODE

                  AND B.CC_PLANT = A.CC_PLANT

                  AND B.FUNC_AREA = A.FUNC_AREA

                  AND B.GL_ACCOUNT = A.GL_ACCOUNT

                  AND B.COSTCENTER = A.COSTCENTER

                  AND B.COST_ELEM = A.COST_ELEM

                  AND B.CO_AREA = A.CO_AREA

                  AND B.PROFIT_CTR = A.PROFIT_CTR

                  AND B.VERSION = A.VERSION

                  AND B.CC_CURTYPE = A.CC_CURTYPE

                  AND B.CURRENCY = A.CURRENCY

                  AND B.UNIT = A.UNIT 

                  AND B.VTYPE = A.VTYPE

                  AND B.CC_FISCVAR = A.CC_FISCVAR

                  AND B.CC_FISCPER_AUX >= ADD_MONTHS(TO_DATE(A."CC_FISCPER_AUX", 'YYYYMM'), -11)

                  AND B.CC_FISCPER_AUX <= A.CC_FISCPER_AUX) AS "SALES_CUM12"

  

            FROM "_SYS_BIC"."LSA.ADM.STR/CVSTR_FIN_REP_ACTUAL_3" AS A

The calculation is working perfectly, despite the fact that it's not performing. My CV is based on FAGLFLEXT table, and it has more that 5.000.000 records.

When I run a Data Preview in my Analytic View (under FAGLFLEXT) it opens fast and easy. But my cumulative SQL Script is taking more than 3 minutes to return the results.

I was refering to HANA Business Function Library and I have found the CUMULATE function, but I coudn't understand the example that they provided there. Can you guys help me to understand how could I use CUMULATE function in this situation of mine, so I can achieve a better performance?

Thanks in advance.

Adrianon Frossard.

Accepted Solutions (0)

Answers (1)

Answers (1)

SergioG_TX
Active Contributor
0 Kudos

HI Adriano,

have you tried doing this on a calculation view?

have you tried checking the performance difference if you were to have an inner join?

just throwing ideas...

Former Member
0 Kudos

Hi Sergio, thanks for answering.

No, I did not tryed creating this cumulation logic through CV, I was hoping that I would be successfull using CUMULATE function. But I'm losing myself in the syntax of SQL while using CUMULATE, in order to cumulate 12 months, and respecting all the other characteristics (partitions).

I'll keep trying... But thanks again for your suggestion.

Yours,

Adriano Frossard