How to create a YoY line graph in Tableau?

Line graph is simple, neat and one of the most popular charts that I use in my work. Today, I want to create a line graph to compare daily revenue with the same day last year. The challenge is that Tableau does not have Month/Day date format. See below:

Screen Shot 2017-06-21 at 8.32.19 PM.png

I have been tried several ways. Now, I will show you the simplest way, which are only two steps.

Step 1: Create a Month/Day field. This is actually not a date, but a string.

Screen Shot 2017-06-21 at 8.40.37 PM.png

Step 2: Drag Month/Day to Columns shelf, and Revenue (anything you want to compare) to Rows shelf. Graph type should be Line. Then drag YEAR(Date) to Color.

Screen Shot 2017-06-21 at 8.45.18 PM.png

It is basically done! Really simple huh?! This method also take care of 2/29/2016. You may see a gap between 2/28/2017 and 3/1/2017 because 2017 does not have 2/29. This is what we expect to see. Some people use Lookup function, which may have issue when we have different number of days between two years.

Screen Shot 2017-06-21 at 8.52.23 PM.png

For this graph in my work, we show year over year average based on rolling 7 day average, which uses a table calculation. I will show you how to do it by two steps as well.

Step 1: Right click the field on Rows shelf, then select Add Table Calculation.

Screen Shot 2017-06-21 at 9.00.07 PM.png

Step 2: The Calculation Type is Moving Calculation. Summarize values using Average. Previous values should be 6 and next values should be 0 because I need rolling 7 days. Compute using Table (across) based on Month/Day because Month/Day is on Columns shelf.

Screen Shot 2017-06-21 at 9.02.55 PM.png

One more thing: by doing this, the line graph will show up until 6/26/2017 (Today is 6/21/2017) due to moving 7 days average table calculation.

Screen Shot 2017-06-21 at 9.17.06 PM.png

I would like to show data until yesterday (6/20/2017). We can use a Lookup function to control the end date.Screen Shot 2017-06-21 at 9.12.56 PM.png

Drag Lookup Date function to Filters shelf. Select True. Then the line graph will be:

Screen Shot 2017-06-21 at 9.16.02 PM.png

Now, revenue year over year trend by rolling 7 day average is done!

Screen Shot 2017-06-21 at 9.20.08 PM.png

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s