How To Tableau: Heat Grids

In 'How To Tableau' I take you through a real life example that I've been asked to help with recently. This week I show you how to create a 'heat grid' to compare any value in a dataset to any other

How To Tableau: Heat Grids

This week I was asked how we could take a table of results, like the one below, and enable users to quickly compare any of the results with any other result:

The solution we came up with was to utilise a heat grid that looks like the image at the top and makes it easy for anyone to scan down a column and across a row and see the variance between those two results.

In the real example we were comparing performance of different portfolios of investments however the same technique can be used to compare any set of results.

In the example we’re going to walk through we’ll compare the volume of sales in any of the month of the year to any other month. We’ll use the sales store example data for this, you can download that and the final workbook below:

• Tableau Heat Grids.twbx

• Tableau Heat Grids Example Data.xlsx

(Note: Comparing monthly results is actually pretty poor practice as months have varying numbers of days so shouldn’t be compared like for like, in retail a month with more weekends will typically have greater sales too. In reality you’d create an average per day to counteract the differing number of days, or use a 4 week period but for our example we’ll keep it simple!)

Creating the data

(Note: The data we’ll use is simply a sum of the Quantity or Orders for each month in 2020 from the Sales superstore. You could create this yourself, or for ease you can download the aggregated data below instead)

Our first step is to create the data we need for this, initially it may look like we can do this in Tableau but we actually need to do something first to allow us to create the view we want.

1. Take a copy of the aggregated date you’ll use and paste it into Tableau (pro tip: Simply hit Ctrl + v on the view and it will add a new data connection with the date).

2. Right click the newly created data source and click ‘Edit Data Source’

   

3. On the data source pane that has appeared click in the window and press ctrl + v again and another copy of your data will appear, the Edit Relationship box should auto appear as it doesn’t know what fields to link.

4. Click “Create join calculation” under your data table on the left and enter any value (we just put a 1 in). Do the same on the right as well, it doesn’t matter what you put in the calculation as long as it’s the exact same on both sides.

   

5. Close the Edit Relationships window. The next couple of steps are optional but make it easier later on

6. Click your first data source, then click on the arrow to bring up the drop down menu and click rename and call it ‘Heat Grid Columns’

7. Right Click your dates column (Month of Order Dates in our example) and rename this to Column Months.

8. Right Click your values (Measure Values in our example) and rename this to Column Values.

9. Repeat the last 3 steps for your second data source, but use Rows in place of Columns. It should look like this:

   

   Create the view

10. Create a new sheet, from your Heat Grid Example data drag Column Months onto the Columns and Row Months onto the Rows. (Pro tip: Double click Row Months and then Column Months to automatically add them to the view)

11. Create a new calculated field (Pro Tip: Press Alt then a then c), name it Monthly Comparison, for the calculation divide the row values by the column values (remembering to subtract it from 1 so it’s a relative percentage change) as below then press OK.

    1 - ([Column Values]/[Row Values])

12. Double click the Monthly Comparison measure you just created to add it to your view, it should look something like this:

    

13. Drag Monthly Comparison from your measures onto Colour.

14. Click the Marks drop down box and click Square. It should now look like this and pretty much gives us what we need, in the next steps we’ll clean this up and make it easier to read.

    

Finishing steps

While the above gives us what we need there are a few things we can do to make this look nicer and be easier to read.

Format the numbers

As we’re looking at a relative comparison let’s make these percentages:

1. Click Monthly Comparison in your measures, hover over Default Properties and click Default Number Format.

2. Select Percentage and make it 0 decimal places.

Format the layout

I personally prefer a border around the values the cells to be more square and the numbers to be centred as I think this gives a cleaner look.

1. Click Colour and set the border colour to be white.

2. Click Label and then the Alignment drop down menu.

3. Click the Centre option Under both Vertical and Horizontal alignment .

4. Hover over any of the month titles on the Rows until you see the size arrow, drag the arrow down until it lines up with the bottom of the next row in order to double the size of your rows. This is how it should now look:

Add Tooltips

I’d like to be able to see a bit more info about the actual values and how they changed, to avoid cluttering my view I do this in my tooltip.

1. Drag both Row Values and Column Value to tooltip. You could leave it like this but you could also customise your tooltip as below.

2. Click Tooltip and then edit what you would like to show. I like to show my tooltip as a commentary so edited mine to show like this.

   

3. You may even want to add another calculation that shows the actual change in values between the two months. I left this out as it wasn’t required.

Remove The Same Month Comparisons

Notice the diagonal line down the middle that’s all 0%? That’s where we’re comparing the same month, let’s hide that since it doesn’t make sense to include it.

1. Right click on your Monthly Comparison calculated field and change the calculation to this (this is going to give us null when it’s comparing the same months:

   IF [Column Months] != [Row Months] THEN 
   1- [Column Values]/[Row Values]
   END

2. Drag your Monthly Comparison onto filters and make sure include nulls is not selected.

Final Touches

There’s a couple of final things I like to do to finish this off as well:

• Change the colour: I’m not a fan of the blue and rusty orange colour scheme, blue and red works well for this but be mindful of accessibility and in particular colour blindness.

• Remove headers: The headers that say Row Months and Column Months don’t add anything to our chart so let’s hide them by right clicking on each and selecting Hide Field Labels…

• Format borders: I like the outside borders of my grid to be the same as my inside borders. Select Format > Borders (or press ctrl o, b), under both row and column divider set the Pane to the lightest grey and the Header to None.

That’s it, we’re all done! The result should look something like this:

We can now look down any column and then along the row to see how the sales varied between any two months. Hovering over the result gives us the individual order quantities for those months and the variance.

What next?

Want to take this to the next level? Why not try some of the following:

• Add a parameter that lets uses flick between relative and absolute variance.

• Add a parameter that lets users choose whether to compare the sales values, profit, discount or order quantities

• Incorporate it into a dashboard that when you click a box shows you the per day or per week sales for just the selected months.

Have you found this useful? Do you have ideas for alternative ways of doing this or ways to expand on this? If so I’d love to hear them at @AlanMurray_TTB or linkedin.com/in/alan-m-murray/

If you’re looking for a new job working with Tableau don’t forget to check out my weekly hand picked list of the latest jobs working with Tableau in my new project Tableau Jobs

Thanks,

Alan @ Tableau Tea Break