YTD, QTD & MTD.pptx

tableau

1. YTD, QTD, MTD AND DATE FUNCTIONS IN TABLEAU Presented by: PRANALI SHIVAJI SHIRURKAR 29 TH August, 2022

2. INTRODUCTION YTD : Year to Date PYTD : Previous Year to date LYTD : Last Year to date QTD : Quarter to Date PQTD : Previous Quarter to date LQTD : Last Quarter to date MTD : Month to Date PMTD : Previous Month to date LMTD : Last Month to date

3. Two types of years prevail in the corporate world. These two years are the Fiscal Year and the Calendar Year. The similarity between these years is that these lasts for 365 days or twelve consecutive months. The calendar year begins in the first of January and ends on 31 st December every year, while a fiscal year can begin on any day of the year but will end on exactly the 365 th day of that year. FISCAL YEAR Vs CALENDAR YEAR

4. YEAR January February March April May June July August September October November December YTD QTD MTD

5. DEFINITION YTD : YTD is the period between the first day of the calendar year and the current date . QTD : Quarter-to-date is a period starting at the beginning of the current quarter and ending at the current date. Quarter-to-date is used in many contexts, mainly for recording results of an activity in the time between a date and the beginning of either the calendar or fiscal quarter. MTD : Month-to-date is a period starting at the beginning of the current calendar month and ending at the current date. Month-to-date is used in many contexts, mainly for recording results of an activity in the time between a date and the beginning of the current month

6. PYTD   PYTD refers to the period beginning from the first day of the previous calendar year or fiscal year to the date equivalent to the current day from the previous year. PQTD  Previous quarter to date is the period starting from the beginning of last quarter and ending at the current day of last quarter PMTD   Previous month to date is the period starting from the beginning of last calendar month and ending at the current day of last month.

7. YTD QTD MTD 01/01/2022 + all values up to 29/08/2022 01/07/2022 + all values up to 29/08/2022 01/08/2022 + all values up to 29/08/2022 Current Year 2022 PYTD PQTD PMTD 01/01/2021 + all values up to 29/08/2021 01/04/2022 + all values up to 29/05/2022 01/07/2022 + all values up to 29/07/2022 LYTD LQTD LMTD 01/01/2020 + all values up to 29/08/2020 01/07/2020 + all values up to 29/08/2020 01/08/2020 + all values up to 29/08/2020

8. DATE_PART VALUES 'year' Four-digit year 'quarter' 1-4 'month' 1-12 or "January", "February", and so on 'dayofyear' Day of the year; Jan 1 is 1, Feb 1 is 32, and so on 'day' 1-31 'weekday' 1-7 or "Sunday", "Monday", and so on 'week' 1-52 'hour' 0-23 'minute' 0-59 'second' 0-60 'iso-year' Four-digit ISO 8601 year 'iso-quarter' 1-4 'iso-week' 1-52, start of week is always Monday 'iso-weekday' 1-7, start of week is always Monday The valid date _ part values at a glance

9. DATE FUNCTIONS IN TABLEAU DATETRUNC Truncates the specified date to the accuracy specified by the date _ part. This function returns a new date SYNTAX : DATETRUNC(date _ part, date, [start _ of _ week]) Eg: DATETRUNC(‘quarter’,#2022-08-15#) = 2022-07-01 12:00:00 AM DATETRUNC(‘year’,#2022-08-15#) = 2022-01-01 12:00:00 AM

10. DATEADD Returns the specified date with the specified number interval added to the specified date _ part of that date SYNTAX: DATEADD(date _ part, interval, date) Eg: DATEADD('month', 3, #2022-08-15#) = 2022-11-15 12:00:00 AM The expression adds three months to the date.

11. DATEIFF Returns the difference between date _ 1 and date _ 2 expressed in units of date _ part. SYNTAX : DATEIFF(date _ part, start _ date, end _ date, [ start _ of _ week]) Eg : DATEDIFF('week', #2013-09-22#, #2013-09-24#, ‘ monday’) = 1 DATEDIFF('week', #2013-09-22#, #2013-09-24#, ‘ sunday ’) = 0

12. DATEPARSE Converts a string into a date in a specified format SYNTAX : DATEPARSE(date _ format, [date _ string]) Eg : DATEPARSE(“dd.MMMM.yyyy”, “15.April.2004”) = 2004-04-15 12:00:00 AM TODAY Returns the current date. SYNTAX : TODAY() Eg : TODAY() = 2022-08-29

13. DATENAME : Returns date _ part of date as a string SYNTAX : DATENAME(date _ part, date, [start_of_week]) Eg : DATEPART('year', #2004-04-15#) = 2004 MAX : Returns the maximum of  A  and  B  ( A  and  B  must be of the same type). Returns Null if either argument is NULL SYNTAX : MAX(expression) or MAX(expr1, expr2 ) Eg : MAX(#2004-01-01# , #2004-03-01#) = 2004-03-01 12:00:00 AM

14. 8 . DAY Returns the day of a date as an integer SYNTAX : DAY(date) Eg : DAY(#2022-08-15#) = 15 ISDATE It is a condition function that returns True if a given value or string is a date. SYNTAX : ISDATE(string) Eg : ISDATE(“August 15, 2022”) = True

15. MAKEDATE Returns a date value constructed from the specified year, month, and date SYNTAX : MAKEDATE(year, month, day) Eg : MAKEDATE(2022, 8, 15) = # August 15, 2022# 11. MAKEDATETIME Returns a datetime that combines a date and a time. The date can be a date, datetime, or a string type. The time must be a datetime SYNTAX : MAKEDATETIME(date, time) MAKEDATETIME(“2022-8-15", #07:59:00#) = #08/15/2022 7:59:00 AM#

16. MAKETIME Returns a date value constructed from the specified hour, minute, and second SYNTAX : MAKETIME(hour, minute, second) Eg : MAKETIME(14, 52, 40) = #14:52:40# 13. MIN Returns the minimum of a and b (a and b must be of same type). Returns NULL if either argument is NULL SYNTAX : MIN(expression) or MIN(expr1, expr2) Eg : MIN(#2004-01-01# ,#2004-03-01#) = 2004-01-01 12:00:00 AM

17. 14. MONTH Returns the month of the given date as an integer SYNTAX : MONTH(date) Eg : MONTH(#2022-08-15#) = 8 15. NOW Returns the current local system date and time SYNTAX : NOW( ) Eg : NOW( ) = 2022-08-15 1:08:21 PM

18. 16. QUARTER Returns the quarter of the given date as an integer. SYTAX : QUARTER ( ) Eg : QUARTER(#2022-08-15) = 3 17. WEEK Returns the week of the given date as an integer SYNTAX : WEEK() Eg : WEEK(#2004-04-15) = 16

19. YEAR Returns the year of the given date as an integer SYNTAX : YEAR(date) Eg : YEAR(#2022-08-15#) = 2022