How to color between two lines in Tableau

This week for #MakeoverMonday, Andy gave us some Census data to play with and this infographic to make over

Image

What works well

  • Good title and subtitle
  • Nice appealing colors
  • Line colors are Labeled clearly
  • I like the point values on the ends only

What could be improved

  • I’m not loving the bottom side-by-side chart. I feel like this isn’t the main storyteller, is more contextual and could be utilized better as viz in tooltip. However, not possible in this static image (#knowyouradience)
  • I’d really like to shade between the lines

Here’s my creation (click the pic for the interactive version)

Area

As you can see, it’s very similar to the original just cleaned up a bit. So how did I do it?

At a high level, this chart type is an area chart overlayed with a line chart. The inspiration for this viz came from the legendary Rosario Gauna and her #WorkoutWednesday challenge back in August.

Data Prep

The first thing we need to do is calculate the metrics used; % of population. Prior to opening Tableau, I chose to prep my data a bit. The data that Andy provided was very long with a separate column for each individual age. For folks just getting started in Tableau, know this:

Tableau works best with skinny and tall datasets

Having this knowledge, the first thing I thought when I saw it was to pivot the table on the age columns. I chose to do this in Tableau Prep.

Screen Shot 2018-11-05 at 10.48.16 AM.png

Then I added a step to clean the field names. I also used Tableau Prep to create an “Age Group” field so that I could isolate only the rows I needed for this. My calculation looked like this.

Screen Shot 2018-11-05 at 11.03.05 AM

One of the issues, when you pivot data, is that you might end up duplicating some values that could prove troublesome later down the road. In this case, one of the fields that we ended up duplicating was the [Total Population] field. Which means, if left alone when we aggregate to calculate the % of population, my denominator would be MASSIVE and incorrect. So I chose to group and aggregate the [Total Population] field based on the minimum value so I only get the one correct value for each year.  But first I needed to filter the rows to just those with the “ALL” dimension member for:

  • Origin
  • Sex
  • Race

Screen Shot 2018-11-05 at 10.57.25 AM

No that I have value for each year, I want to join it back to my pivoted dataset on year. This will make it much easier when I start working with it in Tableau. Did I NEED to do this? No, I probably could’ve handled the aggregation with LOD’s in Tableau but this gave me a good excuse to get better at Tableau Prep.

Back in Tableau

Now that we’ve got our data prepped and ready, we’ve got one more step to get the exact total population value that we need. It’s a very simple LOD

{ Fixed [Year] : Min ( [Total Population] ) }

This will be the denominator for our % of Population calculation:

Sum ( [Estimate] ) / Sum ( [Yearly_Pop] )

Next, we need to isolate the values for each age group, like this

{ Fixed [Age Group] , [Year] : IF Min ( [Age Group] ) = “Child” then [% of Population] end ) }

And then we flip the argument to “Senior for the other field. Now we’re ready to build

Let’s start with a simple line chart

Lines

In order to color between the lines, we essentially are building an area chart with the bottom area colored white to appear as the canvas. Then the other two parts are colored based on which value is higher. Let’s dive in

The first thing to do is calculate the bottom section. We want Tableau to compare our two lines and take minimum values for each year across the view. Did you know you can add multiple measures to the MIN() function? I only learned just learned this and it blew me away.

{ Fixed [Year]: MIN( Sum( [Child] ) , Sum( [Senior] ) ) }

Next, we need to calculate the difference between each [Child] value and the [Bottom] value.

{ FIXED [Year]: Sum( [Child] )Sum( [Bottom] ) }

Then we duplicate this field to do the same thing for the [Senior] field

Now let’s make a stacked area chart using Measure Names & Measure Values and these three new values. We get something like this

Areas

Now, this might not look like what we want but I assure you, we’re in the home stretch. We can easily drag our measure names around to move [Bottom] to the…bottom. Then we can edit the colors to whatever we want but we need to color [Bottom] to white (or whatever background color you’re using and we got what we’re looking for

shaded

Now we just need to combine that with our [% of Population] field as a dual axis, sync and clean up with simple formatting and boom! We’ve just colored between the lines!

Area

GO FORTH AND VIZ!

 

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: