UPDATE: This is one of my older posts. This method still works, but I have since made two other periods over period comparison tutorials. Those methods might provide decent alternatives if this method doesn’t work for what you’re trying to do. A very basic and blunt way to do date range comparisons can be found here. And a more sophisticated version can be found here. Thanks for visiting my site. I hope these help!
A staple of dashboards is the standalone KPI with a red and green number below it indicating the percent change since the previous period. Usually, the period is a quarter or a month. A prime example is Google Analytic’s home page.
I have found that Tableau is terrible at this. Tableau’s greatest flaw is its lack of a built-in period over period comparison. So to do this type of work in Tableau you have to get creative with parameters and calculated fields. There are many different ways to accomplish this visualization. Below I will show the best way I have found to accomplish this goal.
The whole process requires three calculated fields and three parameters. I will be using the “EU Super Store” dataset that comes with Tableau for my example. Let’s start with the parameters.
Parameter 1 – Date Selection
This is the parameter the users will adjust to pick their current time periods. I set the date ranges to be far apart. You can set them how you choose.
Parameter 2 – N Previous Periods Back
In most cases, this will be set to 1. As we are generally only going to want to see the most previous period. but I like that this method doesn’t code in how many periods back we may want to see. I think it adds to the robustness of this method.
Parameter 3 – Period
The period parameter sets the timeframe. Again, this could be coded, but I like it as a parameter in case the period needs to be changed. Being able to quickly change between options makes life easier.
The calculated fields are where most of the action happens and I will include the code that goes with them.
Calculated Field 1 – Current_KPI_Profit
Let’s go through the logic on this as it’s a bit unintuitive. The output is the desired measure, in this case [Profit] if it meets our criteria for the dates.
DATETRUNC is an important function. What it does is take an input from period: ‘month’, ‘day’, etc. Then it formats the date variable to be like that period. For example, if DATETRUNC(‘month’, 3/14/2018) was passed the result would be “3/2018” or “March 2018”. So the first part of the AND-Statement confirms the [order date] variable equals the [order date] of the [date selected].
The second part of the AND-Statement excludes all dates occurring after that day. So if 3/14/2018 were passed as the [Date Selection]. The second part of the calculated field only keeps the data “less-than” or more normally said “before” 3/14/2018.
This calculated field gets us out current period. The first part of the AND-Statement grabs the whole period the selected date occupies. The second part excludes all data that occurs after that specific day.
IF
(DATETRUNC([Period], [Order Date]) =
DATETRUNC([Period], [Date Selection])
AND
DATETRUNC('day', [Order Date]) <=
DATETRUNC('day', [Date Selection]))
THEN
[Profit]
ENDCalculated Field 2 – Date Filter
This calculated field is very similar to the previous one. This calculated field will be used as a filter to make sure only the relevant data/dates are in the workbook. This will make more sense when it all comes together. The calculated field gets two time periods. A current period. This part of the code comes after the OR-Statement. It’s identical to the previous calculated field. The part of the code that comes before the OR-Statement keeps the previous period. Look at the code and notice it is the same as the current period except that it utilizes the [N periods Back] parameter.
(DATETRUNC([Period], [Order Date]) =
//This top part is used to calculate the previous period
DATETRUNC([Period], DATEADD([Period], -[N Periods Back],[Date Selection]))
AND
DATETRUNC('day', [Order Date]) <=
DATETRUNC('day', DATEADD([Period], -[N Periods Back],[Date Selection]))
)
OR
//This part calculated the current period.
(DATETRUNC([Period], [Order Date]) =
DATETRUNC([Period], [Date Selection])
AND
DATETRUNC('day', [Order Date]) <=
DATETRUNC('day', [Date Selection]))
//So when this is set to true it reduces the data set to just the current period and the specified previous period
//It chops up the month exactly. Because of teh "and" statements
//So if you choose August 20th. This script graphs August 1st through the 20th
//And it selects July 1st through the 20th. Assuming period = month, and N = 1Calculated Field 3 – Relative Date
Relative date just uses DATETRUNC as a reference date for the Percent change that we’ll use in the Table Calculation.
Putting It All Together
- Drag Measure Names into the Rows area
- Drag Measure Values into the Text Box
- Clear out the Metrics you don’t need and leave only “Current_KPI_Profit” and “Profit) in the Measure values Card
- Drag the calculated field “Date Filter” into the Filters Card
- Select only the Trye values
The Final step it to make Profit into a Percent Difference Table Calculation. And to compute it using “Relative Date”.
- Drag “Relative Date” to Details
- Right-Click “Relative Date” and set it to exact date. It should now be a green pill
- Right-Click “Profit” and in Quick Table Calculation select Percent Difference
- Right-Click “Profit” again and in Compute Using select “Relative Date”
At this point it’s done so all you need to do is format as you please. A trick to making formatting easier is to go to Analysis > Stacked Marks > Off. Since there is a calculated field and a decent amount going on. Turn off the setting and it will made formatting easier.
Hope this helps. Here is a link to the workbook on Tableau Public. Go ahead and download it and try it for yourself. I
f you have any questions comments or concerns feel free to leave them below.
