The challenge this week was another that looked perfectly possible with some head scratching, but I ended up scratching from one side to the other again.

From looking at the blog, Andy’s chart appears to be a dual-axis of a Gantt and a Circle (for the median). For the Gantt the low point is the 25th percentile, and the upper point must therefore be the 75th percentile. So for the Gantt to “work”, we need calcs to define the 25th percentile of the selected Measure, one for the 75th, and one to work out the gap between the two, in order to put the latter on the Size card.

Before diving in with calculations, I opted to do the easier stuff – sorting out the Parameter and its associated calculation:

screen-shot-2017-02-01-at-20-42-49

Screen Shot 2017-02-01 at 21.09.09.png

The sound of the door closing, is that of me leaving my comfort zone and embarking on a quest involving some haphazard Googling. I started here with a nice little thread, which showed the rudimentary process of calculating the percentile manually.

However, I found after some fumbling that table calculations and Level of Detail calculations are not compatible. I feel comparatively more comfortable with LoDs, so this caused me issues later on. I won’t bore you with that dead end, other than to show the simple calcs it entailed:

screen-shot-2017-02-01-at-20-51-39

screen-shot-2017-02-01-at-20-51-46

Now I already knew (from memory, not experience) that there is a PERCENTILE() function in Tableau. What does that do for me?

screen-shot-2017-02-01-at-20-52-58

OK. So I can apply this to our data as follows:

screen-shot-2017-02-01-at-20-56-45

Seems OK, and when I plotted it by Year, it looked like this:

Screen Shot 2017-02-01 at 20.57.36.png

It transpires that I could easily verify the calculation, as the default aggregation levels in Tableau allow you to set aggregation to various percentiles too:

Screen Shot 2017-02-01 at 20.58.49.png

Plotting that on the same chart gave me matching results, so I now know that the 75th Percentile calc just involves duplicating the 25th, and swapping 0.25 for 0.75. That in turn gives me the opportunity to write the size calculation:screen-shot-2017-02-01-at-21-02-05

I’ve written about gantt‘s a few times, so knew that I was now in an OK place, at least structurally. The Circle on the dual-axis is just the median of the selected Measure in the corresponding year:

screen-shot-2017-02-01-at-21-09-38

So I could now combine my gantt and circle to get much closer to the end result:

Screen Shot 2017-02-01 at 21.11.28.png

Indeed, all that remains is the pesky tooltip, and that one final piece of the puzzle: stating the count of players falling within the percentile range. Oh – and that legend…..

Earlier I mentioned the fact that the WINDOW_PERCENTILE solution wouldn’t work for me, as it is incompatible with table calcs, and it’s here where that observation is relevant. It’s also here where I had to resort to downloading Andy’s workbook, and boy is it obvious why I needed to:

screen-shot-2017-02-01-at-21-24-38

It’s completely logical, but would have taken me at least an hour to get right. When I break it down, my thinking is:

Bring the Player field into the scope of the calculation, as it isn’t in the view. If their value for the selected Measure is between the overall Year’s 25th and 75th Percentile, give him a 1, else give him a 0

The Player field isn’t in the view as it would knacker the gantt and circle elements, which is why {INCLUDE} is required. It’s also necessary to use {FIXED} for [Year], otherwise you’ll just sum up all of the 25th / 75th Percentiles. It makes absolute sense, but that jumbled nest of standard and curly brackets is somewhat complex!

The final challenge to overcome is the creation of the nice little legend to educate viewers as to the content of the viz. Andy kindly reminds us in his blog that it is not an image – it is some sort of floated worksheet. I already had the downloaded workbook available, and openly admit that I did not mess about with this and I just looked at Andy’s technique as I could have sunk a world of lost minutes into this. And I still did. I do not get this.

To start, I just duplicated my main Chart sheet. I then cleared out all the gridlines and axes, and dropped Year onto filters:

Screen Shot 2017-02-01 at 21.43.48.png

The last Year in the image above represents a nice long gantt, so I filtered the view to only include 2016. The next requirement is to replace the current Row Measures and [Size] calculation with ones that won’t be affected by the parameter, as I don’t want the Legend to shift about when the parameter is used, or it might distract the viewer.

To do this, I just duplicated the Row Measures and the Size calc. For the Row Measures, I removed references to the [Chosen Stat] calculation and set them to refer to the Touchdowns Measure. The [Size] calc was amended to refer to the new, static Row Measures.

The annotation and scaling part was far fiddlier than I expected and I just couldn’t figure it out, even with a downloaded workbook to refer to. I now had a nice, long isolated bar, and was anticipating that I could just annotate the Median and the upper and lower points and float that on the dashboard:

Screen Shot 2017-02-03 at 19.31.53.png

Take a look at Rebecca Abrahams‘ post on her new blog about the same challenge, there are some uncanny similarities! I don’t get it. Rebecca doesn’t get it. Help! If someone could post a short walkthrough of this technique, it would be gratefully appreciated by at least two members of the Tableau community.

My final attempt is here.

EDIT!

Another great blog post about this challenge by @tarannum_in is here!

EDIT 2!

Lack of attention to detail highlighted that my vertical gridlines were “wrong”. The original had the vertical lines running through the centre of the gantt bars. The solution? Switch from a Discrete to Continuous Year. To get the axis labels to the top, I duplicated the Year Measure and removed the Title and tick marks from the lower axis.

screen-shot-2017-02-04-at-19-23-42

This then presented another hurdle to overcome. 2005 and 2017 appear on the Year axis, whereas the original runs from 2006 to 2016. How do we resolve this one? Bizarrely, it’s by changing the Data Type of Year to Number (decimal):

screen-shot-2017-02-04-at-19-30-15

Once that switch is made, the axis runs as per the original:

Screen Shot 2017-02-04 at 19.31.18.png

I was getting much closer, and then Michael Mixon gave the final tip I needed:

Screen Shot 2017-02-04 at 19.45.48.png

Oh. Like this?

screen-shot-2017-02-04-at-19-44-25

See how the y-axis is set to start at 6 to fill the vertical axis? And the x-axis starts at 2005.9 to reveal the entirety of the 2006 Year? I had to fiddle a lot with the precise positioning of the annotations, and finally I got is to look OK on the dashboard. Here is the actual Legend sheet:

screen-shot-2017-02-04-at-20-06-04

In spite of the funky looking alignment, it looked fine on the dashboard:

screen-shot-2017-02-04-at-20-10-03

To spite me, things went nuts when I published to Tableau Public, but I don’t care! I consider that I got this one over the line at long last!

Advertisements