How to do Year-over-Year comparisons in Tableau || Let’s have a (word about) date(s) (2/6)

For today, we will be looking at how to do year-over-year comparisons avoiding one of the most annoying effects when creating such a comparison out of the box.

Using sample superstore, assume you want to compare two years and their difference.

Your initial build will look like this:

Next, you will be adding a quick table calculation for difference (or percent difference) and this is what you get

Workable, but most people, and especially management, will want to have another layout, more excel like.

And it gets worse if we add more measures or start moving around the Measure names from rows to columns

Using modified totals to overcome this obstacle

In order to deal with this problem, we need to create fields for for every measure that we want to include.

For example, using [Sales], create this calculation:

//Sales for modified totals
IF countd(year([Order Date])) = 1 THEN sum([Sales])

ELSE

       sum( IF YEAR([Order Date]) =  { MAX(YEAR([Order Date])) } THEN [Sales] END )
        -
       sum( IF YEAR([Order Date]) =  { min(YEAR([Order Date])) } THEN [Sales] END )


END

This calculation will do the following: whenever there is only one year, it will return the sum of sales. In other cases, it will fall back to the second part.

The second part subtracts the sales of the minimum date in the view from the sales as per the maximum date.

How does this come together?

This time, create again your view with the order date years on columns and put our newly created calculation on text marks.

Next, add column totals from the analytics pane

Since in the totals Tableau considers the entire data underlying the table we will not only have one year but 2021 and 2022. Thus, the formula we created falls back to the “else” part.

Since 2022 is the max year, the first part will be true and return 2022 values, the second part will return 2021 values.

Note that we have to put the date filter into context to make this work, otherwise the LOD we used in the formula will deliver (technically correct) unexpected results.

Right click your column totals, select “format” and on the left in the formatting pane go to the very bottom and replace “Grand total” with “Difference”.

We can now either add more measures (that we need to create!) or add further dimensions as I did in the following screenshot. This is what you get

And that’s it for today.

I hope you found that useful and as always, appreciate your feedback.

Until next time

Steffen