YTD(Year To Date), MTD(Month To Date) and Quarter wise separation of data in HANA Modelling.

[akashkannan22]

Introduction:

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.

Fiscal Year:

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$$')

 

Input Month:

START MONTH:

CALCULATED MONTH:

IN FILTER:

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

In filter:

("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)

 

Year: 

to separate year alone,

leftstr('$$inputdate$$',4) - year

 

Start Date:

if("month"<='03',"year"+'01',if("month">'03' and "month"<='06',"year"+'04',if("month">'06' and "month"<='09',"year"+'07',"year"+'10')))

 

In filter:

"startdate"<="ERDAT" and "ERDAT"<='$$inputdate$$'

 

Conclusion:

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.

Reference:

YTD