For #MakeoverMonday this week I created a really basic slopegraph, which I was happy with after a series of minor formatting tweaks. It’s not mind-blowing, but it clearly demonstrates the apparent changing habits of America’s carnivores.

Screen Shot 2018-01-02 at 19.25.31

With it still just about being the holiday season (the data was released on New Year’s Day), I kept an eye on Twitter to see what was being created elsewhere in the community and this viz from Ben Davis stood out:

Screen Shot 2018-01-02 at 19.34.50

I just really like it! And what’s more, I don’t know how to go about creating a chart like this, so it is time to find out.

Ben advised me that he followed an excellent post by Rody Zakovich and all I’m doing here is following Rody’s guide. I was aware of this chart being “a thing”, but it had escaped me until now. My hope is that by giving it another airing, it might make more people give it a go, as I think it’s a really aesthetically pleasing chart.

Let’s go. The essence of my aim is to create the chart below, but (critically) to fill the “gap” between the lines. Note that I have pivoted the data to lump all the “Meats” together. The filters just reflect that I’m limiting the data to Actuals, where the “Meat” is either “Total Poultry” or “Total Red Meat”.

Screen Shot 2018-01-02 at 19.53.49

So how do we fill the gap? Rody’s ingenious solution is to create a polygon to shade between the lines, and it all relies on this table calculation:

Screen Shot 2018-01-02 at 19.59.51

:shrugs:

Say what? Let’s break it down. What happens if I create the MIN(Meat) calc and shove it onto Text?

Screen Shot 2018-01-02 at 20.03.05.png

Makes sense. It’s basically returning the Dimension member; it can’t be anything else. What if we instead look at the WINDOW_MAX(MAX(Meat))? You got it!

Screen Shot 2018-01-02 at 20.08.59

The Meat with the highest value in the chart is “Total Red Meat”.

What about INDEX()?

Screen Shot 2018-01-02 at 20.45.34.png

SIZE()?

Screen Shot 2018-01-02 at 20.45.58.png

Put it together, and what have we got? In Rody’s own words, the whole calc is doing this:

This calculation is figuring out whatever the Max Meat is on the chart. It doesn’t matter which one it is, I just want it to be dynamic in case I filter to two different Meats. For the “Max” Meat, I take the Index and count up from left to right, then on the second Meat, I take the Total Marks (Size) for both dimensions and subtract the INDEX for the second dimension, finally add the SIZE() again.

If I put the calc output on Text, I get this, which best shows what is plotted and why:

Screen Shot 2018-01-02 at 20.28.50.png

Switching the Mark Type to Polygon gives you the shaded area:

Screen Shot 2018-01-02 at 20.29.58.png

So you then just need to create a dual-axis, with the second axis being line charts coloured on the basis of the Meat:

Screen Shot 2018-01-02 at 20.31.52.png

The table calc is pretty simple by table calc standards, moreso when you break it down. I really like the aesthetic impact of shading between Dimension members’ lines, so I’ll aim to use this technique again in future. Thanks to Rody for his post, to Ben for reminding me, and to anyone who has persevered with reading this!

Advertisements