×

Data

# Questions from Training: Comparing Metrics for Same Weekday in Previous Year

//

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.

## Steve Taylor

How to Build an XmR Chart in Tableau In this blog, I’ll run through the steps to build an XmR control chart, which is typically used to determine if a manufacturing or ...
New Features in Tableau 2020.1: Dynamic Parameters and Animations It’s a new year, so like clockwork, there’s a new release of Tableau Desktop currently in beta: version 2020.1. There are a couple of ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×