VizWiz

Launch, grow, and unlock your career in data

April 9, 2014

Tableau Tip: Analyzing Year over Year Trends with Table Calcs

14 comments
UPDATE – 10-Apr-3014:
I received some feedback from both Jonathan Drummey and Joe Mako about this blog post and some of its inaccuracies.  There are a couple of key notes:
  1. My intent was to show how you can compare the 7-day averages of two time periods. In this example, I’m calling this a Year over Year calculation, but really it’s a comparison versus 365 days ago. Small, but important distinction.
  2. The Superstore Sales data set has days that are missing, so unless you turn on domain padding, you won’t be comparing the prior 365 days that you’re expecting to compare. I’ve updated the post below.
  3. Like most things in Tableau, there are many ways to solve the same problem. Joe pointed out some things he would have done differently with the calculations. While neither of us is wrong, note that you should always look for ways to be more efficient.

April is #TableauTipsMonth, so I thought I would pick something from my backlog and get to it. Today I’ll be writing about a fairly typical scenario:
  1. Your data is cyclical through the week
  2. Daily numbers vary wildly, so smoothing is necessary
  3. You need to compare to the same period from the prior year
This can be handled pretty easily and understandably with a few table calculations. To start, let’s look at daily sales for 2013 in the Superstore Sales data set to illustrate the wild nature of daily data that many of us see.
image
This data set looks like it might be cyclical, so let’s apply a 7-day average calculation to it. We could do this via the quick table calculations on the pill, but we will want this calculation for later. Right-click on Sales in the Measures pane and choose Create Calculated field. Build this calculation:
image
Here’s what the chart looks like if I filter Order Date to 2013 on the Filter shelf (I’ve focused in on January):
image
Since I have filtered the year of Order Date to 2013 by dragging the Order Date pill onto the Filters shelf, Tableau first ran a query that returns only results for 2013 and then it will do the 7-day average calculation. We don’t want that because the first six days of 2013 will be wrong. They are wrong because they don’t contain a full 7-day date range.
To fix this, we need to change the calculation. Notice that I’m not passing a filter to Order Date inside the calculation. By passing the filter in the table calc, Tableau will return all of the data for all years, then the table calc will running after the database query, which will then provide the full date range for the 7-day average to calculate correctly.
image
And this is what the chart looks like if filter the data via the table calculation (of course you have to remove Order Date from the Filters shelf):
image
Quite a different story. The lesson here is that you need to understand how Tableau is filtering the data. Anything on the Filters shelf will filter the data before any table calcs are performed.
One thing to note, though, is that this data set does not include all dates. Therefore, we need to turn domain padding on by right clicking on the Order Date pill and checking the “Show Missing Values” option. Notice how there are now gaps in the line; that’s because Tableau is filling in the missing dates for us.
image
So that covers our 7-day average. To calculate the 7-day average for the same 7-day period 365 days ago, we only have to make a simple adjustment to the 7-day average calculation we’ve already created.
image
The only change is the start and end period inside the WINDOW_AVG function. Now drag the new measure onto the same axis as the 7-day average.
image
So now we can see how the 7-day average on an day compared to the 7-day average 365 days ago. Perfect!
Now that we have these two table calcs, we can easily compute the change between the two dates periods with another calculated field:
image
Drag this new measure onto the Rows shelf. Clean it up a bit, and we now have a nice view that answers a simple question: How are sales performing compared to last year?
image
Notice that I used an orange-blue color palette for the comparisons to the prior 365 day values. This is a good practice to employ because the colors corresponding with the colors for each line. If the bar is orange, then last year performed better and vice versa.
Download the workbook used in this example here.

14 comments :

  1. This is awesome, Thank you!

    ReplyDelete
  2. Beautiful charts.

    ReplyDelete
  3. Awesome charts. I will apply these ideas to a dashboard that I'm about to start.
    Keep up with the excellent blog.

    ReplyDelete
  4. This is very helpful, one additional question. I would like to be able to show the above results for dynamic periods, for example the trend for a year, month or just the previous week. I realize I can adjust the above calculation to reflect this, but is there another way to do this dynamically? Ideally I would just use a filter, but for the reasons mentioned in this post that is not a reliable option when using the rolling average.
    Thanks!

    ReplyDelete
    Replies
    1. I would imagine you can do this with a parameter. The parameter could have the three options you mentioned, then you could change the calculation to have a case statement with the formula for each selection.

      Delete
  5. Hi Andy,

    I need to plot only a certain data points for this graph with resect to the horizontal time axis.
    My intent is to highlight/plot only the Saturdays and their corresponding vertical value.

    How can I achieve this.

    ReplyDelete
    Replies
    1. Vanshu, you should filter to only Saturdays in that case.

      Delete
    2. This comment has been removed by a blog administrator.

      Delete
  6. Hi Andy,

    I tried filtering it to only Saturdays. But as the calculated field has rolling sum/avg (with the use of window_avg/sum). The excluded days are getting skipped from the formula.

    Please advice how can I just display just Saturdays, without taking other days out of the the formula data range.

    Thanks much

    ReplyDelete
  7. Hi there! Sorry to ask on such an old thread, but I'm really interested in how you created the % difference graph near the bottom of this post. I'm new to Tableau and trying to do something similar, but I haven't found any other sources on how to create something like this.

    Thanks!

    ReplyDelete
    Replies
    1. Hi Cassie. I've fixed the broken link so you should now be able to download the workbook to see how I did it. Or you can download it here. https://www.dropbox.com/s/d4mks75fzooafqo/7d%20Avg%20YOY.twbx?dl=0

      Delete
    2. Andy,

      Thank you so much for your reply. Much appreciated, this post was a fantastic resource!

      Delete