Been a while since I wrote a Tableau Tip, these are always simple, hopefully useful and stem from day to do day questions I get asked.

This week someone asked me:

-“I have this timeline where I show sales, would it possible to show an average for 2016 and an average for 2017?”

-Why do you need to show them separately? – I asked

-“We have recruited 2 new sales people, and 2017 has seen a big increase in our sales, therefore showing an average for both 2016 and 2017 is not a true reflection of the business.”

Here’s an example of how that would look like using some made up data. The chart will always display the previous year and year to date so that we can compare this year’s performance against the previous year. We can see the increase in January, a product of the expanded sales team.

If we added an average across the timeline it would look like this:

The average for the view above is 40,825 and due to the new sales in 2017 it skews the view, showing the whole of 2016 as under the average for the period in the view.

TIP

Here’s how you do it:

The calculated field above looks at whether the Year in the Date field matches the current year minus 1. Resulting in 2016. If it matches it, we subsequently calculate an Window Average based on Sales, starting on the first month in the view and ending 12 months later, i.e. Dec of the year in question.

Here’s Tableau’s description of the Window_Avg calculation

And the resulting view using the new calculated field as a reference line:

A couple points:

  • You can replace Today() with {MAX(DATE)} in case your data is not updated to 2017, in which case it looks at the latest year in your data source. 
  • The reason we match the year first is so that our average line does not spill over into 2017 as it would if we didn’t filter by year using the IF statement. The average value would still only take into account previous year Jan-Dec but the line would show across the view like in the example below. Orange line is just WINDOW_AVG(SUM([Sales]),FIRST(),FIRST()+11).

The next step is to do the same for the current year.

The difference from the previous calculated field is that we just look for today’s year and the start and finish of our window average. In this case the 13th month (Jan) of my view and the latest month of my view (Last()), in order to keep the calculation automatically updated.

When we combine the two averages in the view it shows a very different picture from the one we’ve seen up top. 2016 average was 37,884 and 2017 is currently 49,650. It also shows that in both years the month of February is below average while January and March are above in both years.

 

I hope you have found this useful, please leave any comments or questions below.

Thank you for reading

David