YTD(Year To Date), MTD(Month To Date) and Quarter wise separation of data in HANA Modelling.
In a live connection dashboard, Standard content dashboard or in SAP HANA dashboard passing the whole data will make a lack or slow down the front-end(SAC, Lumira, PowerBi, etc). To overcome this type situation, we can use YTD, MTD, QTD concept in the HANA modelling.
A fiscal year is a 12-month period of time that a company or government uses for accounting purposes to measure its financial performance.
YTD (Year To Date):
Year-to-date (YTD) means the total of something from the first day of the year through the current date. It is a time-based measurement used in financial management and investment.
MTD (Month To Date):
MTD stands for “month to date.” It’s the period starting from the beginning of the current month up until now … but not including today’s date, because it might not be complete yet.
QTD(Quarter To Date):
QTD stands for “quarter to date.” It’s used in exactly the same way as MTD, except you’re looking at the time period from the start of this quarter until now. Be careful though: this can refer to either the calendar or the fiscal quarter.
Procedure to apply YTD:
Based on the date field in your table create a calculated column in that projection. And create a input date field(yyyymmdd) to receive the date from the user
Example: Date field – ERDAT(yyyymmd)
i) Calculated month column – leftstr(“ERDAT”,6) – CALMONTH
ii) Starting month of the year- leftstr(‘$$IP_MONTH$$’,4)+’01’ – CALYEAR
In the apply filter column –
(“CALYEAR”<= “CALMONTH”) and (“CALMONTH”>=’$$IP_MONTH$$’)
MTD(Month To Date):
Follow the same procedure that we followed in YTD. Instead of Start month of year column use start month column. So in this we will take the start day of that month to the input day.
let the date field – ERDAT
Input Day – yyyymmdd
Calculated Start Day – leftstr(‘ERDAT’,6)+’01’ – START_DATE
(“START_DATE”<= “ERDAT”) and (“ERDAT”<= ‘$$IP_DAY$$’)
QTD(Quarter To Date):
Based on the input date, we have find the Quarter which its belongs to. Then we have display that particular data of that quarter.
let ERDAT be our date field,
Month Calculated column-
leftstr(‘$$inputdate$$’,6) – month_date
then separate the month alone by using
rightstr(“month_date”,2) -month(calculated column)
to separate year alone,
leftstr(‘$$inputdate$$’,4) – year
if(“month”<=’03’,”year”+’01’,if(“month”>’03’ and “month”<=’06’,”year”+’04’,if(“month”>’06’ and “month”<=’09’,”year”+’07’,”year”+’10’)))
“startdate”<=”ERDAT” and “ERDAT”<=’$$inputdate$$’
This blog will be useful for better understanding of YTD, MTD, QTD. Feel free to ask if you have any queries and follow my page for more contents.