Search code examples
viewcalculated-columnshana

SAP HANA - Calculations in calculated columns

Is it possible to create a calculated column in calculation view which goes as follows?

Table A

Month       Amount
-----       ------    
1           1000
1           2000
2           3000
2           3500

Filter:

filter month={CurrentMonth}; // here CurrentMonth= 2

JSON output:

[{
previousMnthAmount:3000,

currentMnthAmount:6500
}]
Solution

  • I would propose creating a Scripted Calculation View. In it, you can write SQLScript code for doing the necessary selects to get the data as you need it.

    I see two options, based on what you want to use the view for:

    1. Without input parameters.
    2. With input parameters.

    If you go for a view with input parameters, you might avoid some unnecessary calculations if you are really interested only in the data corresponding to one month.

    Without parameters

    Something along the lines of the following should work:

    SELECT "T1"."MONTH", "T1"."TOTAL" AS "CURRENT_TOTAL", "T2"."TOTAL" AS "PREVIOUS_TOTAL" 
 FROM (SELECT "MONTH", SUM("AMOUNT") AS "TOTAL" FROM #TEST GROUP BY "MONTH") AS "T1" 
 LEFT JOIN (SELECT "MONTH", SUM("AMOUNT") AS "TOTAL" FROM #TEST GROUP BY "MONTH") AS "T2" 
 ON "T1"."MONTH" = "T2"."MONTH" + 1;

    Where you should replace #TEST with your own table. Assuming that the following is the content of your table: Raw table content

    Then you would obtain the following results: Result without parameters

    If you wrap this into a view, then you can select from it and do a WHERE "MONTH" = 2 and obtain the values for the 2nd month.

    With parameters

    You can optimize the sql by removing the join altogether and doing filtered sums:

    SELECT (SELECT SUM("AMOUNT") FROM #TEST WHERE "MONTH" = 2) AS "CURRENT_TOTAL", 
 (SELECT SUM("AMOUNT")  FROM #TEST WHERE "MONTH" = 1) AS "PREVIOUS_TOTAL" FROM DUMMY;

    Of course, inside a calc. view, you should replace the fixed month values (2 and 1) with a parameter (e.g. 2 --> :IV_MONTH and 1 --> :IV_MONTH - 1). With the same content as above, you would obtain the following result:

    Result with parameters