Which Sub-Categories are frequently ordered together?

Week 49 of 2021, and it was Ann Jackson’s turn to set her final #WOW challenge (sniff sniff!). Ann’s been setting challenges for the last 4 years, and we’re all going to miss her (I’m sure she may pop up as a guest challenger at some point in the future ….).

Ann decided to revisit her first ever challenge which I did complete here.

Obviously in 4 years, Tableau has moved on, and new features make building this concept a bit more straightforward. So let’s crack on.

Modelling the data

Over the years Ann has adapted her style which includes capitalisation of text. Whilst the challenge can be built with the Tableau provided Superstore Sales data source, Ann very kindly provided her own version which already contained the capitalised fields. I used this version.

To do this type of ‘basket analysis’ you need to use 2 instances of the data source which you model using relationships as follows :

Add a relation of Order ID = Order ID and Sub-Category <> Sub-Category

This results in a data pane which lists a set of fields from the Ann Jackson Superstore data source and a set of the same fields from the Ann Jackson Superstore1 data source. In this blog, I’ll reference these data sources as AJS and AJS1. If you examine a single order with multiple sub-categories you’ll see how the data is being related

The first 2 columns above are from the AJS data source – the order contains 4 different sub-categories. The last 2 columns are from the AJS1 data source, and you can see that for each AJS.Sub-Category, only 3 AJS1.Sub-Category records are listed. This allows us to see the combination of what was ordered with what.

Building the matrix

Add AJS.Sub-Category to Rows and AJS1.Sub-Category to Columns and you have the start of your matrix.

The number of orders is displayed in each cell, which is

# Orders

COUNTD([Order ID])

Add this onto Text

This adds Null column, which is the number of orders containing a single Sub-Category.

Right-click on the word Null in the column to select the column, and Edit Alias to rename the Null to NONE.

To remove the top right hand side of the matrix, I indexed each row/column.

Sub Cat Index

INDEX()

Sub Cat 2 Index

INDEX()

Add Sub Cat Index to Rows, change to discrete (blue pill) and move to be in front of AJS.Sub-Category. Change the table calculation so that it is computing by the AJS.Sub-Category field. Each row should now be numbered from 1 to 17.

Now add Sub Cat 2 Index to Columns, again change to discrete and move to be in front of the AJS1.Sub-Category pill. Edit the table calculation to verify it is computing by the AJS1.Sub-Category field. All the columns should now be numbered 1-18.

We want to remove the data for the records where the Row Index is greater then the Column Index

FILTER

[Sub Cat Index]>=[Sub Cat 2 Index]

Add this field to the Filter shelf, and set to true.

To colour the cells, add # Orders to the Colour shelf, and change the mark type to Square. Then edit the colour palette to use the Colour Brewer Blue Purple colour palette Ann advises (she’s used these palettes in the past, so I already have them installed, but you should be able to get them quickly from here).

Format the text to match mark colour and to be aligned centre. I also set the font to be Tableau Medium and bold. Set the column and row border divider lines to white.

Now uncheck show header against the two Sub Cat Index fields, and hide labels for column/ rows on the other column/row headings to remove them. Rotate the label for the columns and adjust the alignment, Adjust the font on both the column and row headers to bold and you should have the finished visual.

The Tooltips

We need to calculate the average sales contribution per order. We need 2 calculations for this, as we need to show a value for the AJS.Sub-Category on the rows and a value for the AJS1.Sub-Category displayed on the columns.

Average Sales (Rows)

SUM([Sales])/[# Orders]

Average Sales (Cols)

SUM([Sales (Ann Jackson Superstore1)])/[# Orders]

Add both of these to the Tooltip shelf. It’ll make a Null row appear – just right click and exclude.

Ann’s also been particular about some of the text displayed, so we need calculated fields to apply the required logic.

TOOLTIP – Sub Cat 2 Title

“& ” + [Sub-Category (Ann Jackson Superstore1)]

TOOLTIP- Sub Cat Text

IF [Sub Cat 2 Index]=1 THEN “SUB-CATEGORY” ELSE “SUB-CATEGORIES” END

TOOLTIP – Sub Cat 2

IF NOT(ISNULL([Sub-Category (Ann Jackson Superstore1)])) THEN [Sub-Category (Ann Jackson Superstore1)] + “:” END

Add all three fields to the Tooltip shelf, and adjust the tooltip as below

And with that, you should now have the fully completed challenge. My published version is here.

Finally, once again I want to thank Ann for all her contributions to the #WOW community over the last 4 years. I will miss her capitals and bold colour palettes – I’ll be storing away her pre-capitalised version of Superstore for sure :-)!

Happy vizzin’!

Donna

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s