Tableau 5 Minute Tip: Rolling 12 Months vs Previous 3


Facebooktwittergoogle_pluspinterestlinkedinmailFacebooktwittergoogle_pluspinterestlinkedinmail

rolling12months

A question I get asked often is how do I create a rolling 12 months chart and also show the previous year. In this tutorial I am going to use the EU Superstore to demonstrate who this can be done with a series of simple steps.

Let us start by accessing the EU Superstore and then:

  • Drag Order Date into the Filters pane
    • Choose Relative date
    • Select Ending Date
    • Click Ok
  • Drag Order Date into Columns
    • Right click and choose Month
    • Right click and Choose Continuous
  • Drag the Sales Measure onto Rows

You should now see the following:

rolling12months1

Create a calculated field

rolling12months2

The LOOKUP function is a Tableau calculation that allows you to look up a previous value. In this example we are going to the LOOKUP function to get the SUM([Sales]) 12 positions ago i.e. 12 months.

Now we are going to use this Sales (Previous Year) by

  • Dragging the Sales (Previous Year) into Rows to the left of SUM(Sales)
  • Right Click on SUM(Sales) and select Dual axis
    • This shows both the SUM(Sales) and Sales (Previous Year) chart
  • On the canvas, right click on the Sales axis label and select Synchronize axis
    • This ensures that the SUM(Sales) and Sales (Previous Year) have the same axis values.

You should now see the following

rolling12months3

NOTE: the 12 nulls have appeared because the Sales (Previous Year) will produce null values when the year is 2011 since there are no values for 2010.

Now we are going to close our window size so that we will only see the current 12 months by creating the following calculated field:

rolling12months4

The LAST() function is a table calculation that allows us to limited the number of visible items.

  • Drag the Last Calculated field on the filter
  • In the range select 0 and 12

After this you should now see the following visualization

rolling12months5

If we apply some formatting we can not get our final visualization we will get the following

rolling12months

I hope you enjoyed the Tableau 5 Minute Tip. If you have any questions or have ideas for tutorials please find me on twitter at thoang1000

Facebooktwittergoogle_pluspinterestlinkedinmailFacebooktwittergoogle_pluspinterestlinkedinmail

Toan Hoang

About Toan Hoang

Toan Hoang is a technology enthusiast with a broad understanding and appreciation of most aspects of technology but with a passion for Business Intelligence Solutions and Technologies, Data Management and Web Programming.


Leave a comment

Your email address will not be published. Required fields are marked *

3 thoughts on “Tableau 5 Minute Tip: Rolling 12 Months vs Previous

  • Rajeev Pandey

    Hi Mr. Toan.
    Another great tutorial and its very useful as well.
    Just wanted to highlight a typo “If we apply some formatting we can not get our final visualization we will get the following” , i think you mistakenly said “not” in above mentioned sentence.
    In addition to that, the last screenshot contains Month value from March but we already applied filter for Last () 12 months . So I think it should show Dec 2013 – Dec 2014.Please correct me if my understanding is wrong.

  • Nishant

    Hi,
    Very Simply done. Great !!!
    When I tried to recreate it using the super store data the ‘Sales Previous Year’ is not showing when I drag it in the row.
    I cannot find out where I am doing the mistake. Can you suggest???