Introduction to Tableau Date Functions
Tableau Date Functions are inbuilt functions present in Tableau which enables the user to manipulate the data records in a data source. These date functions allow users to create basic date operations on the records and also create complex date expressions. Tableau provides with many date functions like DAY, MONTH, YEAR, DATEDIFF, DATEPART, DATEADD, DATETRUNC, DATENAME, MAKE DATE, MAKE TIME, NOW, TODAY, etc.
Creation of Input for Tableau Date Functions
Each data source in tableau which basically points to a database where records are stored can store data fields in a different way. The fields are imported to the tableau as a text string or numerical values. These interpretations follow a set of steps that are used by Tableau to distinguish and differentiate between the date and other fields. These steps can be summarized as follows:-
1. Verification of Date Fields
Verifications are required to distinguish and identify if the records contain the date, this can be done by identifying if the date string is present in the connection source or if the string is visible in the dimension plane. This step help in identifying non-date records from date records.
2. Change the Data Type of the Field
The next step in this process is used when the data needs to be corrected for e.g. if the record contains much null value. These fields are transformed to string type in nature and fed to Dateparse function.
3. Parsing the Date Format
There are infinite array formats present in the data records, The Dateparse method is used to define the parts of the date. This creates a map for Tableau to translate the string fields to date format. This map is called Format for tableau.
4. Defining the Date Function Expression
In case the data being used is not of string type and belonging to any other format like numeric, the DATE function is used to convert the expression to a date type format. Tableau creates a new date field in the data source.
Different Types of Tableau Date Functions
1. DAY – The DAY function is used to return the day number from the Date String. The Syntax is as follows:- DAY(Date)
2. MONTH – The MONTH function is used to return the month number from a given date in tableau. The syntax is as follows:- MONTH(Date)
3. YEAR – The YEAR function is used to return the year from the given date. The syntax is as follows:-YEAR(Date)
4. DATEDIFF – Returns the difference between the dates defined in the parameters. The syntax is as follows: – DATEDIFF(depart, date1, date2, [start of week (optional)].
5. DATEPART – Returns the date part of a date as an integer. The syntax is as follows DATEPART(depart, date, [start_ of_ week(optional)])
6. DATEADD – Returns the date with the interval specified to be added to the data fed as input. The Syntax is as follows:- DATEADD(depart, interval, date)
7. DATETRUNC – The DATETRUNC truncates the specified date to accuracy specified by the date part. This function returns a new date as output. The syntax is as follows:- DATETRUNC(depart, date, [start_ of_ week(optional)])
8. DATENAME – It returns the date part component of the supplied date as a string for its output. The syntax is as follows:- DATENAME(depart, Date, [start_ of_ week(optional)])
9. MAKE DATE – The MAKE DATE function returns a visualization of the date value developed from the input year, month and date. The syntax is as follows:- MAKE DATE(Year, Month, Day)
10. MAKE TIME – The MAKE TIME function returns a visualization of a date value which is a result o the provided hour, minute, second reference. The syntax is as follows:- MAKE TIME(hour, minute, second)
11. NOW – The Now function returns the present date and time corresponding to the PC the user is logged on to… The syntax is as follows:- NOW()
12. Today – Returns the current date as an output for the assigned program. The syntax is as follows:- TODAY()
13. MAX – Returns the maximum value of the compared entities, this function is generally used for comparison of numeric expression however the same is valid for a date as well. For null exception Null is granted (MAX(date1, date2))
14. MIN – Returns the maximum value of the compared entities, this function is generally used for comparison of numeric expression however the same is valid for a date as well. For null exception Null is granted (MIN(date1, date2))
Examples of Date Functions in Tableau
| Function | Example | Output |
| Day | DAY(#2019-06-03#) | 3 (output is an integer equivalent to the day in the provided data) |
| Month | MONTH(#2019-06-03#) | 6 (output is an integer equivalent to the month in the provided data) |
| Year | YEAR(#2019-06-03#) | 2019 (output is an integer equivalent to the Year in the provided data) |
| Date diff | DATEDIFF(‘week’, #2019-06-01#, #2019-06-04#, ‘Monday’) | 1 (because when the start of the week is Monday then 04 June (Tuesday) and 01 June (Saturday) are in different weeks ) |
| DATEDIFF(‘week’, #2019-06-01#, #2019-06-04#, ‘Sunday’) | 0((because when the start of the week is Sunday then 04 June (Tuesday) and 01 June (Saturday) are in same week weeks ) | |
| DATEADD | DATEADD(‘month’, 5, #2019-06-01#) | 1/11/2019 (5 months are added to the date ) |
| DATEPART | DATEPART(‘year’, #2019-06-01#) | 2019( returns the requested depart as an integer) |
| DATEPART(‘month’, #2019-06-01#) | 6 ( returns the requested depart as an integer) | |
| DATETRUNC | DATETRUNC(‘quarter’, #2019-06-01#) | 4/1/2004 00:00:00 AM (truncates date to the limit specified by the depart) |
| DATENAME | DATENAME(‘year’, #2019-06-01#) | 2019(return the date part of the date as a String) |
| MAKEDATE | MAKEDATE(2019, 6, 3) | #June 3, 2019# (function returns a constructed date from the supplied input) |
| MAKE TIME | MAKE TIME(13, 40, 25) | #13:40:25# (function returns a constructed time from the supplied input) |
| NOW | NOW( ) | 2019-06-03 1:10:45 AM (current date and time value are printed) |
| TODAY | TODAY( ) | 2019-06-03 (current date is printed) |
| MAX | MAX(#2019-06-01#, #2019-06-04#) | 6/4/2019 12:00:00 AM (maximum value among the option is printed) |
| MIN | MIN(#2019-06-01#, #2019-06-04#) | 6/1/2019 12:00:00 AM (Minimum value among the option is printed) |
Conclusion
The Date functions in the tableau are used to carry out various logical and arithmetical operations with dates. The results of the functions are determined by the output type of functions. Some functions may provide for the same logical functions however the difference may be observed among the type of the output as can be seen in DATEPART and Year function in the table above.
Recommended Articles
This is a guide to the Tableau Date Functions. Here we discuss the concept, example and the different types of date functions in tableau. You can also go through our other Suggested Articles to learn more –
