Jim Dehner 

 Tableau Visionary and Ambassador

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Tableau Public 

LinkedIn

Twitter

YouTube

Open for

Post Links

 Date Functions

 

I've had my "issues" with dates – maybe you have too – Date functions allow us to make spectacular charts, compare period sales, and track trends over time.  But which one do you use and when?

Dates are a very special class of dimensions – they self sequence, have a built-in hierarchy, can be used as discrete or continuous dimensions, adjust to your fiscal year start, and can be customized – pretty cool  – but the flexibility leads to confusion.  

This post will explore Date Functions – Formats and Custom Dates will be reviewed in a future article 

Starting at the beginning – dates can take on 2 different forms – 

  • Date Parts categorize data and are discrete – bar charts, period analysis
  • Date values are a continuous form – make great line charts, trending 
 

Date Part

 
Dates in Tableau have a built-in hierarchy that begins with Year and ends with Seconds
 
 
 
 
 
The various Date part functions have a similar format and truncate the date at the level selected
 
– using date part as an example – here the date dimension Start Date  is truncated at Year
The format for the level selected is always singular and is enclosed in single quote marks
 
 

Datepart('year',[start date])

 
It may be easier to see what the various levels will return from the chart below
Note the values returned using Datepart function are Integers – also note that Hour is based on a 24-hour clock 
 
 
 
Other date levels are not part of the date hierarchy but can be used in date functions. 
 
 
 
 
 
Alternately you could use  Datename – returns the actual string (text) value of the level – January, Wednesday

 

 

 
 
For all the examples I will use the Superstore dataset that came with your copy of Tableau – you will be able to re-create examples
 

1 – Use Datepart to show Monthly sales

 
Start with a simple date function that truncates the date at the month level
 

DATENAME('month', [Order Date])

 
 
Create a viz using datename on columns and sum(sales) on rows
 
 
Datename is a discrete function that defaults to a bar chart – a discrete chart type – the bars present to total sales by month for all dates in the data set – 
 
You could create the same chart simply by dragging the Order date to columns and setting the level to month-  When you do Tableau will default to a line chart – same numerical data just a continuous chart type – 
 
 
 
 
 

That's one of the confusing issues with dates – sometimes they default to discrete types and at others to continuous – easy enough to change in this example by changing the chart type to Bar – 

2 Year Month – When to use discrete chart types


If we add Year of order date you can begin to see where discrete chart types better represent the data 

This will place the Year for reach record (row) in the data 

DATENAME('year',[Order Date])

 

 

When applied to the previous month it is easy to see the progression of sales over the years and months



Adding year to the continuous chart also shows the progression but there is a discontinuity at the first of every year


The data are the same – the discrete date part calculation categorize data into year/month buckets – the bar chart (discrete) height represents to total for the month


The continuous chart type also presents the monthly total but only the marks at the month points are an accurate representation of the sales – the lines between just connect the marks and do not relate to sales on any day

3 YTD and YOY with Date Parts


Using DATEPART is one way to do a YTD and prior year to date analysis

Current Year Sales can be calculated as

if DATEPART('dayofyear',[Order Date])<=datepart('dayofyear',today())
and DATEPART('year',[Order Date])=datepart('year',today())
then [Sales]  else NULL end

 
Prior Year Sales as
 

if DATEPART('dayofyear',[Order Date])<=datepart('dayofyear',today())
and DATEPART('year',[Order Date])=datepart('year',today())-1
then [Sales]  else NULL end



then they can be presented in side by side charts as 


 

4 Day of Week Sales Heat Map

 
With the Week and Weekday options you could look at how sales vary by day of the week over the 53 week year
 
The day of the week for each record is 
 

DATENAME('weekday',[Order Date])

 
and the week number can be determined datepart
 

Datepart('week',[Order Date])

 
 
 
 

5 Days to Ship

 
 
A common metric for manufacturers is Days to Ship – here we will use it to illustrate a point about knowing the data elements in the data set
 
The function used is Datediff – it takes the difference in days (the date level) between the order and the ship date 
 

DATEDIFF('day',[Order Date],[Ship Date])

 
The function works at the row level – in the Superstore data set the data is kept at the product level – equivalent to row-level data on the invoice – and orders may have several products
 
The measure the shipping department wants to be measured on is on the order level – the LOD below will return the days to ship for the order 
 

