Lorna’s final #WOW challenge of 2021 was a parameter action based challenge, where the line chart at the bottom reflects the choices made by interacting with the bar chart at the top.

When I saw this was a drill down challenge, I immediately thought of previous similar challenges, so built based on the techniques I’d applied before (see this blog). This involved a parameter to capture the ‘level’ and a ‘drill down’ calculated field to pass through into the parameter on click. The solution I built (here) worked fine on Desktop (see tweet), but when published to Tableau Public, failed to display the Sub-Categories on selection :-(. I don’t know why. Having checked Lorna’s solution after, I realised I’d over complicated my solution, and had no need for the ‘level’ or ‘drill down’ fields. So I rebuilt to see if that fixed my issue with Tableau Public, and it did. So that is the solution I’ll blog about.

As stated in the requirements, I used Superstore v2021.3, but my numbers didn’t match Lorna’s. This isn’t an issue, but explains why my solution looks different from Lorna’s if you’re comparing. Having spoken to Lorna, we assume she didn’t use that version after all, but can’t recall what she may have used instead.

Anyway, onto the build.

First up, we need to define 2 parameters

pSelectedCategory

A string parameter defaulted to ‘nothing’ / empty string. This will be used to store the Category that the user clicks on in the bar chart.

pSelectedSubCat

Another string parameter defaulted to ‘nothing’. This will be used to store the Sub-Category that the clicks on in the bar chart.

We can’t just display the Sub-Category field in the bar chart, as it should only display a value ‘on click’. So we need a calculated field to store the value that needs to be displayed in the 2nd column of the bar chart – ie ‘nothing’ or the Sub-Category.

Sub Cat Display

IF [pSelectedCategory]=[Category] THEN [Sub-Category]
ELSE ”
END

If there’s a value in the pSelectedCategory, then display the Sub-Categorys, otherwise display a blank.

Build out the bar chart by adding Category and Sub Cat Display to Rows, and Sales to Columns. Order both Category & Sub Cat Display descending. Add Category to Colour and adjust. Label the marks, align the headings to the left, remove gridlines, hide the axis and column headings. If you show the parameter you can ‘type in’ a Category and see how the view looks.

On a new sheet, build out the line chart by adding Order Date to Columns set to the continuous (green pill) month format, and Sales on Rows. Label the max & min marks and remove all gridlines.

Create a new field which will be used to colour the line

Colour : Line

[pSelectedCategory]

Add this to the Colour shelf.

Show the pSelectedCategory parameter on the sheet and as you type in each Category value the colour legend will change. Adjust the colour for each value you type in, and for the empty value.

The line chart needs to change based on the selections made by the user, ie the values set in the parameters. A calculated field is required

FILTER

([pSelectedCategory]=”)
OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=”)
OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=[Sub-Category])

This is a boolean field. Add to the Filter shelf and set to True.

Finally, just to make things a bit more complete (and help me check the line chart was matching the selections), I created some additional fields to present on the Tooltip

TOOLTIP : Category

IF [pSelectedCategory]=” THEN ‘All’ ELSE [pSelectedCategory] END

TOOLTIP: SubCat

IF [pSelectedSubCat]=” THEN ‘All’ ELSE [pSelectedSubCat] END

I added these to the Tooltip shelf and amended the text as below

The final step is to place the two charts on a dashboard and then create 2 parameter actions, one which passes Category into the pSelectedCategory field on select. Clearing the selection needed to reset the value to ‘blank’.

The other parameter action needs to do similar, but set the pSelectedSubCat parameter from the Sub Cat Display field.

My revised published viz is here.

Happy vizzin’!

Donna