There are different ideas and logics to determine year-to-date. Besides my post, which is also available on blogs.sap.com there is another post to determine a week-to-date (WTD) and year-to-date (YTD). I think that idea is also a good starting point, and I looked into it.

Instead of a control table which I have to fill manually, I created a new SQL view based on the standard SAP timetables in Data Warehouse Cloud. First create a new SQL view in the Data Builder of Data Warehouse Cloud (DWC).

After you have  a new SQL view, we now look into the SAP timetable for the day with the simple select statement:

SELECT * FROM "SAP.TIME.VIEW_DIMENSION_DAY"

Now we can open the data preview and see which fields are in the dimension view available and there we see a field with the name "DATE_SQL" which we can use to create the logic. So let's select only this field with the following statement:

SELECT "DATE_SQL" FROM "SAP.TIME.VIEW_DIMENSION_DAY"

So this is our start. For a week-to-date (WTD), month-to-date (MTD) and year-to-date (YTD) logic we need now further statement. So let's start with the YTD logic because every year starts on 01/01.

SELECT "DATE_SQL",
TO_DATE(YEAR("DATE_SQL")||'0101', 'YYYYMMDD') as YTD_START
       FROM "SAP.TIME.VIEW_DIMENSION_DAY"

Let me explain the logic. First, we extract the year from the "DATE_SQL" field to get the corresponding year of the data row. After that, we add here the string '0101' to build an SAP internal format of the date. For example 20220101 After we have that, we have to convert the SAP internal date format to a normal date format with the SQL statement TO_DATE.  Now, the data preview looks like this:

Besides the year-to-date (YTD) value, the month-to-date (MTD) is also similar because every month starts at the first. This is the statement:

SELECT "DATE_SQL",
to_date(year("DATE_SQL")||right('0'||month("DATE_SQL"),2)||'01', 'YYYYMMDD') as MTD_START,
TO_DATE(YEAR("DATE_SQL")||'0101', 'YYYYMMDD') as YTD_START
       FROM "SAP.TIME.VIEW_DIMENSION_DAY"

The start is similar to the YTD logic. We get the month of the corresponding date and add a zero to the month. Now we take the two right digits of this result. In case of 010 for October we take the 10 and in case of 04 for April we take the 04. After that, we add 01 to it and have for example the string '0401'. The next step is to get the year of the current date and concatenate it with the previous string to the SAP internal date format. For example 20220401. The last step is the conversion to a normal date with the TO_DATE statement. In the data preview, we have now this:

The most complex part of the blog post I mentioned earlier was the week-to-date (WTD) calculation. Because every week starts not on the first of a month, and so I looked into other SQL logics not SAP specific what I can do. And this is how it looks like:

SELECT "DATE_SQL",
to_date(add_days("DATE_SQL", -(Weekday("DATE_SQL")))) AS WTD_START,
to_date(year("DATE_SQL")||right('0'||month("DATE_SQL"),2)||'01', 'YYYYMMDD') as MTD_START,
TO_DATE(YEAR("DATE_SQL")||'1231', 'YYYYMMDD') as YTD_END
       FROM "SAP.TIME.VIEW_DIMENSION_DAY"

So let me explain it. We get the weekday of the date, for example for 15.02.2022 you get as result 1. Now, after we have the result of the weekday function, we make it negative with a minus. After that I use the ADD_DAYS function and add to the date in case of the 15.02.2022 a -1 and get the 14.02.2022 which was the week start. This is how it looks like in the data preview:

Now we have the same table as mentioned in the SAP blog post. But I now wanted also the quarter start date, so I added the following logic:

SELECT "DATE_SQL",
to_date(add_days("DATE_SQL", -(Weekday("DATE_SQL")))) AS WTD_START,
to_date(year("DATE_SQL")||right('0'||month("DATE_SQL"),2)||'01', 'YYYYMMDD') as MTD_START,
TO_DATE(YEAR("DATE_SQL")||'1231', 'YYYYMMDD') as YTD_END,
CASE right(quarter("DATE_SQL"),2)
    WHEN 'Q1' THEN to_date(year("DATE_SQL")||'0101', 'YYYYMMDD')
    WHEN 'Q2' THEN to_date(year("DATE_SQL")||'0401', 'YYYYMMDD')
    WHEN 'Q3' THEN to_date(year("DATE_SQL")||'0701', 'YYYYMMDD')
    WHEN 'Q4' THEN to_date(year("DATE_SQL")||'1001', 'YYYYMMDD')
END as QTD_START
       FROM "SAP.TIME.VIEW_DIMENSION_DAY"

So the case statement decides in case of the quarter which logic it has to use and adds for example '0401' for Q2. Here is how the data preview looks like:

Now we can use the same logic mentioned in the blog post above to get the right values. Here is the SQL code as an example copied from the other post and expanded for the QTD logic.

select "COSTCENTER",
       "BOOKINGDATE",
       "MATERIAL",
       "QUANTITY"   
 from "CSV_DATA"
 
 where "BOOKINGDATE" >= ( 
    SELECT CASE
            WHEN :DT_RANGE = 'MTD' THEN "MTD_START"
            WHEN :DT_RANGE = 'WTD' THEN "WTD_START"
            WHEN :DT_RANGE = 'QTD' THEN "QTD_START" 
            WHEN :DT_RANGE = 'YTD' THEN "YTD_START"
           END
    from "Date_Calculation"
    where "DATE_SQL" = TO_DATE(:IP_DATE)
    )
    AND "BOOKINGDATE" <= TO_DATE(:IP_DATE)
               

I defined the DT_RANGE as input parameter with type string and length 3. The IP_DATE is an input parameter with the type date. Here is the example how it could look like when I analyze the quarter-to-date data for the date 05.05.2020.

I think this is another good example of what you can do in SAP Data Warehouse Cloud. I thank Sukanya Krishnan for the original idea, but with my solution I don't have to upload a new file with new MTD/WTD/YTD values and this means less maintenance. If you have similar ideas, please share it in the comments.

• In my newsletter, you get informed about new topics
• You learn how to use Analysis Office
• You get tips and tricks about SAP BI topics
• You get the first 3 chapters of my e-book Analysis Office - The Comprehensive Guide for free