
Ann set this week’s #WOW2021 challenge during #TC21 and chose to live stream her build. I couldn’t watch it but I did then manage to catch a snippet of Kyle Yetter who bravely chose to share his attempt at recreating the challenge via a live stream too with Ann & Luke watching. I had already completed my build by the time I watched Kyle, and it was interesting to see where our approaches differed.
The main via is a single chart, so I started by building out all the data I needed in tabular form, so I could verify the sort.
Defining all the calculations for the main viz
First up, the data needs to be compared to ‘today’ where ‘today’ is set to 01 Jan 2022. I used a parameter to store this
pToday
Date constant set to 01 Jan 2022

We also need to understand the latest order date per customer, so need
Max Order Date By Customer
DATE({FIXED [Customer ID]: MAX([Order Date])})
and so with this we can calculate the days since last order, which is one of our key measures.
Days Since Last Order
DATEDIFF(‘day’, [Max Order Date By Customer], [pToday])
The other measures we need are
#Orders
COUNTD([Order ID])
Avg. Order Value
SUM([Sales])/[#Orders]
along with Sales and Quantity.
These are the 5 main measures displayed, but there are some additional calculations needed for the display labels and tooltips.
The label to indicate the ‘time since last purchase’ is displayed in days or years, so for this I created 2 specific fields
LABEL:Days Since Last Order
IF [Days Since Last Order] <365 THEN [Days Since Last Order] END
This will only store a value when the days is less than 365. I then formatted this field to have a ‘ days’ suffix

Similarly, I created
LABEL:Years Since Last Order
IF [Days Since Last Order]>=365 THEN [Days Since Last Order]/365 END
which stores a value for records >=365 only. This was formatted to have 1 dp and the ‘ years’ suffix instead.
The colour of the first measure is based on whether the customer is considered ‘active’ or not. The threshold for this is managed via a parameter
pActiveCustThreshold
An integer parameter defaulted to 90.

We can then create
Is Active Customer?
[Days Since Last Order]<=[pActiveCustThreshold]
This is a boolean field and will return true or false, but to get the tooltip value to display appropriately, I edited the alias of this field (right click > Aliases)

For the tooltip on the ‘total products’ (ie quantity measure), we need to display the number of distinct products orders, which we can capture in
#Products
{FIXED [Customer ID]: COUNTD([Product ID])}
Pop all these fields, along with the Customer ID and Customer Name into a table and you can see validate all the values are as you expect

Sorting the Sort
We need another parameter to manage the sort.
pSort
I used an integer parameter, but set the display to the required text strings. This is because writing logic on integers is more efficient than strings

I then created a calculated field to determine the measure to sort by
Sort By
CASE [pSort]
WHEN 1 THEN SUM([Days Since Last Order]) * -1
WHEN 2 THEN [#Orders]
WHEN 3 THEN SUM([Sales])
WHEN 4 THEN [Avg. Order Value]
WHEN 5 THEN SUM([Quantity])
END
Notice the first measure is being multiplied by -1 since this measure is to be displayed in Ascending order rather than Descending.
In the table, amend the Customer ID pill to Sort by the Sort By field descending

Show the pSort parameter and test the functionality by switching the values.
Building the main viz
Having got all the calcs defined, this viz is relatively straight-forward. It is a multiple axis viz by Customer ID and Customer Name (note I use Customer ID as well, which I hide, just in case there are multiple customers with the same name).
- Customer ID and Customer Name on Rows. Apply sort to Customer ID as described above. Hide Customer ID (uncheck Show Header). Align Customer Name to right, and adjust font.
- Type in MIN(1) on Columns. Edit Axis to be fixed from 0-1. Add Is Active Customer? to Colour. and adjust. Add LABEL:Days Since Last Order and LABEL:Years Since Last Order to Label. Align Centre and match mark colour. Make sure the two fields are side by side on the label, and not underneath each other. Add Max Order Date By Customer to the Tooltip shelf. Adjust Tooltip
- Add #Orders to Columns. Strip off all the fields that have been automatically added to this marks shelf. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
- Add Sales to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
- Add Avg. Order Value to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
- Type in MIN(0) on the Columns shelf. Change mark type to Circle. Set the Colour accordingly. Add Quantity to Label. Add #Products to Tooltip. Adjust Tooltip
- Remove all row & column borders and gridlines, zero lines etc.
- Add a 0 constant Reference Line to the #Orders, Sales and Avg. Order Value axes.
- Hide the axes, hide field labels for rows.

Building the Viz in Tooltip
The Viz in Tooltip shows the Top 10 Products per Customer by Sales.
On a new sheet, add Customer ID, Customer Name and Product Name to Rows and Sales and Quantity (via Measure Values) to Text.

Add a Rank Quick Table Calculation to the Sales pill, then edit the table calc to be unique and to compute by Product Name

Click on the Sales rank pill , press Ctrl and drag it into the measures pane. Name the field Sales Rank. Format the field to have a # prefix. Add Sales back into the view.

Now drag the Sales Rank field from the Measure Values section and add to Rows, then change to be a discrete (blue) field, then move so it is to the left of the Product Name field. This should cause everything to re-sort into the required order automatically. It’s always worth double checking the table calc is still computing as expected.

Click on the Sales Rank pill and press Ctrl and this time drag to the Filter shelf and check values 1 to 10.
Now hide the Customer ID and Customer Name fields, and format the display.

Finally, set the display to Entire View. This will make the ‘view’ unreadable, but is necessary to ensure you don’t get a ‘viz is too large to display’ message when you get to add the Viz to the tooltip.
Adding the Viz in Tooltip
Back to the chart sheet, and edit the tooltip of the MIN(0) marks card. Insert the Top 10 sheet via Insert > Sheets. Adjust the width and height values and change the filter to just use <Customer ID>

Hovering on the circle mark should then display the Top 10 Products for that specific customer.
Adding the column headings
On the dashboard, use a horizontal layout container above the main viz. Add 6 text objects, enter the relevant text and centre. Adjust the width of each text object to line up with each column.
The help icon
Add a floating text object to the sheet – it’ll appear over the top of the chart. Position the text object to the top left, and set the background to white. Enter the text. From the context menu of the object, select the Add Show/Hide Button. Position the button in the appropriate location and resize. Edit the button options to have a grey background and show ? when hidden and X when shown.

I think that’s covered all the core points. My published viz is here.
Happy vizzin’! Stay Safe!
Donna