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:
Create a calculated field
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
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:
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
If we apply some formatting we can not get our final visualization we will get the following
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