Luke set the #WOW challenge this week, stating it had a difficulty rating of 10/10, so it was with some trepidation when I sat down to tackle it. Was it going to be as difficult as Luke’s radial bar challenge from 2018 week 10….?

Looking at the viz, and reading through the requirements, it didn’t seem as bad to me – it’s a table calculations challenge, and I’m OK with those. I started using Tableau before LoDs were invented, so table calculations don’t scare too much. I’m no expert though, and it still often takes a bit of trial & error to get the settings right.

Building out the Calcs

With a lot of challenges, I often start by just trying to build a tabular view of the data to sense check I’m getting the right numbers, which I then publish onto Tableau Public along with my viz. With a table calculation challenge, building the table of data is crucial.

The 3 key pieces of existing data needed for this challenge are Customer Name, Order Date & Order ID.

To start we want to put these on the rows, but given this will initially generate a lot of rows of data, I chose to arbitrarily filter to a random set of 20 or so customers (include Noel Staavos, as he’s a slight exception, which may catch you out later).

You can see from the above, Noel has multiple orders on the same day, which we need to handle.

The first requirement states we need to order the data by total number of orders per customer, so for this we need the 1st of many calculated fields (this one isn’t a table calculation, though it could be….):

Total Orders

{FIXED [Customer Name]: COUNTD([Order ID])}

Add this as a discrete pill on the Rows (since the final viz needs to show it this way too). I’ve placed it after the Customer Name but it doesn’t have to be there at this point.

We need to sort the customers by this field, so click on Customer Name and select Sort to bring up the Sort dialog. Choose to Sort By Field, Sort Order Descending, Field Name Total Orders. Aggregation Sum

We’ve now got our data ordered in the way we need. The next step is to work out the 90-day reorder rate per customer, which we will tackle in several steps. In much of what follows, some of the calculations could well be done in one calculation, but I like to see the ‘building blocks’ to help verify the calcs are correct.

First up we need to work out how many days between each order, and to figure this out we need to compare the Order Date on each line to the Order Date of the previous line.

I want to display the date of the previous order on the same line as the current order, so create

Previous Order Date

LOOKUP(ATTR([Order Date]),-1)

This ‘looks up’ the Order Date on the previous row. If I wanted to look at the next row, the second attribute would be 1 rather than -1. Or if I wanted to look at the data in the row 2 rows before the current one, I’d use -2.

Add Previous Order Date to the Rows as a discrete field. By default it’ll probably show as ‘Null’, but this is because the table calculation is computing across the table (so is looking for a previous column containing Order Date which doesn’t exist), whereas we want to look down it (ie by Row).

Click on the triangle against the Previous Order Date pill (the triangle indicates its a table calculation), and select Edit Table Calculation

We need to change the settings so they calculate for each Customer Name. Set to Specific Dimensions, and uncheck Customer Name

Note : Its worth having a read/watch of Andy Kriebel‘s Table Calculation Overview post to help you understand table calcs better.

You can see from the above, that for Noel Staavos’ multiple order on the same day, one of the orders is comparing against an order on a different date, while the other is comparing to the other order made on the same date.

Ok, so now we have the current date & previous date on the same row, we now need to work out the number of days between the dates.

Days Since Previous Order

DATEDIFF(‘day’, [Previous Order Date],ATTR([Order Date]))

Add this to the Text shelf. Hopefully it should automatically use the same table calculation settings defined for Previous Order Date, but if the numbers look off, double check the settings. They should match.

Now we can work out if the order is within 90 days of the previous order

Reorder Within 90 Days

IF ISNULL([Previous Order Date]) THEN NULL
ELSEIF [Days Since Previous Order]<=90 THEN 1
ELSE 0 END

Note, I am purposely choosing to output 1 or 0 (ie a number) rather than true or false (a boolean), as it will make the next calculation easier.

Again add Reorder Within 90 Days to the table, and again sense check the table calculation settings if things don’t look right.

Now we have the information we need to work out the reorder rate per customer, which is the number of records where Reorder Within 90 Days is 1 as a proportion of the number of records with a Reorder Within 90 Days value of either 0 or 1, since the requirements state the first order for each customer shouldn’t be included in the metric.

90-Day Reorder Rate

ZN(WINDOW_SUM([Reorder Within 90 Days])/WINDOW_COUNT([Reorder Within 90 Days]))

