Generate MTD/QTD/YTD Values in SAP Data Warehouse Cloud

[reyemsaibot]

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.

 

Control Table

The starting point is a control table that has the following structure:

Source Month	Target Month	Target Value
1	1	MTD
1	1	YTD
1	2	YTD

The whole table has entries for each source month.

Data Table

After we have now the control table for the calculation, we now create a view. This is what the data looks like:

data preview

 

Data Model

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.

join

join settings

 

Output

Output

 

SAC Output

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:

SAC Output

 

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.

MTD values

 

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?

[meghomallar_das]

Hello Tobias,

 

Thanks a lot for posting this. We have some similar requirement, can you please help me more with the control table sample data ?

 

Regards,

Megh

[tmeyer]

Hi Megh,

I uploaded the control table to my GitHub repository https://github.com/reyemsaibot/year_to_date

best regards,

Tobias

[meghomallar_das]

Hello Tobias,

 

First of all thanks a lot for sharing the file.

This works absolutely fine.

I just think that while selecting the inner join we should also check the 'Distinct Values' option as the inner join generates duplicate records and having this option checked really helped me.

 

Regards,

Megh

[tmeyer]

Hi Megh,

Thanks for the hint with the Distinct Values. Perfect when it is working for you

Best regards,

 

Tobias