I love Workout Wednesday. It doesn’t appear to have the traction of community behemoth Makeover Monday yet, but I really hope it starts to gain an audience. I like it because it forces you to deliver to a specific brief.

This week, the task is this, and I’m partly to blame as I’d asked for an opportunity to fiddle with Level of Detail (LoD) and Table Calculations. I’m still new to the Tableau party, and LoD was a fresh release when I started to learn the tool, and I’m a smidgen more au fait with the mechanics of LoD compared to table calcs.

Andy Kriebel dismissed this challenge as a bit of a simple one, but it still caused me some consternation and taught me a few things, so it was a worthwhile exercise for me at least.

Step one – figure out the LoD calc. I always like to build out tables so I can see what I’m doing:

Screen Shot 2017-04-12 at 20.42.30.png

The relevant calcs here are:

Screen Shot 2017-04-12 at 20.43.12.png

By Match Day, what is the largest (maximum) Points Total?

As you’d expect, after 1 match the highest points total was 3 (as you get 3 points for a win in Premier League football). So after 5 matches, at least one team has a 100% record (15 points) and it was the sixth game by which the the final team lost its 100% record.

So if we know the maximum cumulative points total at each Match Day, we can extend that calc to identify if a specific team has matched that total:

Screen Shot 2017-04-12 at 20.46.44.png

If your cumulative points total is the same as the overall maximum cumulative points total for that Match Day, flag it with a 1, otherwise it’s a 0

Arsenal hit the top of the league after ten matches and stayed there for at least three games, as shown in the first image in the post. That’s what Arsenal do. Every season. When the going gets tough, Arsenal get going (down the table).

Naturally, if you then SUM() that calc, you get what you need. The labels are just cell level Reference Lines with some formatting:

Screen Shot 2017-04-12 at 20.50.11.png

A tricky bit is the Axis title, to denote that it’s a LoD calc. To do this, I knew I’d need a dual-axis to force an upper axis into scope for me to tweak. I assumed it was the old AVG(0) trick, but it wasn’t as you can’t synchronise an AVG(0) with an ordinal axis. To get round that, use SUM(0) instead, which you can sync:

Screen Shot 2017-04-12 at 20.57.59.png

Then you can just remove the tick marks from the axis, and tweak the Axis Title to be what you want. Excellent – LoD is done. Bummer – table calc to come. Let’s try and leverage the same approach with viewing data in tables, and indeed the same logic in our calc.

First task is to identify the maximum cumulative points by match day:

Screen Shot 2017-04-12 at 21.05.12.png

Where I always used to (and still regularly do) trip up with Table Calcs is with the “at the level of” bit. Andy wrote a great post and shared a great video about this:

Initially, my default table calc does this:

Screen Shot 2017-04-12 at 21.06.37

Leicester had 50 points after 24 games, so the 50 represents the maximum total after the 24 Match Days. I need to add a couple of things into the context of the calc:

Screen Shot 2017-04-12 at 21.11.36.png

This looks sensible, and it mirrors the phasing of my [Matchday Max] LoD calc:

Screen Shot 2017-04-12 at 21.13.45.png

So all that remains is to amend the table calc to include the same sort of logic as the final LoD calc:

Screen Shot 2017-04-12 at 21.15.39

And then the Compute Using needed to be configured:

Screen Shot 2017-04-12 at 21.16.43.png

So you can see that where the Points Total matches the Table Calc Cumulative Match Day, a 1 is drawn, else it’s a 0. When you start to build the second bar, you need to make sure that you have the requisite Match Day field on Detail or you won’t be able to Compute Using the basis required. Thereafter, it’s just a case of replicating the steps of the first chart to create the Axis title and you’re done.

Advertisements