Set this to be a percentage with 0 dp.

By choosing to set Reorder Within 90 Days to an integer, the WINDOW_SUM() is simply summing up the 1s & 0s in the column. WINDOW_COUNT() is just counting the number of 1s & 0s there are in the column. Wrapping with a ZN means any Customers without any reorders will report as 0% rather than NULL.

Let’s add that to the table now too. You’ll see it reports the same value down the whole table across all customers, whereas we need it to show a different value per customer (although still show the same value for all the rows of the same customer).

We need to once again adjust the table calculation settings for this field.

What you’ll notice this time though, is there are Nested Calculations within this field, so the settings need to be checked for both the Previous Order Date calculation and the 90-Day Reorder Rate calculation

Both need to be identical with Order Date and Order ID being checked.

At this point, we have enough that we could start building the main viz, but I’m going to continue building out the ‘check sheet’ with the data I need for the KPIs too.

For the Overall Reorder Rate < 90 days we need to get a count of all the reorders within 90 days across all customers, as a proportion of all reorders (ie not the 1st order for each customer).

Count Reorders < 90 days per customer

IF FIRST()=0 THEN WINDOW_SUM([Reorder Within 90 Days]) END

I’m choosing to output the value only against the 1st row for each customer. This is because I’m going to be adding up this column shortly, and if I outputted the value against each row, I’d be double-counting.

Add this field to the table, and once again verify the table calculation settings are applied to each nested calculation. If done right, you should get the correct number against the first row for each customer

Now I want a sum of this column

Total Reorders < 90 Days

IF FIRST() =0 THEN WINDOW_SUM([Count Reorders <90 days per Customer]) END

Again, I’m choosing just to show this value against the 1st row, but this time it will be the first row in the whole table, as this time the table calculation needs to considering all the customers.

Add this field to the view. It’ll probably automatically give you the right number, but it’s worth having a look at the table calculation settings.

It contains 3 nested calculations this time : Total Reorders < 90 days, Count Reorders < 90 days per Customer, Previous Order Date.

The settings for Count Reorders < 90 days per Customer and Previous Order Date should be as before, with Order Date & Order ID both checked.

The setting for Total Reorders < 90 days is different though. By default it like shows Table(down), which gives the right result, but to ensure things don’t go awry if the pill gets moved around for some reason, I like to explicitly set the computation, by changing to Specific Dimensions and selecting all 3 fields

So this gives me one of the values I need to help me work out the overall rate; I now need the other

Count All Reorders

IF FIRST()=0 THEN WINDOW_COUNT([Reorder Within 90 Days]) END

This again is a nested calculation. Previous Order Date should be set with Order Date & Order ID as usual, and Count All Reorders should be set across all 3 fields.

Now we have the numbers to work out

Overall Reorder Rate

[Total Reorders <90 days]/[Count All Reorders]

which is set to a Percentage to 0 dp.

Add this onto the table and check the calculation settings again. This time there are 4 nested calculations

• Total Reorders < 90 days : set to all 3 fields
• Count Reorders < 90 days per Customer : set to Order Date & Order ID
• Previous Order Date : set to Order Date & Order ID
• Count All Reorders : set to all 3 fields

Remember, you may have filtered the customers in your view, so you may get a different value from the solution at this point!

Right onto the final KPI – average number of reorders per customer. For this we need the total number of reorders, which we’ve already got- Count All Reorders, and number of customers.

There’s probably a simpler way of doing this, but I’m continuing down the same route I’ve been working on.

First I want a count of the customer for each customer (which is 1), then I want to sum that up.

Count Customers

IF FIRST()=0 THEN COUNTD([Customer Name]) END

then

Total Customers

IF FIRST()=0 THEN WINDOW_SUM([Count Customers]) END

When added to the table, this is again a nested calculation with Count Customers set to Order Date & Order ID, and Total Customers set to all 3.

Now we can work out

Average Reorders Per Customer

[Count All Reorders]/[Total Customers]

which is set to be a number to 1 decimal place.

Adding this to the table, and verify the nested calculations are all set properly

• Count All Reorders : set to all 3 fields
• Previous Order Date : set to Order Date & Order ID
• Total Customer : set to all 3 fields
• Count Customers : set to Order Date & Order ID

Yay! We’ve now got all the values we need to build the KPI table and the main Viz.

I would recommend naming this sheet as Check Data or similar.

Building the KPI Viz

