One of the challenges presented to DS3 this week, was for each data schooler to learn a Tableau calculation and teach it to the colleagues in the end of the week. The calculations were proposed by Emma but randomly distributed between us.
I was the lucky number 5 and that meant I had to learn and teach date functions, such as DateDiff and DateAdd.
Date calculations are used to calculate a difference between dates or to add some amount of time to an existing date in our dataset.
While many functions use date parts such as year, month, day, hour, minute or seconds in their calculations, other functions are only concerned with returning a particular date part.
DATEADD FUNCTION
The DateAdd function adds a specified time period to a given date. This function is useful when we want to calculate new dates of a date we already have in our data set, to create reference lines in a time series analysis or to create ‘new dimensions’ so we can filter our data.
It is formed by a date part followed by the increment and the known date.
DateAdd (date_part, increment, date)
- Date_part: specifies the type of time period that is being added. It is always specified in single quotes and lower case (example: ‘year’, ‘day’, ‘quarter’, ‘weekday’.
- Increment: it stipulates the exact amount of time to add to our date.
- Date: the actual date that we want to use for the addition (it can be a constant field, a parameter or another function that returns a date).
Examples:
- DateAdd (‘day’, 3, #15-04-2016#)
Tableau returns: 18-04-2016
- DateAdd (‘day’, -30, Today())
Tableau returns: Taking into consideration that today is 09/06/2016 the result will be 10/05/2016
- DateAdd (‘month’, -12, window_max (max[Date]))
Tableau returns: the date that is 12 months prior to the last date in the defined window. This calculation gives us a date 12 months’ prior the end of our data set independently of its size and the gap between the current date and the last date of the data set.
DATEDIFF FUNCTION
Calculates the time between two given dates, in order words, it is the difference between date1 and date2 expressed in units of date_part.
It returns an integer value of a specified unit of time and it is useful to create additional dimensions or metrics to our analysis.
DateDiff(date_part, date1, date2)
- Date_part: specifies the type of time period that is being returned. It is always specified in single quotes and lower case (example: ‘year’, ‘day’, ‘quarter’, ‘weekday’.
- Date1 & Date2: dates from our data set that are used for subtraction. The values can be constants, parameters or other functions that return dates.
Examples:
- DateDiff (‘day’, #June9, 2016#, #June11, 2016#)
Tableau returns: 2 (two days’ difference between June 9th and June 11th).
- DateDiff (‘day’, [invoice date], Today())
Tableau returns: the invoice ‘age’ or how long ago was the invoice produced.
- DateDiff (‘week’, #09-06-2016#, #14-06-2016#, ‘monday’)
Tableau returns: 1, because each date belongs to a different week with Monday as the start day.
- DateDiff (‘week’, #12-06-2016#, #14-06-2016#, ‘sunday’)
Tableau returns: 0, because each date belongs to the same week with Sunday as the start day.
- Logical functions (Case) using Date Functions
Case [Parameter].[Date Unit]
When ‘day’ then datediff(‘day’, [Date Order], [Date Ship])
When ‘year’ then datediff (‘year’, [Date Order], [Date Ship])
End
Tableau returns: integer, either the number of days between order and shipment or the number of years.