Generate MTD/QTD/YTD Values in SAP Data Warehouse Cloud
In this blog post, I want to share an idea of how you can generate month-to-date (MTD), quarter-to-date (QTD), and year-to-date (YTD) values in SAP Data Warehouse Cloud. This is only one way, I think there are several other ways how you can solve this issue. I am happy to discuss your ideas in the comment section.
The starting point is a control table that has the following structure:
|Source Month||Target Month||Target Value|
The whole table has entries for each source month.
After we have now the control table for the calculation, we now create a view. This is what the data looks like:
Now we have the data and the calculation table, we just create a simple join to the data, which you see in the following screenshots.
With the value type, we can now filter our data for MTD/QTD/YTD and the corresponding month. In the SAC, it will look like this:
In this example, I filter my data for June, and so I see directly the aggregated values. For the MTD you see the month value of 578881,24. For the QTD you get the values for the second quarter of the year and for the YTD you get the values of the entire year.
As you see, it works perfectly and is very fast. I had a similar post where I used this logic in one on Premise SAP HANA DB.
What do you think about this solution? I know this is only one approach and there are more out there. Maybe someone has a better solution?