First step, duplicate the Check Data sheet.

For the KPIs, we only need 2 of the measures we’ve created, so remove all the pills from the Measure Values section except Overall Reorder Rate and Avg Reorders per Customer.

Then remove Total Orders and Previous Order Date from the Rows.

Due to the way table calculations work, we need to keep Customer Name, Order Date & Order ID in the view, but we only need the 1st row in the whole table, as that is where the data we want has been stored.

Create a new field

First Row

FIRST()=0

Add this to the Filter shelf and select True

Hide Customer Name, Order Date & Order ID by unchecking Show Header from each pill

We need to create a fake axis to get the display we need. In the Columns type in Min(0) twice, then remove Measure Names

Expand one of the Agg(Min(0)) cards on the left, and then drag the Overall Reorder Rate pill from the Measure Values section at the bottom, so it sits directly on top of the Measure Values pill

The Avg Reorders per Customer will now be on the Label shelf of the other AGG(Min(0)) card.

Change the mark type of both cards to Text.

Uncheck Show Header against the MIN(0) pills, and format to remove all columns/row/grid & zero lines.

Now you can format the text for each mark to be the appropriate size and with the relevant wording.

Then finally turn all tooltips off, and remove the Customer Name filter if it’s still there.

Hopefully you should get the numbers that now match the solution! Rename the sheet KPIs.

Building the main Viz

Once again start by duplicating the Check Data sheet we built.

Move Order ID and Previous Order Date onto the Detail shelf.

Move 90-Day Reorder Rate onto the Rows, and set to discrete

Move Order Date to Columns and change to continuous. Remove Measure Names from Columns.

Change the mark type to shape and move the Reorder Within 90 Days pill from the Measure Values section onto the Shape shelf. Change the shapes so NULL is the triangle and 0 and 1 are both diamonds.

Add Reorder Within 90 Days to the Colour shelf too, by clicking on the one on the Shape shelf, holding down Ctrl and dragging onto Colour. This will have the affect of duplicating the pill and will retain the table calculation settings. If you can’t get this to work, then drag from the Measures pane on the right, but make sure you set the table calc settings to match.

Change the colours so Null & 0 are grey (#CAC4BE) and 1 is purple (#5557EB).

The Tooltip for each shape also needs to be coloured accordingly, based on whether the mark represents the First order, a reorder over 90 days, or a reorder under 90 days (I chose to add the logic for first). I created 3 calculated fields for this:

Tooltip: First

IF FIRST()=0 THEN ‘First Order’ END

Tooltip:Over 90

IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END

Tooltip:Witin 90

IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END

I placed all 3 of these fields on the Tooltip shelf which was then formatted as:

while all 3 fields are in the tooltip, only 1 will ever actually display a value,

Make sure the table calculation settings for all 3 tooltip fields are set with Order Date & Order ID checked.

Now we need to create the line to join the marks up.

Duplicate the Order Date pill on Columns to create 2 instances of the pill sitting side by side. Set to Dual axis, synchronise and s’move marks to the back’ so the lines are behind the shapes.

Change the mark type on the Order Date (2) card to line. The requirements state

“Make sure the “line” color matches with the following mark. It must be a single color“.

Hmmmm… I don’t think that last sentence was there when I tackled the challenge… I just read the line colour ‘matches’ with the following mark, so to do this I added Previous Order Date to the Path shelf. The line graduates in colour, but from the colour of preceding mark to the colour of the following mark.

Looking at Luke’s solution now that I’ve seen this statement, he chose to use a Gantt mark type to represent the line, which meant each gantt block was coloured fully. Oh well… I didn’t quite get this then 🙂

Stop the tooltips from showing on the line, by deleting the text in the tooltip, then its just a case of re-sizing the marks, and formatting to suit. Set the background colour of the worksheet to grey.

Adding the red header on the dashboard

To get the red header displayed above the table, add, a container onto the dashboard and add the viz to sit inside it. With the container selected (identified by the blue border), set the background of the container object to red (#D81159).

Then select the viz itself (identified by the grey border), and adjust the outer padding settings, to have 0 left, bottom & right, and 4 at the top.

Hopefully this should now be pretty much everything needed to get this challenge sorted. If I’ve missed anything, please comment. My published viz is here.

It’s a pretty lengthy read, so if you’ve got this far – well done & thank you!

Happy vizzin’!

Donna