Dates are one of the key fields which is extensively used in most of data analysis scenarios. Tableau provides a large number of inbuilt functions involving dates. One can do simple date manipulations like adding or subtracting days from a date and also create complex expressions involving dates.
Step 1: To explore and create the date functions, use the Calculated Fields. To bring up the Calculated Field window, right-click anywhere in the Data window i.e. sidebar to bring up that menu. Select Create calculated field…
Step 2: The above step opens a calculation editor which lists all the functions that is available in Tableau. If you right-clicked on a particular dimension or measure to create your calculation, then it will appear in the Calculated Field window in the formula text area. You can change the drop-down value and see only the functions related to Date.
Step 3: After selecting the Date, it lists all the functions available under it’s category. On selecting each function, it describes about what the function does with an example so it would be easy to use the same.
Step 4: Now that you have got your functions selected, lets have some knowledge about the potential values for date_part, which is a constant string argument used in many of the Date Functions.
- Year – Four digit year representation
- Quarter – 1 to 4
- Month – 1 to 12 or by name i.e. “January” , “February” and so on
- DayofYear – Day of the year; Jan 1 is 1, Feb 1 is 32, and so on(1-365)
- Day – 1 to 31
- Weekday – 1-7 or by name “Sunday”, “Monday”, and so on
- Week – 1 to 52
- Hour – 0 to 23
- Minute – 0 to 59
- Second – 0 to 60
Step 5: When you are using Date functions you will sometimes want to use literal expressions which signifies a constant value that is represented as is it. Date literals are signified by the pound symbol (#). If you wanted to input the date “July 25, 2008” which would be interpreted a a string, as a literal date you would type #July 25, 2008# which is equivalent to using a date function to convert the argument from a string to a date.
Step 6: Now that you know the different values, let’s have a look at the Date Functions.
1. DATEADD Function :
DATEADD(date_part, interval, date)
The DATEADD function allows you to specify a portion of a date and increase it. The number that you use for the interval will change the date by increasing the date_part where the type of increment is specified.
Example : DATEADD (‘day’, 15, #2014-09-15#) = 2014-09-30 12:00:00 AM. This expression adds 15 days to the date #2014-09-15#
2. DATEDIFF Function :
DATEDIFF(date_part, date1, date2, start_of_week)
This function allows you to return the difference between date1 and date2 expressed in units determined by date_part. The start_of_week parameter is optional, and if it is not defined, then the start of the week is determined by the associated data source.
Example : DATEDIFF ( ‘day’, #2014-09-15#, #2014-09-20#) = 5
3. DATENAME Function :
DATENAME(date_part, date, start_of_week)
You can use this function to return the date_part parameter of the date as a string. Again, the start_of_week parameter is optional.
Example : DATENAME (‘month’, #2014-09-01#) = “September”
4. DATEPARSE Function :
This function essentially works in the reverse of DATENAME by converting a string into a date/time with your specified format. If the string does not match the date/time format, then it will return a value of Null.
Example : DATEPARSE (“dd.mmm.yyyy”, “29.september.2014”) = #29-09-2014#
5. DATEPART Function :
DATEPART(date_part, date, Start_of_week)
The DATEPART function allows you to return a specified date_part as an integer. Again, the start_of_week parameter is optional.
Example : DATEPART(‘year’, #2014-09-30#) = 2014
6. DATETRUNC Function :
DATETRUNC(date_part, date, Start_of_week)
This function truncates the date to the accuracy of the date_part that you specify in the function. In other words, it rounds towards that date_part.
Example : DATETRUNC(‘quarter’, #2014-09-29#) = 2014-07-01 12:00:00 AM
7. DAY Function :
This function returns the day of the specified date as an integer.
Example : DAY(#09-29-2014#) = 29
8. ISDATE Function :
This is a logical test that is also included in the list of Logical Functions. It tests a string to determine if it is a valid date (true/false).
Example : ISDATE(“September 29, 2014”) = true
9. MAX Function :
MAX(expression) or MAX(expr1, expr2)
The MAX function returns the maximum of a single expression across all records or the maximum of two expressions for each record. The two arguments must be the same type. This function will return a value of NULL if either argument is NULL.
Example : MAX(#2014-09-15#, #2014-10-15#) = 2014-10-15 12:00:00 AM
10. MIN Function :
MIN(expression) or MIN(expr1, expr2)
Like the MAX Function, the MIN function returns the maximum of a single expression across all records or the maximum of two expressions for each record. The two arguments must be the same type. This function will return a value of NULL if either argument is NULL.
Example : MIN(#2014-09-15#, #2014-10-15#) = 2014-09-15 12:00:00 AM
11. MONTH :
This function returns the month of the specified date as an integer, just like the DAY function does for day.
Example : MONTH(#09-29-2014#) = 9
12. NOW :
Returns the current date and time.
Example : NOW() = 2015-16-05 12:00:00 AM
13. TODAY :
Returns the current date.
Example : TODAY() = 2015-16-05
14. YEAR :
The YEAR function returns the year of the specified date as an integer.
Example : YEAR(#09-29-2014#) = 2014