Week Three of #WorkoutWednesday was a war of attrition! Create a single line chart for a State, then replicate it 49 times for the other States in scope. Pleasingly, it wasn’t a technical brain-drain and I managed to get most things right. Here’s a summary of one bit I got wrong, and some other nice things I learned from the challenge this week.
- Number formatting to get a “+0” on an axis
The variance from the national average needed to be plotted on a scale like this:
For the National Average, I used this Level of Detail calculation:
Focus on Year. Add up all the [Unemployed] values and divide that figure by the sum of all the [Civilian Labor Force Population] values
The variance from that average was simply:
MAX() is required to ensure that we are comparing two aggregated values – without it the calculation is not valid. We can do this because the MAX of the [National Average] can only be the National Average – it’s a unique value.
As far as the axis scale is concerned, looks easy? Just a straight bit of formatting? Yes:
If, for some odd reason, you switched this so the negative formatting was before the positive, the axis would end up looking like this:
2. Centre aligned chart “titles”
The bit I stuffed up! Spot the problem:
Alaska and Connecticut have nicely centred titles, but Alabama and Colorado are shifted over to the left. Why is that? Well, I used centred worksheet titles, and that centring occurs across the breadth of the chart – axis included. Alaska and Connecticut don’t need the y-axis in this viz because the viewer can discern the scale from the leftmost State, so the State text is aligned in those instances.
I didn’t really know how to resolve this (aside from doing some dodgy manual spacing until things looked “just right”), so downloaded the original workbook. To recreate Andy’s methodology, I just needed to do this:
State(Upper) is a basic calc to convert the State name to upper case:
The important thing is that as a Discrete (Blue) field, it creates a Header:
See how the grey shaded area around Alabama does not extend to the y-axis? As a result, ALABAMA is centrally aligned over the x-axis itself. Just formatting the font size and removing the “Field Labels for Columns” tidies it up. A bonus of this approach is the white space saving. Compare and contrast the two methods:
On the left is the “Header” version. Obviously the centred State name is obvious, but note too the proximity to the vertical gridlines compared to the image on the right – it is closer to the chart itself.
3. Reference line vs. reference band
Initially, I used a Reference Line and shaded above it. Compare a reference line on the left, to a reference band on the right:
I end up stating the obvious quite a bit, and will do so again. The reason the reference line version (left) extends beyond the band (right), is because we aren’t in control of the range of the colouration. With a reference band you can stipulate the Band From and Band To values, so that’s why the Band option was appropriate in this case:
An unimportant observation is that I don’t know how optimal the User Interface is with Reference Bands. In order to set the Band to being based on a Constant, we need to navigate to the right to select the relevant option:
I don’t have much experience with using the Analytics Pane, but didn’t find this wholly intuitive.
That’s it – there are three tips I picked up from #WorkoutWednesday this week!