Running Total / Cumulative Sum using M_time dimension in HANA Calculation View
source link: https://blogs.sap.com/2023/03/29/running-total-cumulative-sum-using-m_time-dimension-in-hana-calculation-view/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK