
This week Candra Mcrae provided a market basket analysis challenge which had a bit of all sorts going on as well as the core ‘what else was bought with product x’ puzzle – parameter actions, sheet swapping, custom toggle buttons.
Hopefully I’ll cover all the key elements :
- Identifying the 2nd customer order
- Identifying the orders containing the selected sub-category
- Identifying the ‘other’ sub-categories ordered
- Building the bar chart
- Building the tree map
- Creating the toggle button
- Controlling what viz to display
- Creating the dashboard and interactivity
Identifying the 2nd customer order
The requirements state the analysis is based on returning customers, and specifically their 2nd order. So we need a way to identify this set of data.
In the superstore data set, a customer only ever places one order on a day, so we will make use of this feature, but this technique won’t always work in other situations – I personally have been known to place multiple orders with Amazon on the same day, especially when Christmas shopping!
If we look at the dates customers have ordered, we’re looking to identify the orders associated to the customer & dates highlighted below

We can identify whether the date is the first order date using a FIXED LoD calculation
Is Customer’s First Order?
{FIXED [Customer ID]: MIN([Order Date])} = [Order Date]
The FIXED LoD statement within the {..} is finding the minimum Order Date for each Customer ID. This is then being checked against each Order Date, and returning true when it matches.

I solved this, by first adding Is Customer’s First Order? = False as a data source filter (right click on the Superstore data source -> edit data source filters)


This works due to Tableau’s order of operations; the data source filter is filtering the data ‘early on’, so the rows of data we’re now working against, has no knowledge of those rows. Subsequently the customer’s first order is now actually reporting the customer’s 2nd order as the minimum (aka first) order date.
We can now add Is Customer’s First Order? to the Filter shelf and set to True, to just work with this set of orders.

NOTE – If the data source has not yet been extracted, which will be required if you want to publish to Tableau Public, then you will need to do one of the following :
- At the point of extraction, the data source filter will automatically be applied as an extract filter. If you keep it, then after extracting, remove the data source filter if its still there, otherwise you’ll find you’re reporting against the 3rd customer order OR
- At the point of extraction, remove the extract filter that is automatically applied. After extraction, re-add the data source filter if it’s been removed.
An alternative to using any data source/extract filters is to use the following LoD (which I only got my head round after building out the above, so I didn’t put it in my solution., but works just as well).
Is Customer’s 2nd Order?
{FIXED [Customer ID]:MIN(IIF([Is Customer’s First Order]=False,[Order Date],NULL))} = [Order Date]
The IIF statement is getting the Order Date for all the records which aren’t the Customer’s 1st order. The FIXED LOD, is then returning the minimum/smallest of these dates, which will be the date of the 2nd order. This is then compared to each Order Date to identify the appropriate record.
If you strip off any data source/extract filters, you can see how this works, and Is Customer’s 2nd Order? can be added to the Filter shelf instead, set to True, to get the required rows.

Identifying the orders containing the selected sub-category
To start with we need a parameter to store the selected Sub-Category. Right click on Sub-Category and select Create -> Parameter to create a string parameter which lists the sub-categories. I named mine pSelected_SubCat and it’s defaulted to Accessories.

What we’re now trying to find is the rows relating to Order IDs that contain the stated Sub-Category, in this instance Accessories

So we first need to identify the rows which match
Customer Ordered Selected SubCat
IIF([pSelected_SubCat]=[Sub-Category],1,0)
Add this to the view, and you can see the rows associated to Accessories are marked with 1

For this, I used Sets as per the technique described in the link Candra provided within the hint.
We want the set of Order IDs where the sum of the Customer Ordered Selected SubCat field is >=1. Right click on Order ID and Create -> Set. On the Condition tab, set the relevant properties.

Adding the Orders with Selected SubCat set into the view, and we can see it’s picked up the rows we care about, and we can now filter by this set


An alternative to using Sets, is to use another FIXED LoD calculation
{FIXED [Order ID]: SUM([Customer Ordered Selected SubCat])}>=1
For each Order ID, sum up the Customer Ordered Selected SubCat field, and if it’s >=1 then it will return True, and you can then filter by this instead.
Identifying the ‘other’ sub-categories ordered
We want to count the orders which contain each Sub-Category, which is simply
Count Orders
COUNTD([Order ID])
With a basic view that is filtered as required, that lists Sub-Category by Count Orders (and sorted by Count Orders descending), we get

