Friday, 19 May 2023

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

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:

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

START MONTH:

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

CALCULATED MONTH:

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

IN FILTER:

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

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

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

then separate the month alone by using

rightstr(“month_date”,2) -month(calculated column)

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

Year: 

to separate year alone,

leftstr(‘$$inputdate$$’,4) – year

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

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’)))

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

In filter:

“startdate”<=”ERDAT” and “ERDAT”<=’$$inputdate$$’

SAP HANA Cloud, SAP HANA Career, SAP HANA Skills, SAP HANA Jobs, SAP HANA Tutorial and Materials, SAP HANA Certification, SAP HANA

No comments:

Post a Comment