Tip Week #2 - Date Calculations

by Luisa Bez

Date calculations are useful if you want to manipulate dates in your data source. To get you started I’m going to cover five of them in this blogpost:

  • DATENAME
  • DATEPART
  • DATETRUNC
  • DATEADD
  • DATEDIFF

Before I dive deeper into each function let me clarify a term I’m going to use throughout this blog: date parts. A date part is just that: the part of a date. It could be a day, a week, a year etc. For example the date part ‘year’ of 20/06/2018 is 2018.

Most syntaxes include start of the week, but you don’t have to define that in each calculation and I haven’t done it in this post.

See the calendar below for reference.

DATENAME

The DATENAME function is very simple and returns the date part as string. This comes in handy when you’d like to know what day of the week 7 August 2018 is.

Syntax: DATENAME(date_part, date, [start_of_week])

Example:


DATEPART

DATEPART is similar to DATENAME, but it returns an integer for the date part instead of a string. Therefore Tableau interprets the result as Measure, but we don’t want to aggregated anything so we can just move it to Dimensions. It helps you to answer questions such as, ‘If the week starts on Sunday, how many days into the week is 07/08/2018?’ Note: When the date_part is ‘weekday’, the ‘start_of_week’ parameter is ignored. This is because Tableau relies on a fixed weekday ordering to apply offsets. In our case Tableau defines the start of the week as Sunday and therefore counts 3 days.

 

Syntax: DATEPART(date_part, date, [start_of_week])

Example:

 

DATETRUNC

You can use this function to truncate your date to the accuracy specified by the date part. The week trunc for example returns the first day of the week that our Order Date falls into. In this case our week begins on Monday 6th August.

Syntax: DATETRUNC(date_part, date, [start_of_week])

Example:

 

DATEADD

DATEADD adds an increment to the specified date and returns a new date. In other words, you can calculate what date it is 72 days from 07/08/2018.

Syntax: DATEADD(date_part, interval, date)

Example:

DATEDIFF

Datediff comes in handy when you’re interested in the difference between two dates. Again, you can define your date part, but this time you also add a start date and an end date to the mix. You’ll have to aggregate the result as average.

Syntax: DATEDIFF(date_part, start_date, end_date, [start_of_week]) 

Example:

That’s it and I hope you found my introduction to date calculations useful!

Fri 17 Aug 2018

Thu 16 Aug 2018