And you’ll see that Accessories is listed at the top. Change the parameter, and that value will also be listed. But we don’t want to see this – we need to filter it out. For this, we can create
Other Items Ordered
If [pSelected_SubCat]=[Sub-Category] THEN ‘N/A’ ELSE [Sub-Category] END
Pop this into the view, and you can see the ‘N/A’ is listed against the selected Sub-Category.

Building the bar chart
So now we’ve got the 3 filters we need to identify the rows of data we need to consider, we can easily build a bar chart.

Profit Ratio
SUM([Profit])/SUM([Sales])
Is Profitable?
[Profit Ratio]>0
Add Is Profitable? onto the Colour shelf and adjust accordingly

Building the treemap
The simplest way to do this, is to duplicate the sheet you’ve built for the bar chart, then use the Show Me option (top right) and select TreeMap. This will put most of the pills in the right places. The only thing that needs to be shifted is to add Is Profitable? back onto the Colour shelf, and to explicitly set the Sort order against the Sub-Category field. Count Orders also needs adding to the Label shelf, so it can be displayed.

Creating the toggle control
To create the toggle control which will control the switching of the vizzes do the following :
- In the Columns shelf, type in MIN(0)
- Then next to it, type in MIN(1)
- Drag the MIN(1) pill from the Columns shelf and drop onto the MIN(0) axis, when you see the ‘2 green columns’ appear

- Remove Measure Names from the Rows shelf
- Change the mark type to Line
- Create a parameter called Toggle which is an integer containing the values 0 & 1, defaulted to 0. Show this parameter on the view.

- Create a new calculated field, Selected Toggle which just references the Toggle parameter
- Add Selected Toggle to the Columns shelf and change to aggregate to MIN rather than SUM

- Make it dual axes, and synchronise the axes
- Remove Measure Names from the Colour shelf on the All marks card.
- On the Selected Toggle card, change the mark type to Circle, and increase the Size. If you change the Toggle parameter the circle should shift to 1.

- To colour the line based on the position, create a new calculated field
Colour
IF [Toggle]=0 THEN ‘grey’ ELSE ‘green’ END
- Add Colour to the Colour shelf of the All marks card. With the Toggle parameter set to 0, set the colour to grey. Change the parameter to 1, and set the colour to green.
- To set the tooltip create a new calculated field
Toggle Label
IF [Toggle]=0 THEN ‘Bar’ ELSE ‘Treemap’ END
- Add Toggle Label to the Tooltip shelf of the Selected Toggle marks card. Adjust the tooltip text to just show this value.
- Remove the text from the Tooltip of the Measure Names marks card.
- Hide the axes headers and remove the row & column dividers

Controlling what viz to display
With the Toggle parameter set to 0, add the Selected Toggle field to the Filter shelf of the bar chart. and set it to be 0 (or at most 0 depending what filter control displays)

Then set the Toggle parameter to 1, and add the Selected Toggle field to the Filter shelf of the Treemap chart. Set this to be 1 (or at least 1 depending on the filter control displayed)

You should now find that if you switch to the bar chart sheet, nothing is displayed. Change the Toggle parameter back to 0, and the bar chart will now show, but there will be nothing on the treemap chart.
Creating the dashboard and interactivity
Create a dashboard sheet.
Add a vertical layout container
Add the bar chart into the vertical layout container. Set the chart to fit entire view and don’t display the title.
Add the treemap chart beneath the bar chart in the same vertical container. Whilst nothing is actually displaying, still set the chart to fit entire view and again don’t display the title.
The Toggle parameter should have automatically displayed on the right hand side, so test changing the parameter to see how the chart switches.
Add the Toggle Control sheet into the right hand container too.
Add a dashboard action to change the Toggle parameter when the Toggle Control is interacted with. It should take effect on Select of the Toggle chart, affect the Toggle parameter and pass the Measure Values field into the parameter.

Clicking on each end of the Toggle chart should now make the viz change from bar to treemap.
You just now need to tidy up the dashboard – add a title, remove any unrequired objects, set the container background of the right hand panel to grey (you will need to set the background on the toggle sheet to the same shade of grey too).
And hopefully that’s it. My published version is here.
Happy vizzin’! Stay Safe!
Donna