In this week’s #WOW2023 challenge, Erica asked us to show the data for the selected year for a set of EU countries, but within the tooltip, provide additional information as to how the data compared to the same month in the previous year.

A note about the data

For this we needed to use the EU Superstore data set, a copy of which was provided via a link in the challenge page. Since part of validating whether I’ve done the right thing is to have the same numbers, I often tend to use any link to the data provided, rather than use any local references I may have to data sets (ie I have so many instances of Superstore on my local machine due to the number of Tableau instances I have installed). I did find however, that using the data from the link Erica provided, I ended up with a data set spanning 2015-2018 rather than 2016-2019. However I quickly saw that the numbers for each year had just been shifted by a year, so 2018 in Erica’s solution was equivalent to the 2017 data I had.

The viz is also just focussed on a subset of 6 countries. I chose to add a data source filter on Country to restrict the data to just those countries required (right click data source in the data pane -> Add data source filter).

Building out the required data calculations

The data will be controlled by two parameters relating to the Year and the Country

pYear

integer parameter, defaulted to 2017, displayed using the 2017 format (ie no thousand separators). 3 options available in a list : 2016,2017,2018

pCountry

string parameter defaulted to Germany. This is a list parameter and rather than type the values, I chose the option Add values from -> Country

We need to use the pYear parameter to determine the data we want to display, rather than simply apply a quick filter on Order Date, as we need to reference data from across years. Simply filtering by Order Date = 2017 will remove all the data except that for 2017, and so we won’t be able to work out the difference from the previous year. Instead we create

Sales Selected Year

ZN(IF [pYear] = YEAR([Order Date]) THEN [Sales] END)

Wrapping within ZN means the field will return 0 if there is no data.

Format this to € with 0 dp.

We can then also work out

Sales Prior Year

ZN(IF [pYear]-1 = YEAR([Order Date]) THEN [Sales] END)

which then means we can work out

Diff From PY

(SUM([Sales Selected Year]) – SUM([Sales Prior Year])) / SUM([Sales Prior Year])

custom format this to +0.0%;-0.0%;0.0%

This will display a positive change in the format +12.1%, a negative change as -12.1% and no change as 0.0%

Let’s pop all this information out in a tabular view along with the Country and Order Date to sense check the numbers

This gives us the core data to build the basic viz.

Core viz

Add Order Date at the Month date part level (blue pill) to Columns and Sales Selected Year to Rows and Country to Colour. Make sure it’s a line chart (use Show Me) if need be. Adjust the colours accordingly.

Amend the Order Date axis, so the month names are in the abbreviated format (right click on the bottom axis -> format)

Identifying the selected country

We need to change the colours of the lines to only show a coloured line for the selected country. For this we need

Is Selected Country

[Country]=[pCountry]

Add this field to the Detail shelf . Then click on the small icon to the left of the Is Selected Country pill, and select the Colour option.

This will mean that both Country and Is Selected Country are on the Colour shelf, and the colour legend will have changed to a combo of both pills

Move the Is Selected Country pill so it is positioned above the Country pill in the marks card section, and this will swap the order to be True | Country instead. Modify all the colours in the legend that start with False to be ‘grey’. Change the pCountry parameter and check the right colour combinations are displayed.

Change the Sort on the Is Selected Country pill so it is sorted by Data source order descending. This will ensure the coloured line is in front of the grey lines.

Adding the circles on the marks

We need a new field that will just identify the Sales for the selected country and selected year.

Sales Selected Year & Country

IF [Is Selected Country] AND [Year Order Date]=[pYear] THEN [Sales] END

Add this to Rows, then make the chart dual axis and synchronise the axis. Change the mark type of the Sales Selected Year & Country marks card to a circle, and adjust the Size to suit.

Finalising the line chart

Add Diff From PY onto the Tooltip shelf of the All marks card.

Create a new field

Month Order Date

DATENAME(‘month’, [Order Date])

and also add this to the Tooltip shelf. Adjust the tooltip to match the required formatting.

Hide the right hand axis (uncheck Show header).

Edit the left hand axis and delete the title, fix the axis from 0 to 50,000 and verify the axis ticks are displaying every 10,000 units.

Hide the 60 nulls indicator (right click -> hide indicator).

Remove the row & column dividers. Hide the Order Date column heading (right click -> hide field labels for columns)

Create the Country name for the heading

On a new sheet

• Add Country to Rows
• Add Is Selected Country to Filters and set to True
• Add Country to Colour and then also add Is Selected Country to colour in the way described above.
• Add Country to Label
• Adjust the formatting of the Text so it is much larger font.

Hide the Country column (uncheck show header), and remove all row/column dividers. Ensure the tooltip won’t display.

Putting it all together

I used a horizontal container placed above the core viz. In the horizontal container I added blank objects, a text object, and the Country label sheet. I adjusted the size of the objects to leave space to then float the parameters. The parameters were resized to around 25 pixels so they just displayed the arrow part of the parameter. All this was a little bit of trial and error, and I did find that after publishing to Tableau Public, I had to adjust this section again using web edit.

My published viz is here.

Happy vizzin’!

Donna