Week 2 of #WOW2022 was Kyle Yetter’s first challenge as official WOW coach. On first glance when it was posted on Twitter, I thought it didn’t look too bad… I figured they’d be some ‘baselining’ of dates that I’d need to do to get the axis to display.
However, it was a little trickier than I first anticipated, mainly in trying to ensure I got the right values to match with Kyle’s posted solution (which at one point seemed to change while I was building). I’ve also realised since clicking on the link to the challenge again, that it was tagged as an LoD challenge, although there was nothing specific in the requirements indicating this was a requirement. I don’t think I used any LoDs…
Anyway onto the build, and I’m going to start by getting the dates all sorted, as this I found was the trickiest part.
Firstly connect to the data, then verify that the date properties of the data source are set to start the week on a Monday (right click data source > Date Properties
Build a basic view that displays Sales by the week of Order Date and the year of Order Date. Exclude 2018 since we’re only focussing on up to the last 2 years of data.
Examining this data compared to the solution, the first points of each line relate to the data shown against the 7 Jan 2019, 6 Jan 2020 and 4 Jan 2021. Ie the first point for each line is the first Monday in the year.
For simplicity, to make some of the calculations easier to read , I’m going to store the start date of the order date week in a field.
Order Date Week Start
DATE(DATETRUNC(‘week’, [Order Date]))
This means I can easily work out the last day of the week
Order Date Week End
DATE(DATEADD(‘day’,6,[Order Date Week Start]))
Add these fields as discrete exact dates (blue pills) onto the table and remove the existing WEEK(Order Date) field since this is the same as Order Date Week Start
When we examine the end points of each line in the solution, the points relate to the data shown against the week starting 30 Dec 2019 to 5 Jan 2020, 28 Dec 2020 to 3 Jan 2021 and 22 Nov to 28 Nov 2021. For the first 2 points, we can see the data is ‘spread’ across 2 years (ie 2 columns), which means when categorising the data ‘by year’, using the Year associated to the Order Date itself isn’t going to work. We need something else
Year Group
YEAR([Order Date Week Start])
Move this field into the Dimensions pane (above the line), then add to the table, replacing the existing YEAR(Order Date) field. 2018 will appear due to the very first row of data, but don’t worry about this for now.
If you scroll down to the week starting 30 Dec 2019, all the data is now aggregated in a single Year Group.
So things are starting to take shape. We can now work on how to filter the data.
The requirements indicate we should imagine ‘today’ is 1st Dec 2021, so we’ll use a parameter to hold this value.
Today
Date parameter defaulted to 1 Dec 2021
We only want to show data for the last 2 years and for complete weeks up to today
Core Data to Include
[Order Date Week End] < [Today] AND YEAR([Order Date Week Start]) >= YEAR([Today])-2
Remove the existing filter and add this field instead, set to True. You should now have just 3 columns and the data starting and ending at the right points.
The next area of focus is to think about how the data is going to be presented – the lines are all plotted against a single continuous (green) date axis, so we need to ‘baseline’ the dates, that is adjust the dates so they are all on the same year.
Date To Plot
MAKEDATE(2021, MONTH([Order Date Week Start]), DAY([Order Date Week Start]))
this is basically setting the week start dates to the equivalent date in 2021.
In the table we’ve been building, add Date To Plot to Rows and set to the week level and be discrete (blue). Remove the Order Date Week Start pill and move the Order Date Week End to the Tooltip as this is where this pill will be relevant in the final viz.
We’re starting to now see how the data comes together, but we’ve still got some steps to go.
I’m going to adjust the Year Group, so we can present the Current, Previous, Last 2 Yrs labels. Change as follows
Year Group
YEAR([Order Date Week Start]) – YEAR([Today])
This returns values -2, -1, 0 which means the values will be consistent even if the ‘Today’ value changes. The values can then be aliased (right click Year Group > Aliases
Next focus is on the % difference in sales. Add a Percent Difference quick table calculation to the existing Sales pill. The vales will change to those we can see when hovering over the points in the solution.
Edit the table calculation and modify to explicitly compute by Year Group, which is important to understand as, when we build the viz, whether the data is going across or down may change, so ‘fixing’ like this ensures we retain the values we know are correct.
In order to manage the custom colour formatting in the tooltip, we’re going to ‘bake’ this field as a calculated field. Press CTRL, then click and drag the pill into the data pane and name the field accordingly. If you examine the field, it’ll probably look quite complex
I’m going to start building the viz now, and then we’ll add the final calcs needed for the formatting later.
On a new sheet
Add Core Data to Include to Filter and set to True
Add Date to Plot to Columns and set to a continuous week level (green pill)
Add Sales to Rows
Add Year Group to Colour and adjust accordingly
Reorder the values in the Year Group colour legend, so the ‘current’ line in the chart is displayed on the top, and the ‘2 yrs ago’ line is at the bottom
Format the WEEK(Date To Plot) field to be a custom date of dd mmm (ie 10 Jan)
Format the Sales axis to be $ with 0 dp
Add Order Date Week End to the Tooltip shelf and format the field to custom date format of mmm dd (ie Jan 10).
Now we need a couple of additional calcs to help format the % sales difference displayed on the tooltip.
% Sales Diff +ve
IF [% Sales Diff] >= 0 THEN [% Sales Diff] END
% Sales Diff -ve
IF [% Sales Diff] < 0 THEN [% Sales Diff] END
Format both these fields with a custom number format as ▲0.0%;▼0.0%
Add both these fields onto the Tooltip and adjust the table calculation settings of each to compute using by Year Group only
The 2 yrs ago line has no % Sales Diff value, and also no label, so we need a field to help this too
TOOLTIP: Label YoY%
IF NOT(ISNULL([% Sales Diff])) THEN ‘YoY%:’ END
This means the text ‘YoY%’ will only display if there is a % Sales Diff value.
Add this field onto the Tooltip too, and again adjust the table calculation settings.
Now format the text and tooltip as below, setting the two % Sales Diff pills to be side by side and colouring accordingly.
Hopefully, this means you now have a completed viz
My published viz is here. And nope… no LODs used 🙂 There are a few more calculated fields than what Kyle mentioned, but I could have condensed these by not having so many ‘building blocks’, but this may have made it harder to read.
I’m now off to check out Kyle’s solution and see whether I really over complicated anything…
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
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!
A beautiful looking bump chart for week 5 of #WOW2020; a challenge set by Luke inspired by a conversation with Zach Bowders.
Immediately looking at the chart, I knew it was highly likely to involve dual axis (at least for the ‘standard’ challenge).
One axis to represent the line; the other to represent the blocks. The table calculation of Rank was also going to be involved, since the positioning was all based on the Sales rank (which was assumed rather than explicitly stated).
Basic Data Structure
First up I just wanted to sense check I had made the correct assumption in respect of ranking the sales, so using the 2019.4 version of Superstore data, I built a very basic view; Region on Rows, MONTH(Order Date) on Columns and SUM(Sales) on Text. Applying the Quick Table Calculation of Rank to the SUM(Sales) pill, and ensuring it was computing for each Month, changed the displayed output to the rank expected.
With all that clarified, I could focus on building the viz. Let’s start with the ‘standard’ view.
Standard View
Having built my ‘basic data’ tabular structure, I started by duplicating this view, and then applying the following changes :
Moved the SUM(Sales) rank pill from Text to Rows
Moved Region to Colour
Editing the Rank of Sales axis to reverse it, and adjusting the colours associated to each Region and we have the Line chart completed. At this point I chose to rename the Region field to COLOUR:Region (Line), as I figured I’d need another instance of Region later to colour the blocks (which were different colours).
Dual Axis
Duplicate the SUM(Sales) Rank pill (by clicking on the pill, and holding down Ctrl) and place alongside the existing pill on the Rows shelf.
Change the mark type of the second instance to be Square.
Duplicate the Colour:Region (Line) field and rename to Colour:Region(Block), and add this to the Colour shelf of the Square mark type.
At this point you’ll end up with a single row of blocks. This is because the Rank table calculation of the 2nd mark type was based on the Colour:Region (Line) which no longer exists on this 2nd instance, and it needs to be changed to Colour:Region (Block).
Adjust the colours to suit
Make the chart Dual Axis, Synchronise the 2nd axis, and Move Marks to Back of that 2nd axis (right click on the axis to find this option).
Displaying the Labels
The challenge requires the rank position to be displayed on the ‘January’ blocks and the Region to be displayed on the ‘December’ blocks, BUT to only use one calculation on the Label shelf. I achieved this by creating the following calculated fields:
Label:Rank
CASE RANK(SUM([Sales])) WHEN 1 THEN ‘1st’ WHEN 2 THEN ‘2nd’ WHEN 3 THEN ‘3rd’ WHEN 4 THEN ‘4th’ END
which simply translates the rank number into its string form, and then
Label:To Display
CASE MIN(MONTH([Order Date])) WHEN 1 THEN [LABEL:Rank] WHEN 12 THEN ATTR([Region]) END
When it’s January, then use the Rank Label we created, but when its 12, use the Region field (or equivalent if its been renamed).
On face value, this may look confusing – how can the MIN return multiple values? But as this is Table Calculation field (as it references the Label:Rank field which is based on the Sales rank), and it’s computing per month, the month is changing. You’d get the same result if you change MIN to MAX. If the calculation was considering the whole table, this logic wouldn’t work.
Add this to the Label shelf of either mark, set the table calculation to be applied for each month (as above), and format the text to be middle centre.
Hide both the axis headers, remove the column/row lines and any gridlines, change the format of the date (via format -> axis) to be abbreviated, and ‘hide field labels for columns’. Remove tooltips from both marks.
At this point, you may think the gapping between the squares is too big in some places, too small in others. Don’t yet try to adjust the sizing, until you’ve got the chart on the dashboard, as how the display looks on the sheet, doesn’t always reflect the dashboard.
I created the dashboard to the 700×350 size specified, added the viz, and then increased the Size of the Square mark type. This is what the sheet looked like
But this is the dashboard with the legends and sheet title removed :
With a few adjustments, this is the core of the ‘standard’ version.
Advanced Version
The advanced requirement was to not use the Square or Shape mark type.
I started by duplicating the version I’d already built, then just tried a few mark types I thought might help to see if I got any inspiration – bar and gantt, but neither clicked.
1st Attempt
NOTE – detailing this for information to understand my thought process, but this doesn’t ultimately give the desired outcome, so just read only 🙂
Then I had a moment of inspiration (or thought I did) and decided to try using Text mark type. I used my ‘go to’ geometic shapes website, and copied the ‘square’ into a calculated field, which I added to the Text shelf, and increased the Size as large as possible
This gave me something very close
But I needed one of the axis to be shifted slightly, so used an offset parameter to move one of the axis around. Again it took a bit of shuffling to get it looking ok on the dashboard, but once satisfied I published to Tableau Public, only to find that rendered everything differently 😦
So back to the drawing board…
2nd Attempt (that worked)
In working through the above, I’d expanded on some concepts I hadn’t done when flicking through the standard mark types, and Gantt type was the mark I needed. Ultimately this wasn’t something that I managed without a bit of trial and error. I’m just going to document what I ultimately ended up with.
Take the ‘standard’ view of the chart you’ve hopefully already built. Change the mark type of the 2nd axis to Gantt rather than Square.
Create a new calculated field
Size
0.9
and add this to the Size shelf of the Gantt mark, changing the aggregation to MIN rather than SUM. Setting to 1 will make the marks butt up to each other.
As both axis were identical with the results of 1-4., the line points are positioned at the base of the Gantt. I needed to adjust the line axis to be at say 1.5, 2.5 etc, although exactly what the offset needed to be I wasn’t sure, so I created a Parameter called Offset
I then changed the first SUM(Sales) rank pill, to add Offset, simply by ‘typing into’ the pill
I found a value of 0.4 worked for me, but added the Offset parameter to the sheet so I could adjust up or down as needed
Removing the axis headers as before and adding to the dashboard may have required more minor adjustments, but on publication, this version didn’t go all wonky.
Donna + DataViz 