Dates Functions and Other Necessary Evils

Dates Functions and Other Necessary Evils

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 review 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 begin 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 

There are also some date levels that are not part of the date hierarchy that are useful as you will see later 

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 which 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 set 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 present the monthly total but only the 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 the 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]))  }

May not see 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 days)

The same array of levels are available for date values but the functions returns 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 - 

Clearly the values are different - 

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 selection 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 is really 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 by 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 a template

Enjoy
Jim

Next: Date Formats and Custom Dates

FAQ Series - Show - Hide Filters

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  -   

Show - Hide Filters

Often we will see a Forum question on how to "Filter the view without filtering the underlying data?"  Really?  

What's really going on ? 

Load a data set into Tableau, open the Data Source tab and look at the data - It is the complete data set 

Now create a new worksheet with a simple chart 

Open the Analysis drop down and select View Data

The window that opens is the "Underlying Table" for the worksheet

The table is a subset of the full data set - 

In Tableau each worksheet has its own underlying table.  As you bring dimensions and measures to the rows, columns and marks card they "Filter" the full data set - what is left is the underlay table for the worksheet and it is the only data from the data set you loaded available for that sheet.

So what - 

When you add filters to the viz it does affect the underlying table so it is not possible to "Filter the view without filtering the underlying data"  but it is possible to use a table calculation to Hide data from the viz.  

I like to think of it as creating a viewing window. Data that is in the window can be seen - that outside the window is hidden

Lookup() Function

This example uses Superstore data and presents the running total of Sales by Category over all the Months-Years in the data set - It looks like this

Now you only want to show the running total for 2019 but you want to include the data form 2016-2018 ("Filter the view without filtering the underlying data").  Using Lookup() we will create a window based on the custom format at the year level of the Order Date 

Create a custom date on Year - we want to "Filter" by year - 

Open the Date pill then Create and then Custom Date

When the window opens Select Year from the Drop Down and the Date Part button (make the date discrete)

Now use Lookup - a table calculation - to read the year of each date in the table individually - This works much like a Lookup in excel - in words it says " from the Order Date Years measure - return the value in the current cell"  Note - the attr() all table calculation must be an aggregate

Then we add a conditional statement that will look each value of the actual Order Date (Years)  either assign it as Show or Hide

Create the viz.  

The real order date goes on columns and the running sum of sales on rows
Add the Lookup Filter to the filter shelf and set to "Show" and calculate across (or by Order Date)

and this is the result

The "window" is hiding January 2016 through December 2018 but allowing the 2019 data to be seen.  The date is not really filtered out - the lookup filter window just hides the value you don't want to see

Last Function 

Frequently a user wants to compare the latest sales to the prior year same period - but they only want to see the last few weeks (or months). A solution is to use Last() - another table calculation - to create a dynamic viewing window -


To show only the last 6 weeks create a  "Last Show/Hide Filter" 

Adding a filter based on the last month date in the data base makes the viz dynamic for the YoY calculation

Then create the viz

The can be any level of discrete date parts - add a year filter to only include current and last year and set the Show/Hide filter to Show and the window will include the last 6 data part values - here weeks 

The viz will dynamically adjust to the last 6 weeks when the database is updated  

These are just 2 examples - obviously there can be many more.

It is important to remember that table calculations are used to create the viewing window. They are at the bottom of the Order of Operation (a topic for another day) and work on the final fully filtered table that underlays the specific worksheet 


Hope this helps 

Jim