For week 29 of #WorkoutWednesday2019, Luke Stanke set the challenge above (described here), which is comparing the overall average of orders placed per day against the average for each month.

On the face of it, this didn’t seem too bad (especially for a challenge set by Luke).  Some days when I tackle these challenges, the path I take can be long and arduous with several false starts along the way; the result being more of a ‘happy accident’ than anything of real coherence. Luckily for me, since I’d promised to start blogging on these challenges, this wasn’t one of them.

My usual approach to any of these challenges is to take some time reviewing the published viz on Tableau Public; hovering over the various marks to understand what’s on the tooltips and seeing if I can get any clues into how the various objects (marks, views, legends, titles etc) have been rendered, whether axis are being sneakily used, understanding the interactivity at play etc.

My next step is to then get the figures right based on what’s presented.  I typically like to create a ‘data’ sheet in the workbook – a tabular view of the data and associated calculations I have built, so I can easily sense check with the published viz whether my assumptions and computations are valid, and it provides a useful reference point if I’m trying to figure out what I did sometime later. 

So I started this challenge the same way.

First up, I assumed the ‘line’ was probably a reference line, and hovering over the viz confirmed this.  Everything else seemed pretty straightforward, so onto the figures.

1. Average no of orders per day per segment

This is what the line represents, and do to this I need to find the total number of orders placed per segment and the total number of days on which orders were placed for each segment.  The average is then just count orders / count days.

Some people will create everything in a single calculated field, but I like to break things up to help me troubleshoot if things don’t quite work as intended, so I ended up with 3 calculated fields, and since the overall average was required at a level higher than the level of detail being displayed (which is month in this viz), I figured LoD calculations were the way to go.

Count Orders Per Segment

{FIXED [Segment]: COUNTD([Order ID])}

For each segment, count the number of distinct orders that exist.

Count Days Per Segment

{FIXED [Segment]: COUNTD([Order Date])}

For each segment, count the number of distinct days on which an order was placed.

Overall Avg Orders Per Day Per Segment

SUM([Count Orders per Segment]) / SUM([Count Days Per Segment])

Format this to 2dp.

2. Avg no of orders per day per segment per month

This is what each coloured ‘bar’ represents, and for this I needed to find the total number of orders placed per segment per month, and the total number of days in each month on which orders were placed for each segment.   I chose to stick with LoDs again for this :

Count Orders Per Segment Per Month

{FIXED [Segment], MONTH([Order Date]): COUNTD([Order ID])}

For each segment and month, count the number of distinct orders that exist.

Count Days Per Segment Per Month

{FIXED [Segment], MONTH([Order Date]): COUNTD([Order Date])}

For each segment and month, count the number of distinct days that an order was placed.

Avg Orders Per Day Per Segment Per Month

SUM([Count Orders Per Segment Per Month]) / SUM([Count Days Per Segment Per Month])

Format this to 2dp.

Putting these fields out in a table, you can see the first three columns contain the same values for each segment even though the data is being displayed at the month level.  The final 3 columns are the monthly figures.  These numbers all reconcile back to the data displayed on the viz.

So I’m heading in the right direction, now onto the next bit.

The requirement is to “Label the bars with the percent difference between the monthly value and the overall value”, so I need another field…

3.  % Difference

…which is basically the difference between column 3 and column 6 above, as a proportion of column 3

([Avg Orders Per Day Per Segment Per Month]-[Overall Avg Orders Per Day Per Segment])/[Overall Avg Orders Per Day Per Segment]

By default though, this shows 0s when added to the table as the numbers calculated are actually 0.38, -0.33 etc, so it needs formatting as a percentage to 0 decimal places.  However, applying the standard ‘percentage’ number format won’t quite cut it for this challenge, as Luke has labelled the positive numbers with a + too.  To get this, I need to apply custom formatting.

The easiest way to get this right I find, is to use one of the default number formatting options to set the number in whatever ‘main’ format you need, eg if you have a monetary value to display in £k, use the Currency (Custom) to get all the settings right.  In this instance though I want Percentage set to 0 dp..

Once done, press ok to close the dialog box, then go to set the number format again, but this time choose Custom.  The formatting ‘style’ applied previously will be shown

and it can then be modified to get the desired format, in this instance I change to

It’s often these little formatting tips that get thrown into the #WorkoutWednesday challenges that I love the most, although sometimes they can be tucked away and hard to find (or remember).

So at this point I now think I can start to build the viz, so adding the various pills I need, and adding a reference line I get this…

A line chart isn’t what I want though, and it’s not bars either, as they start from 0.  The mark type I need is gantt

which makes me realise I need to create another field…

4.  Difference

The gantt chart has the marks in the right position, but to make the ‘bars’ I need to alter the size, and that size is the difference between the mark position and reference line, which is

([Avg Orders Per Day Per Segment Per Month]-[Overall Avg Orders Per Day Per Segment])

Placing this field on size, gives me this but while the bars are the right height, they’re not in the right place.

Using gantt bars in this way is akin to the technique used in building waterfall charts, and is rectified simply by applying a multiple of -1 to the pill on the size shelf (in this instance [Difference]).

As a shortcut, I simply type this into the pill on the size shelf itself, which is a nifty little trick.

And voila! The viz is essentially there now.  Just need to add colour and further formatting …

The ‘bars’ are coloured based on whether they’re above or below the line, ie whether the difference is positive or negative, so another field is needed :

5. COLOUR : Difference

IF [Difference]>=0 THEN ‘green’ ELSE ‘blue’ END

And popping this on the colour shelf, and adjusting the colours to suit, gives me

The main thing left now is the little formatting bits and pieces :

Borders : remove columns

Axis : remove title, set to be independent, and set not to start at zero

Reference Line : change the label font size, align left middle, and set the shading to have a white background and 100% opacity

Reference Line TootlTip (new feature in v2019.2): set to Custom as below

Note if I’d simply called my field ‘Average’ rather than ‘Overall Avg Orders Per Day Per Segment’, I wouldn’t have needed this step, though it’s always useful to try out the new ‘little’ features if you can 🙂

Label : add [% Difference] to label shelf, and format centre middle

Tooltip : add relevant fields and adjust formatting as appropriate

The final thing I noticed was the axis scale – my scale was 2dp due to the formatting of my [Avg Orders Per Segment Per Month] field.  Luke’s axis was mixed – some scales at 1dp and some with 0.

I tried a few things, like formatting the axis to be Number (Standard) which has the effect of ‘automatically’ showing a number as a decimal or a whole numbers (something I recall from a very early WorkoutWednesday challenge a couple of years back).  But this didn’t give me the desired effect.  I ended up setting the axis format to be 1 dp.  But this then meant the value on the tooltip also ended up displaying as 1dp, when I wanted it to be 2 *sigh*

To fix this I created a duplicate field of the measure being displayed (a copy of [Avg Orders Per Segment Per Month]), and formatted it to 2dp, and placed this on the tooltip instead. 

I’m going to have to have a peak at Luke’s solution to figure out what magic he’s done here….

Very final step was to add to a dashboard, and add the title and my own custom footer.

So phew done!  My published version is here.

If you’ve got this far, thank you for reading J  I can’t guarantee all write ups will be to this level – it’ll partly depend on the challenge itself, and what path I head down to solve it.

I’m now off to have a peak at Luke’s challenge to figure out that pesky axis……. or I would if his workbook was downloadable 😦

Happy vizzin!

Donna