I need to make my posts a bit snappier, so here’s a semi-concise one! Andy Kriebel and Emma Whyte have launched #WorkoutWednesday in 2017. It’s an extension of the old Data School Gym Challenges. In Andy’s words:

The idea is to replicate the challenge that we pose as closely as possible

So whilst #MakeoverMonday is arguably more of a design challenge, this is more of a technical exercise, aimed at improving core skills. I picked out three top learnings from the first week.

1. DATEPART has some surprising “parts”

screen-shot-2017-01-05-at-19-18-42

The first challenge for me was the horizontal axis, which runs from 0-366. One point for each day of the year. I messed about with various DATE functions for a while, and then resorted to my favourite function: GOOGLE(). Googling “tableau day of year” returned this link. It lists  all the “parts” available for use with DATEPART:

screen-shot-2017-01-05-at-19-21-44

‘dayofyear’ is the fella we were looking for:

screen-shot-2017-01-05-at-19-22-48

I certainly wasn’t aware of it before, and comments on Twitter implied that it’s either a newish feature, or indeed one that more experienced users have not been aware of.

2. Including fields in Tooltips without affecting the viz

Part of the challenge was to replicate the Tooltip exactly:

screen-shot-2017-01-05-at-19-25-26

Order Date in its long form wasn’t a field I had used on the sheet, and if I dragged Order Date onto Detail it knackered things. I don’t even know how or why I hadn’t ever thought to just drag fields straight onto the Tooltip Card! Doh!

screen-shot-2017-01-05-at-19-31-09

3. The dots were damn fiddly!

My first attempt was a bit lazy. I just duplicated the RUNNING_SUM calc automatically generated by the table calculation used to draw the Primary Axis line. I then messed about with it:

screen-shot-2017-01-05-at-19-36-51

Looking at it less than a day later I know precisely why it failed, and it was a silly attempt really. If the cumulative % is greater than or equal to target, then plot the prior value. What do you reckon that looks like? Yep:

Screen Shot 2017-01-05 at 19.36.34.png

Close(ish) but no cigar. The key is in plotting only that first point beyond the parameterised “target”. Lots of other people seemed to sail through this. I did not! I saw reports of the use of WINDOW_MIN or LOOKUP. I stopped wasting time and downloaded Andy K’s workbook, and also looked at other solutions provided by other users to look at the options available:

i) Andy K – WINDOW_MIN

Here’s the calc for the dots:

screen-shot-2017-01-05-at-20-04-18

And the [% of Total Orders] – this is just a renamed default two-pass table calc:

screen-shot-2017-01-05-at-20-05-26

How does this translate into a way I can understand? It’s table time:

screen-shot-2017-01-05-at-20-15-08

So clearly a value is only returned for the first data point >= [Target]. Why? Lightbulb moment!

screen-shot-2017-01-05-at-20-17-48

The calc is just saying: If the minimum value in the window matching the criteria of >=[Target] is the same as the minimum value in the window matching the criteria of >=[Target], then pull that value through. Otherwise NULL.

ii) Jeremy Poole – LOOKUP

screen-shot-2017-01-05-at-20-24-18

It looks less complicated to me, so it’s immediately more appealing. Let’s create a table again, but I’ll break it down a bit more this time so we show the LOOKUP offset by zero, and the LOOKUP offset by -1:

screen-shot-2017-01-05-at-20-29-52

Yep, this completely resonates with how my brain operates. The calc says if the current value is greater than target, and the prior value is less, then this must be the first point above target, so pull it through. It’s simple and effective and I need to remember this!

It’s essentially the same logic as Andy used, but I think it is logically expressed in more of a “natural language” manner than the abstract terms that table calculations put on the table. If this is how #WorkoutWednesday pans out for the rest of the year, then we are all going to learn a lot of handy stuff.

Advertisements