Image by RÜŞTÜ BOZKUŞ from Pixabay

Recently while I was working on a dashboard tracking carbon emissions by country, I came across an obstacle when creating a line chart. I could not think of a simple way to add colour in between lines to indicate uncertainty. After some thought I found a workaround which I thought it was worth sharing.

The original and final versions

Data intro

This chart shows different scenarios of global temperature increases. For each scenario there is a line for both minimum and maximum of each scenario. I wanted to show a coloured range indicating each scenario, keeping the original colour scheme differentiating each scenario. in this case pledges and targets, policies and action, and 1.5degree compatible.

In this line chart, colour is used to indicate a category – dark blue for Policies and Action, light blue for Pledges and Targets.  wanted this line chart to have colour between the min and max estimates. I also wanted to maintain the colour code for the category of projection – in this case pledges and targets, policies and action, and 1.5degree compatible.

A web search quickly yielded two data school blogs (here and here) describing how to create a line chart with colour between the two lines based on which line was higher. The basic approach to these charts is to have a dual axis line chart and area chart with the same measure. I highly recommend reading and following along with those blog entries for further detail.

Put simply, the reason why this approach won’t work in this case is because I am already using colour to distinguish scenarios, so I cannot assign colour to another field. In addition, there are multiple regions I want to colour.

The Solution

This works under the following conditions:

  • The lines are not intersecting.
  • The ranges are also not intersecting
  • The lines ultimately come from one measure, and desired colour from a dimension.

The idea

  1. Create separate measures for each line.
  2. Create separate measures for the differences between each adjacent line. I also added a line for 1.5 degrees.
  3. Place all of these measures on a chart in the correct order.
  4. Create dummy measures and place them where transparent areas are required.
  5. Stack marks.
  6. Duplicate and add Dual Axis Area Chart.
  7. Colour each measure and dummy variable.

The stack marks step is crucial. Stacking the marks means that Tableau will sum the measures instead of placing them in the same scale.

Basic outline

Separate Measures for each line:

Pledges and Targets Low

IF [F2 (CAT temp estimates.csv)] = ‘Pledges and Targets’
AND [F3 (CAT temp estimates.csv)] = ‘Low’
AND [Year (CAT temp estimates.csv)] != 2030
THEN [Temperature Rise]
ELSEIF [Year (CAT temp estimates.csv)] = 2030
THEN 0
END

Pledges and Targets High

IF [F2 (CAT temp estimates.csv)] = ‘Pledges and Targets’
AND [F3 (CAT temp estimates.csv)] = ‘High’
AND [Year (CAT temp estimates.csv)] != 2030
THEN [Temperature Rise]
ELSEIF [Year (CAT temp estimates.csv)] = 2030
THEN 0
END

Policies and action Low

IF [F2 (CAT temp estimates.csv)] = ‘Policies and action’
AND [F3 (CAT temp estimates.csv)] = ‘Low’
AND [Year (CAT temp estimates.csv)] != 2030
THEN [Temperature Rise]
ELSEIF [Year (CAT temp estimates.csv)] = 2030
THEN 0
END

Policies and action High

IF [F2 (CAT temp estimates.csv)] = ‘Policies and action’
AND [F3 (CAT temp estimates.csv)] = ‘High’
AND [Year (CAT temp estimates.csv)] != 2030
THEN [Temperature Rise]
ELSEIF [Year (CAT temp estimates.csv)] = 2030
THEN 0
END

Historical

IF [F2 (CAT temp estimates.csv)] = ‘Historical’ THEN [Temperature Rise]
END

Add Line for 1.5 degrees

1.5

IF [Year (CAT temp estimates.csv)] = 2030
THEN 0
ELSEIF [Year (CAT temp estimates.csv)] = 2040
THEN 1.5
ELSEIF [Year (CAT temp estimates.csv)] = 2050
THEN 1.5
ELSEIF [Year (CAT temp estimates.csv)] = 2060
THEN 1.5
ELSEIF [Year (CAT temp estimates.csv)] = 2070
THEN 1.5
ELSEIF [Year (CAT temp estimates.csv)] = 2080
THEN 1.5
ELSEIF [Year (CAT temp estimates.csv)] = 2090
THEN 1.5
ELSEIF [Year (CAT temp estimates.csv)] = 2100
THEN 1.5
END

Specifying the years is not entirely necessary here, but I did so to keep the highlights and tooltips consistent with the other lines.

Add Dummy Measures

We just want measures with a very small value that does not change the chart but allows for greater control of colour. Duplicate for the number of transparent areas desired.

IF [Year (CAT temp estimates.csv)] = 2030
OR [Year (CAT temp estimates.csv)] = 2040
OR [Year (CAT temp estimates.csv)] = 2050
OR [Year (CAT temp estimates.csv)] = 2060
OR [Year (CAT temp estimates.csv)] = 2070
OR [Year (CAT temp estimates.csv)] = 2080
OR [Year (CAT temp estimates.csv)] = 2090
OR [Year (CAT temp estimates.csv)] = 2100
THEN 0.0000001
END

Calculate the Differences Between Measures

Since we are stacking marks later, we want to calculate the differences (in the original chart the vertical distance) between adjacent measures.

For example:

Pledges and Targets Low – 1.5

{ FIXED [Year (CAT temp estimates.csv)] :
SUM([Pledges and Targets Low]) – AVG([1.5])}

Stack marks

Bottom to Top (as in viz)

Create ‘dummy’ fields used for white space

Caveats

As you can see, this solution is a bit cumbersome. It may be more accurate to call it a workaround than a solution. One of the benefits of working in Tableau is that it is very flexible, and there are many tricks that can be employed to create desired results. However, when working as a consultant it is always necessary to consider what is required and most important for a dashboard. Just because you can do something doesn’t mean you should. Some clients want fancy creative visualisations and will value attention to detail and going beyond standard approaches. Others want clear simple charts that are easy to maintain and pass on to other Tableau developers.

The creation of this chart requires multiple calculations which would be hard to understand without explanation. In addition, it would take some time to modify if something else needed to be added to the chart. The nicer looking colour may not be worth it in some situations. All of this is important to consider when working as a consultant.

The Data School
Author: The Data School