A quick and easy way to use Tableau maps is to colour countries by some measure and view the difference between countries by gradations of that colour.

Take a look at the map below showing the highest confirmed cases to data as of a given week starting 12 Apr 2021:

I’ve used pages to view cumulative COVID-19 cases in all countries over time. We can clearly see how the US and India are reporting high numbers, but it’s difficult to differentiate between many other countries.

What about using steps in Colours? That doesn’t really help given we have a lot of countries to view.

So what I wanted to do is introduce some better ways to visualise information using LODs – FIXED functions in particular.

The Goal

I want to be able to easily differentiate countries in their status regarding COVID-19 cases. For any given week, I would like to see:

  • Total confirmed cases to date
  • The death rate of each country
  • Each country’s confirmed cases figure as a percentage of its population
  • An indication of whether each country’s percentage of its population as confirmed cases is below or above the average percentage of all countries in the same continent. (this is in bold because it related to using an LOD).

The Data

The data we’re using is as follows:

It contains one records for each Country/Region and date, noting the number of confirmed cases, recovered cases, and deaths. The figures are cumulative, so we aren’t going to be doing any running totals in Tableau.

I’ve also joined this table with two other tables to add the continent and populations of each country:

STEP 1 – Base Calculations and Map

To start with, I’ll add the first 3 items from my Goal section.

Total confirmed cases to date

Our data already has the ‘Confirmed’ column, so I simply drag this onto my map and also drag it onto Size so we can clearly see which countries have the most cases.

I should also point out that I want to view my map by week, toggleable by animation or manual clicks. So, I’ve added my dates field onto Pages, and set it to WEEK(Date).

Important: what this means is that when I’m looking at figures on my sheet for each country, I’m looking at a range of values covering all days in a given week.

When I drag the Confirmed numbers onto my shelf, I’ve selected a MAX aggregation method, which will give me the MAX confirmed figure for a given country in the week currently being viewed. Because Confirmed values are cumulative, the MAX value in the week will, by definition, be the value on the last day of the week.

The death rate of each country

This one is a simple calculation –

‘Deaths % of pop’:

Each country’s confirmed cases figure as a percentage of its population

Now, here I’d like to take the MAX value of confirmed cases as a percentage of population for the given week – i.e. the largest of the 7 days’ values for the week. To do this I’ll first calculate the Confirmed % of the population similar to Deaths above.

‘Confirmed % of pop’:

Next, I’ll use the FIXED LOD to get the MAX value from the set of values this returns for each country and week –

‘Max Confirmed % of pop’:

Here’s how I like to read LODs – “for each country, for a given year, month, and week, find me the MAX value of all the [Confirmed % of pop] values”.

Finally, with these measures created, I then add them to my map (Confirmed % of pop is large and orange, with Deaths % of pop underneath it). I then set both measures to use the MAX aggregation method once again for the same reason as before – I want to see the latest value in of the week being viewed.

Now we’re ready to tackle the fourth goal.

STEP 2 – Comparing Countries to Continents

The title is a giveaway that we are going to need LODs because I want to compare values at the country level to values at the continent level.

So what do we need to do?

Well, if you recall, I’ve already calculated ‘Max Confirmed % of pop’, which gives the largest ratio for each country in the week being viewed. I simply want the average of those numbers for all countries in the same continent.

To do this, I’ll use a FIXED LOD expression to average all these values at the continent level and create a new measure –

‘Avg Confirmed % of continent pop’:

And that’s it! All that’s left to do now is find the difference for each country between its own highest Confirmed % of population ratio for the week – ‘Max Confirmed % of pop’ and this new measure.

I create this as a new calculated measure –

‘Confirmed Diff – country to continent’:

This will result in each country having either a negative or positive number, which is easy to distinguish using colour. I add this new measure to Colour and split it into 2 colours.

Now, this is quite different to our original map.

By looking at this map I can quickly and easily identify a few things:

  • Which countries have a high number of confirmed cases by the size of dots
  • Which countries have a higher than average (compared to all countries in Europe/the same continent) percentage of confirmed cases in their population (pink dots).
  • Which countries have a lower than average (compared to all countries in Europe/the same continent) percentage of confirmed cases in their population (green dots).

More importantly, because I’m viewing my chart as a possible animation over many weeks, I can absorb all this information at a glance rather than having to read individual figures.

To sum up, using LODs allows us to introduce interesting calculated measures that can really add value to a chart.

 

The Data School
Author: The Data School