Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Active Contributor

Requirement :

Most of the popular reporting tools can handle cumulative sum or running total , but still there have been lot of posts in SCN about whether it is possible to do it in HANA itself.  So, just wanted to see , if it 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

Detail Steps :

Figure 2 shows overall design of the calculation view.  It is pretty simple one, isn't it ?

Figure 2 : Graphical Calculation View

a) I 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 I shall have 12 records  ( overall 12 X 12 = 144 ) .  Where my Month matches with Month1 , that is my actual value for that month . And I need to sum all other values where  Month >= Month1 .  So, for above example, sum of  10, 20 and 30 would give me cumulative sum for Month 3.

To do this, I 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, I have two aggregation nodes. One with filter SAME_MONTH = "Y' and another with GREATER_EQ_MONTH = 'Y' .

Figure 5 : Aggregation node ( C UM_SUM )  -

I 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

Note: Model can be much more complex based on number of characteristics you take, in my case it was only Month . If you really want to go for this approach ( have enmity with script or coding :smile:   ) , 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 .

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"

Or you can use Window function for better performance


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

SQL in Section c running against a table with 1.3 billion records and I get the results in 40 micro seconds

c )

  WHERE CALMONTH BETWEEN '201401' AND '201603'

Please share your feedback and improvement suggestions

Labels in this area