For once, I got through a #WorkoutWednesday challenge without too many mental or physical scars. However, there was still a lot I took away from the exercise, so here’s a quick overview of those learnings:

Data Preparation

The data needed to be unioned, pivoted and tidied up. Unioning in Tableau is simple. I just highlighted the Male and Female tables and dragged them into the blank window:

screen-shot-2017-02-11-at-19-38-10

Once in situ, I enabled the Data Interpreter to sort out the headers:

This slideshow requires JavaScript.

Next, I just pivoted the Year columns to make the short / wide format long / tall instead, hid / renamed a few fields, and applied the data source filters required:

screen-shot-2017-02-11-at-19-42-33

Referring to Emma‘s original, I could see that the standard trellis-type calcs could be reused:

Screen Shot 2016-08-29 at 17.57.17Screen Shot 2016-08-29 at 17.57.08

So the first real challenge was working out how to plot the data. I started with table calcs and putting Gender on Detail, but that was a false dawn. Ultimately, I needed to calculate the Male and Female Population Estimates, calculate those as a percentage of total, and then find a way to figure out the national average for each Gender. It’s pretty straightforward stuff. The Male calculations are below:

Screen Shot 2017-02-11 at 19.54.56.png

Screen Shot 2017-02-11 at 19.55.17.png

If I plotted that on the view, I got this:

Screen Shot 2017-02-11 at 19.56.02.png

But I knew that I wanted the Male values to head right-to-left, and the Female values to head in the opposite direction. I experimented with shoving a “-” in front of the calcs, but there is a much more obvious solution. Right-click the x-axis > Edit Axis > tick “Reversed”:

Screen Shot 2017-02-11 at 19.57.42.png

I could then put the Female equivalent on the canvas:

screen-shot-2017-02-11-at-19-59-23

That image is with the [Male] calc prefixed with a “-“. This isn’t what I needed to do, as it meant that I couldn’t plot the National Averages as continuous lines to give these values context. The closest I could get was a Gantt effect using cell level Reference Lines, but that wasn’t what was required:

Screen Shot 2017-02-11 at 20.01.23.png

What was needed, was a dual-axis of bar and line for the Male measures, alongside the same for the Female measures. Before progressing, I needed to figure out a way to get the national averages per gender:

screen-shot-2017-02-11-at-20-07-26

This gives the National Average by Year, Age Group and Gender.

screen-shot-2017-02-11-at-20-06-49

Whereas the above calc computes the Male National Average. To determine the percentage:

Screen Shot 2017-02-11 at 20.08.50.png

As I often do, I checked the calculations out in a tabular format in Tableau:

Screen Shot 2017-02-11 at 20.13.12.png

And sense-checked things in Excel:

screen-shot-2017-02-11-at-20-14-24

Cool. Once I was comfortable, I could plot things accordingly, with the Row and Column Dividers simply computing by Year:

Screen Shot 2017-02-11 at 20.15.49.png

But it looked a bit dull. I’d already grouped the age groups into decades to avoid the cluttered look of the original, and I’d see that Andy Kriebel had used colour to highlight where measures were above or below the national average, so I nicked his idea:

Screen Shot 2017-02-11 at 20.18.17.png

Sticking that on the Colour card for bars (and the equivalent calc for the Female dual-axis), allowed me to better highlight Age Groups where the Gender was above or below the national average:

Screen Shot 2017-02-11 at 20.19.33.png

The final dashboard can be downloaded here.

Advertisements