Year on Year Comparison Part 1 - Gantt Chart in Tableau

by Liu Zhang

Recently I have encountered an issue to present the year on year (YoY) trend comparison, although there are ways to show the trend, none of them are the most satisfactory.

A quick way:

Separate line chart that splits by Year

It is easy to create a line chart, then split by year, but it is not easy to compare two separate line chart together and the the axis are different range if the date is incomplete for one period.

A better way:

A Gantt chart that uses the previous year as reference bar

A better way to compare YoY trend is to use a Gantt Chart, where the current year values are shown as bars (if exist) and the previous year values are shown as reference bar.

Note: Here the view is broken down by (discrete) Month, in practice, the view can be broken down by any discrete field.


To make the calculation more dynamic that doesn't depend on the future manual adjustment, we will try to use date calculation functions within Tableau rather than manually set the dates.

Note: The blog will aggregate the calculation to monthly level, though any other level can be used depends on the requirement or we can even setup a parameter to control the level (blog link).

First we will create the benchmark date, which is the latest date available for a given dataset, such as sales date, invoice date, delivery date etc.

Use LOD to find the latest date available 

Then we can find the first day of the year for the given End Date calculated, by using DATETRUNC() function.

We trunc(ate) the date at year level

The latest date within the sample dataset is 5th Aug 2021, then the calculation will truncate the date to (year) 2021, as the function will return a field that is still a Date type field, the exact output will be 1st Jan 2021.

Next we can find the previous year by subtract 1 year from the current latest date, then truncate the date. To subtract date in Tableau, we use DATEADD() function.

We add -1 year (i,e, subtract 1 year)

By above logic, the calculation will return 1st Jan 2020.

With the reference dates calculated, we can start define Current Year (CY) and Previous Year (PY) by using those dates.

Since we are (usually) interested in the latest YoY comparison, we will drag the Date - Year Indicator to Filter and filter out More than Previous Year.

Filter the required period

Next we will use similar calculation to calculate the measure values, (Sales in this example) to be used in the view.

Current Year value
Previous Year value

To check the above calculations are correct, we can display the result in the table format.

Notice the CY and PY jump at 2nd Jan 2021

Once we can confirm the calcualtion is correct, we can start to setup the view by dragging the label into the corresponding place.

Depends on the requirement, we can interchange Rows and Columns shelf
Notice the chart type selection in the Marks Card

This should present the following view, where people are free to format the colour, size and tooltips themself.

Final view

Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.

Tableau Public

LinkedIn

Twitter