COVID-19 New Case Trends Cartogram

The challenge this time was set by Luke using the data being collated via Tableau’s Covid-19 data hub.

This viz is essentially equivalent to a small multiple display where the charts for a specific dimension (in this case State) get displayed across numerous rows and columns. The difference here, is the row and column for the State to be displayed in, is specifically defined, rather than just sequentially based on how the data is being sorted.

Luke very kindly provided the logic to determine the rows and columns.

Building out the data

Once again, I’m going to start by putting all my data into a table so I can check my calculations, especially since this challenge does involve table calculations (there’s a hint on the Latest Challenges page)

Data Source Filter

Although not explicitly mentioned in the requirements, the information we need to present is based on the dates from 1st March 2020 to 31st July 2020. I messaged Luke to check this, before I then realised it was stated in the title of the viz – doh!

To make things easier, I therefore added a data source filter to remove all the other dates, setting the Report Date to range from 01 March 2020 to 31 July 2020 (right click on the data source -> add data source filter

I then added the basic fields I needed to a table

  • Province State Name to Rows
  • Report Date discrete, exact date (blue pill) to Rows, custom formatted to mmmm, dd
  • People Positive New Cases to Text

I excluded the fields where Province State Name = Null

We need to calculate the 7 day rolling average per Province State Name which we can do by using the UI to create a Moving Average quick table calculation against the People Positive New Cases pill, and then editing to compute over the previous 6 records (+ the current record makes 7 days). But I want to be able to reference this field, so I’m going to ‘bake it’ into the data model by creating a specific calculated field

7 day moving Avg

WINDOW_AVG(SUM([People Positive New Cases Count]), -6, 0)

Add this into the table, and edit the table calculation to compute by Report Date only and set the Null if not enough values checkbox

Do a basic sense check that the averages are correct by summing up 7 sequential rows and working out the average by dividing by 7.

So it looks like we’ve got the core measure to be plotted, but we’re going to need some additional fields in the presentation.

Again it’s not explicitly stated in the requirements, but it is in the title, that the values plotted need to be normalised. This is to ensure the data for each state is visible; if a state with a relatively low number of cases is positioned in the same row as one with a very high number of cases, it will be hard to see the data for the state with the low cases, because while the axis can be set to be independent, this will only work against a row and not an individual instance of a chart.

To normalise, we need to understand the maximum 7 day rolling average for each state.

Max Avg Per State

WINDOW_MAX([7 day moving Avg])

Add this to the table, setting both the nested table calcs to compute by Report Date.

In normalising, what we’re essentially going to do is determine the 7 day moving Avg as a proportion of the Max Avg Per State, so every value to plot will be on a range of 0-1.

Normalised Value

[7 day moving Avg]/[Max Avg Per State]

Format this to 2 dp, and add to chart, remembering to check the table calcs continue to compute by Report Date only.

Above you can see the max value for Alabama occurred on 19th July, so the Normalised Value to plot is 1

In the chart displayed, each State is titled by the name of the State. In a small multiple grid of rows & columns, we can’t use a dimension field for this, as it won’t appear where we want it. Instead we’re going to achieve this using dual axis, and plotting a mark at the centre point. For this we need to determine the centre date

Centre Date

DATEADD(‘day’,

FLOOR(DATEDIFF(‘day’,{FIXED:MIN([Report Date])},{FIXED:MAX([Report Date])})/2)
,
{FIXED:MIN([Report Date])}
)

This looks a bit complex, so I’ll break it down. What we’re doing is finding the number of days between the minimum date in the data set and the maximum date in the data set. This is

DATEDIFF(‘day’,{FIXED:MIN([Report Date])},{FIXED:MAX([Report Date])})

We’re then halving this value ( /2) and rounding it down to the nearest whole number (FLOOR).

We then add this number of whole days to the minimum date in the data set (DATEADD), to get our central date – 16 May 2020.

Now we need a point where we can plot a mark against that date. It needs to be above the maximum value in the main chart (which is 1). After a bit of trial and error, I decided 1.75 worked

Plot State

IF [Report Date] = [Centre Date] THEN 1.75 END

Finally we need to create our Rows and Columns fields which provides the co-ordinates to plot each state. The calculations for these were just lifted straight out of the requirements – thanks Luke!

Building the Viz

Start by adding the Rows and Columns fields to their respective shelf. Set them to be discrete dimensions (blue pills). You should immediately see a ‘map’ type layout of the US States.

Exclude the Null Rows value.

Now add Report Date as a continuous exact date (green pill) to Columns and Normalised Value to Rows, remembering to set the table calc to compute by Report Date only for all nested calculations. Change the mark type to Area.

Add 7 day moving Avg to Label and set the label to display the max value only and adjust the font size – I ended up at 7pt. Then add Province State Name & People Positive New Cases Count to Tooltip. Format the tooltip to match.

Remove all column/row lines and grid lines, zero lines etc.

There is a requirement to ‘add a line underneath each of the area trends’.

For this I added a 0 constant reference line formatted to be a solid black line.

But you’ll notice that for the charts that sit directly side by side, the line seems to be continuous, but I want to break it up. I re-added the column divider line to be a thick white line to get the desired effect.

Right, now lets get the State label added.

Add Plot State to Rows before Normalised Value and change the aggregation from SUM to MIN.

Change the Mark Type to Text and move the Province State Name field from Tooltip to the Text shelf. Adjust the text label to remove any other fields that are displaying, and resize the font – again I used 7. Clear the Tooltip for the this mark, so nothing displays on hover.

Make the chart dual axis and synchronise axis. Remove the Measure Names pill from the Colour shelf on both marks cards which will have automatically been added.

And now all you need to do is remove all the headers (uncheck Show Header) against Rows, Columns, Report Date & Plot Value, then right click on the >8k nulls label at the bottom right and select Hide Indicator.

You’re all done – you just need to add to a dashboard now. My published version is here.

I really enjoyed this challenge – a nice mix of calculations & format complexity but not overly cumbersome, which meant this blog didn’t take so many hours to write this week 🙂

Happy vizzin’! Stay Safe!

Donna

One thought on “COVID-19 New Case Trends Cartogram

  1. So, it seems I may have ‘missed’ a requirement, and it sort of feels like I cheated on an exam, so I feel compelled to make amends :-).

    Whilst the hyper data source file I retrieved from data.world already contained a field called People Positive New Cases Count, the requirements actually asked for this to be recreated by using the People Positive Cases Count field.

    People Positive Cases Count contains a cumulative value of cases, so to determine how many cases actually occurred per day, we need to use a calculated field to find the difference from the previous row. You can use a ‘Difference quick table calculation’ for this initially, and if you then drag the field back into the measures pane, you can ‘bake’ it into the data source. Call it New Cases Per Day or similar and when you edit it, it will look like
    ZN(SUM([People Positive Cases Count])) – LOOKUP(ZN(SUM([People Positive Cases Count])), -1)

    Make sure the table calc is set to compute by Report Date only.

    You can of course sense check this field by comparing to the existing People Positive New Cases Count as they should match.

    If you choose to use your newly created difference field, you then need to update the Moving Avg field to reference this instead.

    Hope that helps if I confused any of you.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s