This week for #MakeoverMonday, Andy gave us some Census data to play with and this infographic to make over
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)
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.
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.
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
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
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
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
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!
GO FORTH AND VIZ!