{ INCLUDE [Order ID]:AVG(DATEDIFF('day',[Order Date],[Ship Date]))  }

 
 
 
 
It may not seem like a lot – but if the metric is a department KPI it will be noticed
 
And you could come up with many more examples – remember that date part functions create dimensions that categorize the data at the row level and can be used like any other dimension in charts, graphs, calculations, or filters
 

Date Values

 
Alternative date function formula can return a continuous value – 
 
If you are familiar with the date value and time value formulas in excel the concept is the same.  Date values increment daily time values are decimal parts of a day.  (Caution the starting point for Tableau and Excel are not the same so the actual values vary by a couple of days)
 
The same array of levels are available for date values but the functions return the first value in the level as a real date – an example will be clearer – using the same date chart as before
 
 
At each level, the datetrunc formula will take the form of 
 

datetrunc('year',[Start Date])

 
 
Datetrunc will return the starting value of the selected level of the date hierarchy as a value out to the second 
 
Review the chart below and see how it differs for the earlier date part version of the same dates – 
 
 
 
 
 

 
6 YTD using Datetrunc

 
This formula will return all records in the current year 

 
if DATETRUNC('year',[Order Date])=DATETRUNC('year',today()) then [Sales] end

 
The date level to display can be set by selecting from the drop-down list
 
 
 
And the data are plotted as a continuous line for each day of the current year – 
 
 
 

Now change the formula to include all prior years 

if DATETRUNC('year',[Order Date])>=DATETRUNC('year',today()) then [Sales] end


and at the day level, the chart becomes


It may be too busy to be practical but easily changed to months

 

Note there are no breaks at the end of each year as there were with the discrete data part function – one of the advantages of using a continuous function

7 Trend lines


Trend lines can be added to any continuous graph directly from the Analytics tab 
Place the continuous month on columns and the measure on rows – then just drag the trend line to the canvas





8 Parameter Driven Continuous Line Charts


A poster to the Forum brought a particularly interesting challenge recently – they wanted a continuous chart that was driven by a parameter to select the level of the date and a second parameter to set the number of periods in the chart

The solution combines several of the concepts discussed

First, create a parameter to change the date level from days – years

 
 

And a second parameter to allow the user to select how many periods to include in the chart




Next, create a dimension based on the parameter that will be used as the continuous date axis in the chart 

Case for dimension;

date( CASE [Date level]                    
when "Day" then datetrunc('day',[Order Date])
when "Month" then datetrunc('month',[Order Date])
when "Week" then datetrunc('week',[Order Date])
when "Year" then datetrunc('year',[Order Date]) end  )

 
 

The statement might look intimidating but all it does is use the Datetrunc function to create an axis for the chart at the level selected in the Date Level parameter.  

And finally, a filter to determine which data to include 

Parameter Drive Filter:

            CASE [Date level]

When "Day"  then   datetrunc('day',( min([Order Date (Days)])))>=datetrunc('day',dateadd('day',-[lookback period],today()))
and  DATETRUNC('day',   min([Order Date (Days)]))<=datetrunc('day',today())

when "Month"  then   datetrunc('month',( min([Order Date (Days)])))>=datetrunc('month',dateadd('month',-[lookback period],today()))
and  DATETRUNC('month',   min([Order Date (Days)]))<=datetrunc('month',today())

when "Week"  then   datetrunc('week',( min([Order Date (Days)])))>=datetrunc('week',dateadd('week',-[lookback period],today()))
and  DATETRUNC('week',   min([Order Date (Days)]))<=datetrunc('week',today())

when "Year"  then   datetrunc('year',( min([Order Date (Days)])))>=datetrunc('year',dateadd('year',-[lookback period],today()))

and  DATETRUNC('year',   min([Order Date (Days)]))<=datetrunc('year',today()) end


Now that one is messy – but there are 4 similar segments each looks at a different date level and decides of the order date is within the lookback period the user wants to see – Once again using the continuous Datetrunc function

Then it is just a matter of placing everything on the viz



and the user can now dynamically change date levels and period from the 2 parameters 



There can be many more examples but the best way to learn about date functions is to use them – 

A workbook containing the examples shown here can be found at :

https://public.tableau.com/profile/jim.dehner#!/vizhome/DateFunctionExamples/SalesbyMonth



Feel free to download it and use it as a template

Enjoy


Jim

 

 

 

Top Posts 

Leave a Reply

Your email address will not be published. Required fields are marked *