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 )
