In Tableau there are two main methods for calculating a year over year change:
- YoY Growth Table Calculation method
- Using standard calculated fields to calculate Year on Year Growth
Year over Year Growth Table Calculation
First to look at the Table Calculation. If wanting to do year on year change on a line chart, Tableau makes it very simple. Use a built-in Quick Table Calculation.
Set up a line chart with time (months is what I’ll be using in this example as it’s the most commonly used and easy to visualise) on the X-axis and a measure, such as sales, on the Y-axis.
Drag the Year into the colour shelf so you have two years on the same graph.
You should have something like this with each line being a year:
Tableau has an in-built set of table calculations, one of those being Year on Year Growth. To get the chart to show the growth instead of the actual numbers is very straightforward.
- Left click on the little arrow on the right of the green pill in the rows shelf.
- Go to Quick Table Calculation
- Click on Year Over Year Growth
This will give you a single line showing the growth for each month. It’s as simple as that, a year on year calculation created in a few short steps.
Show the actual numbers for both years and the YoY growth % on the same chart is where Tableau comes into it’s own.
Note: when not at the end of the year, showing Year on Year change in a line chart can show a line at 0, when actually it is unknown, for example, the date hasn’t happened yet. It is simple to remove the Null values from the line chart using a filter.
Show the YoY Growth on the labels
As above, create the standard line chart without the YoY change.
Trend both years in the chart, as in the diagram.
Then also place SUM([Sales]) on the label shelf. To do this hold down the ctrl key and drag the green SUM([Sales]) pill from the Rows shelf into the Label shelf on the left of the screen.
Now the SUM([Sales]) is on both Rows and Label.
Now, with the SUM([Sales]) in the Label repeat the Quick Table Calculation to show the Year over Year Growth. Now you will have the actual data trended with the Year over Year Growth rates written at each data point.
Note: See the triangle in the SUM([Sales]) table calculation? That triangle means that pill is a table calculation.
Year over Year with calculated fields
If you prefer just to create a simple This Year / Last Year calculation we can use calculated fields. Staying with SUM([Sales as a measure]), three new calculated fields are needed.
1. Sales this year. A formula such as the following should work:
[Sales this year]: IF YEAR([DateField]) = YEAR(TODAY()) THEN [Sales] END
2. Sales last year:
[Sales last year]: IF YEAR([DateField]) = YEAR(DATEADD('year',-1,TODAY())) THEN [Sales] END
3. Year on Year Change:
[Year on Year Change]: SUM([Sales this year]) / SUM([Sales last year]) - 1
The most appropriate option all depends on the specifics of what you’re trying to do and also the shape of your data.
Simplify the Yearly calculations using boolean
It’s also possible to make intelligent use of the Boolean data type to identify the time periods, such as this year and last year. A full write up of this method is in the article giving an overview of the boolean data type.
These boolean fields offer the flexibility to calculate yearly data over a number of different measures without the need to repetitively write:
IF YEAR([DateField]) = YEAR(DATEADD('year',-1,TODAY())) THEN … END
Instead Sales This Year, as an example, would be: