# 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 2 shows overall design of the 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.

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).

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

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

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.

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 )