In the present age, data visualization tools are becoming much more popular. One such Business Intelligence tool that can help you evaluate data consistently to gain customer insights in Tableau. When it is about calculating dates in tableau, your go-to solution would be the date functions.
Tableau offers a variety of date functions, such as today, now, date name, dateadd, datepart, maketime, makedate, day, month, year, and much more. With a better and comprehensive understanding of date and functions, you would be able to build more efficient dashboards. In this article, let’s concentrate on tableau date functions and find out more about the same.
Just as the name suggests, the date functions are mainly used to work with the records of date in a data source. By using the tableau date function, you can effortlessly manipulate the value of date by making certain changes and alterations in the older ones.
Not just that, you can also search certain date values and create new ones as well.
With the tableau date function, you can apply logical and arithmetic operations on the date value according to the requirements of your analysis. Date fields are essential in almost every data set,, as without the date values, you will not have time reference in your data.
Therefore, with the help of tableau date functions, you can effortlessly create date fields and perform complex operations.
In case you are making use of a .hyper extract, the date functions can be calculated with the help of a traditional Gregorian calendar or the ISO 8601 Standard. The ISO 8601 is the international standard meant to calculate times and date that is different from the Gregorian calendar as to how the beginning of a week of a year is calculated.
In this type, a week always starts on a Monday. On the other hand, in the Gregorian calendar, you can define the day on which the week is beginning. Herein, whenever a new year begins, Week 1 of the year is calculated as the beginning, which is on the 1st of January, irrespective of whether it is a weekday or a weekend.
In the tableau, a date part has the following values:
Jotted down below are the primary tableau date functions that you can use with your dataset:
With this specific function, you can easily add a certain time period to date. You can also specify the date part that you wish to add. And, with this function, you can add the date you selected. This one is efficient whenever you would have to calculate new dates, create reference lines, set time thresholds, or create new dimensions.
Syntax: DATEADD(date_part, interval, date)
DATEDIFF helps calculate the difference of time between two dates at a level that you will be specified, such as hour, day, month, etc. By using [End Date] - [Start Date], will be giving you the precise time amount between two dates.
Syntax: DATEDIFF(date_part, date1, date2, [start_of_week])
The DATENAME function can be used to return the date part name. You can use the first argument to effortlessly specify a part of the date, and the function will accept the YEAR, MONTH, and DAY.
Syntax: DATENAME(Date_part, Date)
With tableau DATEPARSE, you get to convert a string into a certain date format. Under this tableau date function, you will use:
Below-mentioned are the acceptable formats for this function:
Date String |
DATEPARSE Function |
Display Value Returned |
3.9.1994 |
DATEPARSE (“d.M/yy”, “3.9.94) |
9/3/1994 12:00:00 AM |
3/09/94 |
DATEPARSE (“d/M/yy”, 3/09/94”) |
03/09/1994 12:00:00 AM |
03Sep1994:9:8:7.6546 |
DATEPARSE (“ddMMMyyyy:H:m:s.SSSSSS”) |
03/09/1994 9:08:09 AM |
12:10 |
DATEPARSE(“h:m”, “12:10”) |
12:10:00 PM |
The DATEPART function is used to return or extract part of a specific date. You can use the first arg to specify the part of the date. This function is capable of accepting YEAR, MONTH, DAY, and more. For instance, if you have chosen Month as the date part, the function will then return a year from the given date.
Syntax: DATEPART(Date_part, Date)
The DATERUNC function is used to return the first day of a certain date part. You can use the first arg to specify the date part and the function will accept YEAR, MONTH, DAY, and more.
Syntax: DATETRUNC(date_part, Date)
The DAY function is to either return or extract the Day number from a specifically given date.
Syntax: DAY(Date)
In case you are looking forward to checking if the provided string is a date or not, this tableau date function will be used. It may return either true or false.
Syntax: ISDATE(string)
The MAKEDATE function of tableau is used to return the date from a given year, month and day.
Syntax: MAKEDATE(year, month, day)
With this tableau date function, you can return both date as well as the time from the given date and time data.
Syntax: MAKEDATETIME(Date, Time)
Another one that you can use is this MAKETIME function. This one is used to return time from an hour, minute as well as seconds.
Syntax: MAKETIME(hour, minute, second).
This tableau date function is used to return the bigger date when two of them are compared. However, to make the comparison successfully, the two dates should be of a similar type.
Syntax: MAX(expression) or MAX(expr1, expr2)
With Tableau MIN, you can get the return of a smaller date when two of them are compared. Although this function is the opposite of max, the syntax remains somewhat similar.
Syntax: MIN(expression) or MIN(expr1, expr2)
The month function is primarily used to return the Month number from a date that is available in the data.
Syntax: MONTH(Date)
This is for returning the current time and date. This adds the hours, minutes as well as seconds. The NOW tableau date functions are useful in case you would need more granular information. It is also useful if you are working with intra-day data or such data that gets updated in almost real-time throughout the day.
Syntax: NOW()
Another significant function on the list is this tableau QUARTER. Basically, this one is used to return the quarter of the given data. Whatever results you get in return are in the form of an integer.
Syntax: QUARTER()
TODAY is useful when it comes to creating reference lines that show the relation in the data to today (for example, your deadline that is in relation to today), along with the calculations that are based on the date of today.
Syntax: TODAY()
Just as the name suggests, this tableau date function helps you return the week of a specific given date. The returns or outcomes that you get are in the form of an integer.
Syntax: WEEK()
This function is mainly used to return the year from the date given in the data. You will get the return in the form of an integer. For instance, YEAR(#2021-03-16#) = 2021
Syntax: YEAR(Date)
Now that you have a complete, comprehensive understanding of tableau date functions, you are ready to use them with your data. Now, get to know more about them and create date functions or use a variety of them in date fields without any worries.
Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:
Name | Dates | |
---|---|---|
Tableau Training | Dec 07 to Dec 22 | View Details |
Tableau Training | Dec 10 to Dec 25 | View Details |
Tableau Training | Dec 14 to Dec 29 | View Details |
Tableau Training | Dec 17 to Jan 01 | View Details |
Madhuri is a Senior Content Creator at MindMajix. She has written about a range of different topics on various technologies, which include, Splunk, Tensorflow, Selenium, and CEH. She spends most of her time researching on technology, and startups. Connect with her via LinkedIn and Twitter .
Sunday | 15th Dec
08:00 PM IST | 09:30 AM EST