I recently completed a project for a client who asked me to create a flexible date parameter that allow users the flexibility to conduct a Year on Year (YoY) and Rolling Period (RP) analysis based on the start and end dates they choose. This sort of analysis is common in Google Analytics, which they wanted me to replicate using Tableau.
Specifically, the client wanted to see (based on the options toggled):
- Movement (%) between current period and previous period
- The overlay of current period and previous period as a line graph to show trending
This seemed like a very useful date feature that would be applicable across many businesses, so I thought it would be a good idea to write an article about it and share it with the community. I will not detail in this article how to create the first analysis (% movement), but instead focus on the second part which is the tricky bit. If you would like to receive the Tableau file itself, please fill out the form at the end of this blog.
Let me explain what is meant by “overlay”. Let’s say we want to see how sales is trending year on year and period on period. Looking at the screenshot below, I want my users to be able to:
- Select a start and end date (date parameter)
- Select either YoY or Rolling Period analysis (string parameter)
Sales for current period (i.e. start and end dates selected) will be “overlayed” (stacked on top of one another) along the same axis as the “previous period”.
Explain “previous period”
This is what I mean by “previous period” if you select start date: 13/01/2013 and end date: 20/01/2013:
Current period date range: 13/01/2013 – 20/01/2013
Previous period date range: 13/01/2012 – 20/01/2012
The number of days falling in between 13/01/2013 – 20/01/2013 is 8 days, inclusive. To get the previous date range we need to count backward from 12/01/2013.
Previous period date range: 5/01/2013 – 12/01/2013
Now that we’ve seen current and previous period in context, let’s do a check of values and how they should align.
Using the sample Superstore dataset to check the Sales values (with a simple ‘range of dates’ filter), the values are as follows:
Here’s how the axes should align (overlay):
In Tableau, does it actually align that way?
Here’s the screenshot so you don’t have to scroll to the top again….
The YoY analysis is more straightforward so I’ll leave it up to you to do your own spot check.
I’m now going to show you how to create this dynamic trend analysis in Tableau.
SHOW ME HOW IT’S DONE…….
Step 1 – Create the parameters
First we need to create 3 parameters:
- “Analysis Type” – String parameter allowing users to pick between YoY and Rolling Period
- “Start Date” – Date parameter
- “End Date” – Date parameter
(For simplicity, I’ve set it to Allowable values = ‘All’, without restricting any dates to pick from).
Step 2 – Define “current” vs “previous”
We then need to link the parameters above with a calculated field. For each analysis type, we also need to define what constitutes “current period” and “previous period”.
This is done by creating a calculated field below. Yes it’s long, so let’s break it down by analysis type.
Calculated Field “Analysis Periods”
To explain this part in layman’s terms…
If Order Date is in between or equal to Start Date and End Date (date parameters) then it falls in the current period.
If the Order Date (plus one year) falls in between the Start Date and End Date, then it falls in the previous period. This part is rewinding Order Date by one year.
First part computing current period is the same as for YoY above.
The second part, previous period, is best explained graphically……
This is what’s happening:
Step 3 – Create a ‘unified’ dummy axis
This step creates a dummy axis to align the axes for current period and previous period.
Again it’s done via a Calculated Field:
First of all, let’s take a look at the two calculated fields. The aim is to get the previous period Start and End Dates for YoY.
- [Ref Start Date_YoY]
Syntax: DATEADD(‘year’,-1,[Start Date])
This just rewinds the year of your selected start date by one year.
- [Ref End Date_YoY]
Syntax: DATEADD(‘year’,-1,[End Date])
Same as above, but this time rewinding the selected end date by one year.
Graphically this is what the equation is saying (it’s essentially creating a fake axis to align the current period and previous period):
There are two calculated fields here. Its purpose is to retrieve previous period Start and End Dates for Rolling Period.
- [Ref Start Date_RP]
Syntax: DATEADD(‘day’, -(DATEDIFF(‘day’,[Start Date],[End Date])),[Start Date])
This is rewinding from the Start Date selected, in particular, the number of days in between Start and End Dates.
- [Ref End Date_RP]
Syntax: DATEADD(‘day’, -DATEDIFF(‘day’,[Start Date],[End Date]),[End Date])
Same as above but for End Date.
Here’s the graphical explanation:
If you don’t minus the revised Start Date by 1 day, then this is what will happen (i.e. incorrect alignment):
Try it in the .twbx attached yourself.
Step 4 – Drag everything onto the view
- Sales field onto Rows
- Unified ‘dummy’ axis onto Columns (yes it’s not an axis yet but I will explain later)
- Analysis Periods onto Filters shelf (Show the filter and untick ‘Null’); and Colour marks card
Why is your ‘dummy’ axis not actually an axis?
At the moment, it is a continuous measure with all its values summed and disaggregated by the Analysis
Periods you’ve ticked. When you drag a measure onto Columns and another measure onto Rows, a scatter plot is created to show the relationship between two different variables. We can force this into an axis by changing the aggregation from sum to a dimension (make sure you keep the axis itself as Continuous so let it be…don’t accidentally tick Discrete):
Dimensions slice n’ dice / partition a view. So what you’ve done is force this part of the syntax (boxed below) to stop summing and turn the difference into individual incremental steps that act like an axis.
You also need to make sure that you’ve got the chart type set as line graph.
End result below:
In the actual dashboards I built for my client, I also unticked “Show Header” for both the X and Y axes as they were more concerned about the comparison between the current and previous period.
I hope that was helpful, and that you stay tuned for other neat tips n’ tricks!
If you would like for me to email you the Tableau Reader file itself, please fill out your details here and I will get this across to you shortly.