Running Total / Cumulative Sum using M_time dimension in HANA Calculation View

Introduction

Usually, In Running total, we aggregate the total measure/assets which means from the start date to till date. There are multiple ways to achieve a running total. In this blog, we are going to work on using the M_time dimension in the Graphical Calculation view (HANA STUDIO).

Content

A time dimension is a hierarchical structure whose members represent different timescales, including years, semesters, quarters, months, weeks, days, and so on. A time dimension is useful for time-based analysis and reporting since it offers various levels of granularity and time levels.

Here we have taken VBAK where sales order header details will be stored.

• Select the required fields and get the input as a year.
• Create a calculated column ‘C_year’ where we separate the year from the respective field.

  leftstr( "ERDAT" ,4) // C_year

• Create another calculated column ‘dummy’ where we hardcode the value it can be any value, here I have used ‘A’ as a hardcode value for ‘dummy’, then have a filter for ‘C_year’ which must be less than or equal to the input year(ip_year).
  
  

and in Parallel, add the ‘dummy’ calculated column as same as before for M_time dimension with the same value and add a range to the ‘year’. Here in this system data is available only from 2016 so created a filter from 2016 to the input year (starting year [2016] is optional).

Then add the projection using inner join for the dummy field to have an all combination of years.

Then have a filter calculated year less than or equal to the year from the M_time dimension.

Conclusion

Similarly, using the M_time dimension, we can get the Running Total for month-wise and week-wise also. Hope this blog will be useful for a better understanding of the concept of running total using the M_time dimension.