Running Total / Cumulative Sum using M_time dimension in HANA Calculation View
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).
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.
- 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  is optional).
Then add the projection using inner join for the dummy field to have an all combination of years.
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.