Cumulative Sum / Running Total in HANA Calculation View and the Dangers of It

WARNING: Model can be much more complex based on number of characteristics you take, in this case it was only Month. If you really want to go for this approach ( have enmity with script or coding), do not use the base tables in JOIN_1. Use this model after you aggregate data to a certain extent.
Obviously, you would like to avoid a join ‘m x m’ where m is a large number.
>>> This can be crashed HANA system. <<<

Cumulative sum or running total can be easily done via Graphical Calculation view and it seems to be working.

Conditional summation – If in Calculated column

Note: This might still be easier to do in Scripted View or Reporting Tool . Scripted Calculation View is deprecated now, instead use Table Function.

In Figure 1, our base data is in Column 1 and 2 and we want to add Colum 3 (“C UM_SUM”) to existing data set.

Figure 1: Base data and result

Figure 2 shows overall design of the calculation view.

Figure 2: Graphical Calculation View

a) We have two projections (ALL_DATA and ALL_DATA2) of the base data (first two columns in Figure 1)

b) Created one Calculated Column “DUMMY” on each projection and assigned it a value 1. You can assign any other constant value.

c) In join node (JOIN_1), (inner) joined these two data sets via “DUMMY” column. After joining output would be like below.

Figure 3: Output of Join node

If you notice, for every month now we shall have 12 records (overall 12 X 12 = 144). Where our Month matches with Month1 , that is our actual value for that month. And we need to sum all other values where Month >= Month1. So, for above example, sum of 10, 20 and 30 would give us cumulative sum for Month 3.

To do this, we defined two calculated attributes,
one to check if Month = Month1
and another to check If Month >= Month1 (refer Figure 4).

Figure 4: Calculated Columns (Attributes) on Join_1 node

d) Now, we have two aggregation nodes. One with filter SAME_MONTH = ‘Y’ and another with GREATER_EQ_MONTH = ‘Y’

Figure 5: Aggregation node (C UM_SUM)

We took Month and Amount_1 and renamed Amount_1 to C UM_SUM.

Figure 6: Aggregation_2

Took Month and Amount with a Filter SAME_MONTH = ‘Y’

e) Lastly, we need to union these two aggregation nodes. Take Amount from one node and C UM_SUM from another node.

Figure 7: Union Node

Result is in Figure 1 itself.

Equivalent SQL Code would be as simple as below.
We can create a Calculation View (Scripted) or Table Function using this SQL.

a)

select a."Month" ,a."Amount" , ( select sum(b."Amount") from "<SCHEMA>"."RUN_SUM" b where a."Month" >= b."Month" ) as run_sum
from "<SCHEMA>"."RUN_SUM" a
order by a."Month"

b)

select "Month" , "Amount" , Sum("Amount") over ( order by "Month") from "<SCHEMA>"."RUN_SUM"
order by "Month"

c)

SELECT CALMONTH , ORDER_QUAN , SUM (ORDER_QUAN) OVER ( ORDER BY CALMONTH) AS SUM_VAL
FROM
( SELECT CALMONTH , SUM(ORDER_QUAN) AS "ORDER_QUAN" FROM "MY_TABLE"
WHERE CALMONTH BETWEEN '201401' AND '201603'
GROUP BY CALMONTH
)