Year on Year Comparison Part 2 - Line Chart in Tableau

by Liu Zhang

As much as the previous blog shown how to create a Gantt Chart to show yearly comparison, it is still using a lot of space (ink) to present the view for comparsion.

In fact we can improve it by using line chart instead.

In All Marks, change type to Line
Result - after rotating the labels

It presents the information in a simple view compared to the Gantt Chart, as the areas of the bar isn't used to present anything, hence wasted space (ink). And with a line, it is easier for people to see the connection/trend.

The above is a dual axis chart (blog: Shared vs Dual axis), the downside here is we cannot compare the two lines directly/easily in table calculation, switch to a shared axis chart will be more beneficial in this case.


To achieve a shared axis, we could just use date field calculation, without using calculated field on Sales itself.

First let's concentrate on the required years.

First step, repeat as previous blog

Then we can drag discrete Month and SUM(Sales) into the selves, and break up the view by drag discrete Year in the colour (or details) marks.

We can add Year onto label as well

The benefit of doing with a single measure is that we can do more table calculation for YoY comparison, while it will be much trickier in the previous blog with separate measures.

First we drag another SUM(Sales) onto the tooltip and do a Running Total calculation along the Table (across).

Next we can repeat by drag one more SUM(Sales) onto the tooltip and do same as above, except we will also perform a secondary calculation.

With the above setup, we calculate the running total for each year, then we compare the percentage difference between the yearly running total values.

July 2020 value
July 2021 value

Notice the % difference exist in the 2021 value while it's empty in the 2020 value (as it has no previous year for comparison).

We can also visualize better by duplicate the sheet as crosstab to see more clearly.

We can see the YoY comparison on the running total

Optional - Show only complete month

If we look at the line chart (or crosstab) above, we see there is a huge drop in the Aug 2021 value, this is due to the data being incomplete for the month, as the month has not yet finished.

Therefore depends on the requirement, either the user has high demand to see the real-time value (keep latest data) or low demand to see (filter the incomplete month)

To handle this problem we can create a calculated field for user to choose.

Second line checks for 31st Jan, 28 Feb, 31st March etc

We can drag this calculated field into the filter mark and by displaying it, it will allow the user to choose what they want through a single drop down list.


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