Creating Two Colour Area Charts

Recently, Google started publishing a series of “COVID-19 Community Mobility Reports” that show movement trends of people over time across different categories of places based on anonymised mobile data.

Originally these reports were published as PDF files only whereby you could search for a particular country and then open and download a PDF report for that country.

However, in the last few days I noticed they were also publishing the CSV data that is used to produce the reports, which opened up the possibility to recreate or add on additional data if needed to build your own visualisations.

I wanted to be able to see all six graphs for the different categories of places that the reports track on one dashboard page and be able to quickly filter between countries. Below is the final visualisation I created. Click to open and interact with it in Tableau Public.

Design Changes

I liked the clean look of the original design Google used with area charts. A snapshot of two charts from the original are shown below:

However, I wanted to add more emphasis on whether the trend was increasing or decreasing versus the baseline (which Google define as: “the median value, for the corresponding day of the week, during the 5-week period Jan 3–Feb 6, 2020.“).

To do that, I used a different colour for the positive and negative values of the area chart, but this is not as straightforward as you might think.

Creating a Multi-Coloured Area Chart

I had used this technique in a visualisation about “The Big Mac Index” a couple of years ago, but I couldn’t remember how I approached it, so I made a quick Google search (often quicker than finding the original visualisation!) and found a blog post by Jeffrey Shaffer, which I’m guessing is probably what I used first time around.

Below is a summary of the process to create these area charts using a simple example view in Superstore Sales of profit by week.

Positive and Negative Coloured Bars

Using a mark type of Bar, you can add a SIGN() function to the colour shelf to colour the bars based on the measure being used:

However, this doesn’t work with Area charts. Instead, the SIGN() calculation uses the sign of the sum of all values across the view, so in this case, the sum of all positive profits is greater than the sum of all negative profits, so the result is positive:

If I filter to those weeks where there are more negative values, the sign function returns a negative and the area chart is coloured blue:

Instead, we need to create two half-area charts and join them together…

Duplicate the Measure

First copy the profit pill to create to identical area charts

Fix the Axes

Then edit the axis of the top chart as shown below, with a Fixed Start of zero:

And edit the axis of the bottom area chart as below, with a Fixed End of zero:

The result will then look like one area chart…

…but you now have two Marks cards each with their own colour shelf, so you can colour the lower area chart a different colour:

Add Reference Lines

The issue now is the scale of each chart is different as their axes have different scales. To resolve this, create a calculation that will return the maximum absolute (i.e. positive or negative) value of profit across the view

This can then be used to add reference lines to both charts, which effectively scales both charts to use the same axis values:

Note, for the lower chart, you need to add a minus sign in front of the Reference Line calculation.

Remove the Double Zero

The next challenge is that, because there are now two charts, each one has its own zero labelling:

To remove this, we can use the number formatting in Tableau. Below, all I have done is add a space at the end of that formatting line, which tells Tableau that any zero values should be represented as a space.

Finished Area Chart

Below is the final chart. Click the image to open the workbook in Tableau, which you can download for a closer look.

Thanks for reading!

Marc Reid

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 )

Google photo

You are commenting using your Google 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

WordPress.com.

Up ↑

%d bloggers like this: