Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.
This is a question that came up in a class I taught to a commercial airline who were looking at their web analytics data, I also taught it in a colleague’s class dealing with car parking at airports:
“How can I compare a metric against the same weekday in a previous year?”
Do you want to investigate a metric at the day level, but the metric is vastly different on weekends? If yes, then doing a simple year-on-year comparison of the same date will not compare like with like. For example, comparing the 20th January 2018 (Saturday) to the 20th January 2017 (Friday) wouldn’t lead to useful insight in your report.
Date Table to the Rescue
One possible solution involves that repeatedly useful item – a date table. This will be a separate table in your database, or even just a well-maintained Excel sheet, containing every calendar date with extra information (such as whether it’s a weekend day or a national holiday). Into my date table, I’ve added a column called [SameWeekdayPrevYear] which was calculated in SQL (as that’s where my date table is stored) using:
DATEADD(DD, -364, d.Date)
To calculate the difference between sales for a particular order date and the equivalent order date on the same weekday of the previous year, I joined my date table to my orders table on [Order Date] = [Date]. I then brought in another instance of my Orders table and joined that to my DateTable on [SameWeekdayPrevYear] = [Order Date (Orders1)]:
As I only want to return the sales for the same weekday from last year, I can hide all the other fields from my Orders1 table and rename the remaining fields: Sales (Orders 1) becomes [Sales Same Weekday Prev Year], and Order Date (Orders 1) is renamed [Order Date Same Weekday Prev Year].
This makes it straightforward to see the difference in sales between any date and the corresponding weekday of the previous year, using the following calculated field:
If you want to implement this in your Tableau reports, you can download and use my sample workbook below to help you. The data source for both the tables and SQL query is the Sample Superstore, which is available from the Saved Data Sources in Tableau Desktop.
