Calculating Year-on-Year Percentage Change

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

Advertisement

Selected Sub-Category Influence

Ann Jackson made a special guest appearance this week, setting this challenge to introduce the newly released 2022.3 feature of dynamic zone visibility. As a consequence, a pre-requisite to completing this challenge is to install v2022.3 🙂

I used 6 sheets to build my solution, and I’ll step through each one, and then what’s required to put it all together.

  • Building the Sub-Category Picker
  • Building the Sub-Category Counter
  • Building the Bar Chart
  • Building the Line Chart
  • Building the Viz in Tooltip
  • Building the Dot Plot
  • Hiding & Showing the Charts
  • Adding & Removing Sub-Categories

Building the Sub-Category Picker

On a new sheet, add Sub-Category to Rows and change the mark type to circle. Right click on the Sub-Category field in the Data pane, and Create -> Set. Select the entries from Accessories down to Copiers. This will create a new field in the data pane called Sub-Category Set. Add this field to the Colour shelf, and adjust colours according to whether the values are In or Out of the set.

Add a grey border around the circles (via the Colour shelf) and increase the size a bit. Format the text of the sub-categories so its larger and right aligned. Remove all row/column dividers and hide field labels for rows to remove the Sub-Category column title (right-click on the column title). Adjust the Tooltip. Add a title to the sheet and then name the sheet Sub Cat Picker or similar.

Building the Sub-Category Counter

Create a new calculated field

Count Sub Cats Selected

COUNTD(IF [Sub-Category Set] THEN [Sub-Category] END)

If the Sub-Category is in the set, the return the Sub-Category and count the number of distinct entries.

Then create

Count Total Sub Cats

COUNTD([Sub-Category])

This just counts them all.

On a new sheet, add both fields to the Text shelf, and adjust the text accordingly.

Remove the tooltip so it doesn’t display. Name the sheet Set Count Label or similar.

Building the Bar Chart

Create a new field

Profit Ratio

SUM([Profit])/SUM([Sales])

and format to a % with 0 dp.

On a new sheet, add Profit Ratio to Rows and Sub-Category Set to Columns and also to Colour.

Right click on the text ‘In’ either on the colour legend or at the bottom of the bar, and Edit Alias. Change the text to SELECTED. Do the same thing for the text ‘Out’ and change to OTHER.

Add a row grand total (Analysis menu -> Totals -> Show Row Grand Totals). Adjust the colour of the grand total bar. Right click on the text ‘Grand Total’ and select Format. In the pane on the left hand side, change the Grand Total Label to read ALL PRODUCTS.

Create a calculated field

Overall PR

{SUM([Profit])}/{SUM([Sales])}

The { } make this a FIXED Level of Detail (LoD) calculation, so calculates over the complete data set.

Then create

PR Difference from Overall

[Profit Ratio]-SUM([Overall PR])

and format this to a custom number format of +0.0%;-0.0%;

Adding the second semi-colon implies there is a format for +ve numbers, -ve numbers and zero. In this instance we want zero difference to be displayed as blank.

Add both these fields the the Label shelf and adjust the font/layout accordingly, and match mark colour. Adjust the tooltip too.

Remove the profit ratio axis, remove all gridlines and row/column dividers. Add an axis ruler to the columns. Adjust the colour/size of the column labels. Hide the In/Out of Sub-Category Set column label (hide field label for columns). Add a title to the sheet and name the sheet Bar Chart or similar.

Building the Line Chart

On a new sheet, and Order Date to Columns and set to be a continuous (green) pill at the Quarter-Year level. Add Profit Ratio to Rows and Sub-Category Set to Colour.

Create a new calculated field

PR Per Quarter

{FIXED DATETRUNC(‘quarter’, [Order Date]):SUM([Profit])} /
{FIXED DATETRUNC(‘quarter’, [Order Date]):SUM([Sales])}

and format this to % with 0dp.

Add this to Rows next to Profit Ratio.

Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card, and remove the In/Out Sub-Category Set pill from the colour shelf of the PR Per Quarter marks card. Manually adjust the colour of this line to the appropriate shade.

On the All marks card, click the Label shelf, and check the Show mark labels option and select line ends. Adjust the font of the labels to be smaller, bold and to match mark colour.

Right click on the PR Per Quarter axis on the right hand side and select move marks to back, the right click again and uncheck Show Header to hide that axis.

Right click on the bottom axis, and Edit Axis and remove the axis title. Edit the left hand axis and amend the title so its capitalised.

Format the font of both axis, so the text is smaller, and then remove all row/column dividers, and all gridlines and zero lines. Add axis rulers for both the rows and columns.

Then add a sheet title and subtitle and name the sheet Line Chart or similar. I use this site to get the circular symbols used in the subtitle.

Building the Viz in Tooltip

The line chart shows another chart on hover.

On a new sheet, add Order Date as a blue discrete pill set to the Quarter-Year level, to Rows then add Sub-Category Set to Rows too. On the Columns shelf, double click and manually type in MIN(1). Add Sub-Category Set to Colour, then edit the MIN(1) axis to fix it from 0 to 1.

Add subtotals (Analysis menu -> Totals -> Add all subtotals). This will add a Total row to each section. Manually adjust the colour of the Total bar if need be via the colour legend.

Right click on the text ‘Total’ in the chart, and format. Amend the Total label to read ‘ALL PRODUCTS’ instead.

Add Profit Ratio to the Label and ensure the font matches mark colour. You may need to adjust the font size and boldness, and expand the row height a bit to see the text.

Hide the Order Date column, adjust the font of the Sub-Category Set column to be darker/bolder and right aligned, and adjust the column width so all the text is displayed.

Hide the MIN(1) axis, remove all row/column dividers and hide the Sub-Category Set column label. Then set the sheet to Entire View, and name the sheet VIT or similar.

Return the Line Chart sheet, and on the Tooltip shelf of the All marks card, adjust the tooltip to display the Order Date and insert a reference to the VIT sheet via the Insert -> Sheets -> VIT option

Adjust the height and width to suit.

Building the Dot Plot

On a new sheet, add Sub-Category Set to Rows and Profit Ratio to Columns. Change the mark type to circle. Then add Product ID to the Detail shelf and Sub-Category Set to Colour. Add column grand totals and adjust the colour of the grand total if need be. Format the ‘Grand Total’ text so it reads ALL PRODUCTS.

To get a clearer idea of how many products there are, we are going to randomly spread the dots across a vertical y-axis. For this we create

Jitter

RANDOM()

This just returns a number between 0 and 1.

Add this field to Rows and change it to be a Dimension. Adjust the opacity of the Colour to 50%.

Hide the Jitter axis. Make the header column wider, so the text doesn’t wrap, and adjust the text to b bigger and bolder and right aligned. Hide the Sub-Category Set column heading. Adjust the size and title of the Profit Ratio axis. Remove all gridlines and column dividers.

Right click on the Profit Ratio axis and add a reference line, which is set per pane to the the Total of the Profit Ratio. Use the Value as label and set the line to be a dotted black line at 100% opacity.

Add Product Name to the Tooltip and adjust accordingly. Add a title and name the sheet Dot Plot or similar.

Hiding & Showing the Charts

We’re going to control which sheet displays by use of a parameter, so I created

pChartSelector

an integer list from 1-3 which are mapped to the 3 display values

Then create a dashboard sheet and using layout containers build out the dashboard. I used a horizontal container in the centre of my dashboard. Within that I used a vertical container to house the Sub-Category Picker and the Sub-Category Counter. Then the 3 charts (bar, line and dot plot) were arranged next to that. I fixed the width of the vertical container with the picker and counter. The pChartSelector parameter is then added at the top right. I made use of both inner and outer padding and background colours of pale grey and white to get the look as reqiured.

To make the hide/show functionality, I created the following fields

Show bar

[pChartSelector]=1

Show line

[pChartSelector]=2

Show dot plot

[pChartSelector]=3

I added Show bar to the Detail shelf of the bar chart sheet, Show line to the Detail shelf of the line chart sheet and Show dot plot to the Detail shelf of the dot plot sheet.

Then back on the dashboard, I selected the bar chart sheet (so it’s surrounded by a dark grey border), and on the Layout tab on the left hand side, I checked the Control visibility using value checkbox and selected the Show bar field

I then repeated this process, this time selecting the line chart sheet, and when I checked the Control visibility checkbox, I selected the show line field instead. this made the line chart disappear, since my parameter was set to ‘Compared to the Total’ which was equivalent to the parameter = 1 and not 2. Changing the parameter to ‘Over Time’ and my line chart showed and the bar disappeared.

Repeat the process again for the dot plot, selecting the show dot plot field instead. Now only 1 chart should display at a time.

Adding & Removing Sub-Categories

The final step is to add the interactivity to allow selection and removal of a sub-category when clicking on the circles of the Sub-Category Picker sheet.

First, you need to add a dashboard action which changes set values

Add Sub-Categories

Uses the Sub Cat Picker sheet as source and on Select targets the Sub-Category Set by Adding values to the set. The values are retained when the selection is cleared.

Then add another dashboard action to change set values. This one is called

REMOVE

Uses the Sub Cat Picker sheet as source and via the Menu targets the Sub-Category Set by Removing values from the set. The values are retained when the selection is cleared.

The title of REMOVE is what is then displayed in the text of the tooltip when a circle that has been added is the clicked again.

Phew!

Quite a lengthy post this week, but there’s a lot going on. My published viz is here.

Happy vizzin’!

Donna

Can you create a common starting point?

Kyle set the challenge this week, revisiting his favourite topic – baseball. The aim was to build what I’ve often referred to as a ‘rocket chart’, as it charts progress from a single ‘launch’ date/point. However having had a quick google, I can’t see any other reference to this being used for this type of chart….no idea where it came from <shrug>.

Anyway, the requirement was to compare the profiles of when home runs (HRs) had been accumulated over the course of a player’s career, restricting to just the players who are in the all-time top 10. These players hadn’t necessarily played during the same years or even decades, so there was a need to baseline the information according to the days since they started. Kyle also threw in the requirement that this was to be an LoD based challenge only, with no use of table calculations.

Build the basic chart

As mentioned above, we first need to ascertain how many days have passed between when the player hit their first home run, and the subsequent dates. We use a FIXED LoD to work out the minimum date per player

Min Date Per Player

DATE({FIXED [Player] : MIN([Date])})

And with that we can the work out the number of days that have passed

Days Since Min Date

DATEDIFF(‘day’,[Min Date Per Player], [Date])

And with this, we can quickly build out the main crux of the chart. Add Days Since in Date to Columns, and change to be a continuous dimension. Add Career HR to Rows and amend the aggregation to use AVG rather than SUM, as I found there looked to be duplicate records for some dates for the same player. Add Player to Detail.

Colouring the lines

Kyle provided a custom colour palette to use based on the team colours of the player. I updated by preferences.tps file with this data, and closed and reopened Tableau Desktop to ensure it picked it up. For more information on working with custom colour palettes see this Tableau help article.

Along with the player colours, we also need to identify which player has been selected.

For that we need a parameter to define who the selected player is

pPlayer

string parameter using a List where the values are added from the Player dimension. this causes the default to be set to Albert Pujols.

Show the parameter on the display.

We can now create

Is Selected Player?

[Player] = [pPlayer]

which will return a boolen true/false.

Kyle stated that we should be able to set the colours without having to manually click against every Player|T or F combination.

Now I managed this when I first built my solution, but in writing this blog and trying to replicate the steps, I’m not getting the same behaviour. So I have managed to come up with another way. The gif below hopefully demonstrates, but I’ll list the steps too.

Move the Player pill from Detail onto Colour

Edit the Is Selected Player field to just return True (use // to just comment out the original calculation)

Add Is Selected Player to the Detail shelf, then click the detail icon to the left of the pill and change it to Colour. This is a way to get multiple pills on the Colour shelf. Dragging will just replace the field being used for colour.

The colour legend dialog box should display a list of <Player>, True entries (if the legend isn’t displaying go to Worksheet > Show Cards > Reset Cards – you may then have to add the parameter to the display again).

Edit the colour legend, select the MLB HR Top 10 colour palette and click Assign Palette. This will automatically assign the relevant colour to each entry, since they were added based on alphabetical order.

Re-edit the Is Selected Player field, so it is back to [Player] = [pPlayer].

The entries in the colour legend will now only list one <Player>, True entry and the rest all false.

Edit the colour legend, and multi-select (ctrl-click) all the False entries, and then select the lightest shade of grey from the Seattle Grays palette. This should give you the desired display.

Select Alan Rodriguez from the parameter control. Both Albert, False & Alan, True should now be coloured. Edit the colour legend again and manually set the Albert Pujols, False entry to the same grey shade.

Now if you select any other player, only 1 line should be coloured, and it should be coloured to the corresponding player’s colour.

Setting the Tooltip

Add Season HR to Tooltip and change the aggregation to AVG. Add Date to Tooltip too and set it to be an Attribute. Amend the tooltip accordingly.

Adding the highest season HR indicator

Firstly we need to determine what the maximum Season HR value is per player

Max Season HR Per Player

{FIXED [Player]: MAX([Season HR])}

With this, we then want to get the corresponding Career HR value for that same time.

Career HR | Max Season HR

IF [Season HR] = [Max Season HR Per Player] THEN [Career HR] END

Add this field to Rows and change the aggregation to Avg.

Set to Dual Axis, Synchronise Axis and then set the mark type to Circle. Adjust the size of the circle mark slightly if need be.

Labelling the lines

On the Line marks card, add Player and Career HR to the Label shelf. Adjust the aggregation of Career HR to Avg. Edit the label, so only line ends are labelled. Adjust the font size to something quite small, and set the colour to Match Mark Colour.

Finally remove all gridlines, row & column dividers, and hide the axis. Title the chart.

When added to a dashboard, I then used a floating text object for the introductory text and positioned the parameter as a floating object underneath the text.

My published viz is here.

Happy vizzin’!

Donna

Can you make a bullet chart?

Kyle returned this week to set this challenge to create a bullet chart which compared Win % for baseball teams in a year (bar) to the previous year (line), along with call out indicators (red circles) if the difference was greater than a user defined threshold. To add an extra ‘dimension’ to the challenge, a viz in tooltip showing the historical profile for the selected team was also required.

Building the basic bar chart

We’re going to use a parameter to define which year we want to analyse, as we can’t just filter by the Year as we need information about multiple years

pYear

integer field defaulted to 2018, formatted so the thousands separator does not display, and populated by Add values from the existing Year field

With this, we can create the following fields

Wins – CY

IF Year = [pYear] THEN [Wins] END

Wins – PY

IF Year = [pYear]-1 THEN [Wins] END

Games – CY

IF Year = [pYear] THEN [Games] END

Games – PY

IF Year = [pYear]-1 THEN [Games] END

and subsequently

Wins per Game – CY

SUM([Wins – CY]) / SUM([Games – CY])

and

Wins per Game – PY

SUM([Wins – PY]) / SUM([Games – PY])

Both these 2 fields are formatted to a custom format of ,##.000;-#,##.000 (this basically strips off the leading 0, so rather than 0.595 it’ll display as .595.

Now we have all these, we can build a dual axis chart.

Add League and Team to Rows and Wins per Game – CY to Columns. Sort by Wins per Game – CY descending. Display the pYear parameter.

Add Wins per Game – PY to Columns, make dual axis and synchronise the axis. Change the mark type of the CY card to a bar and the mark type of the PY card to Gantt. Remove Measure Names from the Colour shelf of both cards, and change the colour of the gantt bar to black.

Show mark labels for the bar mark and align left (expand the width of each row if need be).

Colouring the bars

The colour of the bars is based on whether the CY value is greater or less than the PY value. So we need to find the difference

CY-PY DIfference

[Wins per Game – CY]-[Wins per Game – PY ]

and then work out if the difference is positive or not

CY-PY Difference is +ve

[CY-PY Difference]>0

Add this to the Colour shelf of the bar marks card and adjust accordingly.

Adding the call out indicator

The red circle is based on whether the difference is above a threshold set by the user . A parameter is required for this

pChange

float field set to 0.1 by default; the values should range from 0.05 to 0.2 in 0.05 intervals

Show this parameter on the sheet.

We then can create

CY-PY Diff Greater than Change

IF ABS([CY-PY Difference])>[pChange] THEN ‘●’ ELSE ” END

The ABS function is used, as we want to show the indicator regardless as to whether the difference is a +ve or -ve difference. The ● image I get from copying from https://jrgraphix.net/r/Unicode/25A0-25FF. I use this page a lot, so keep it bookmarked.

Add this field to Rows, and then format the field so it is red and the font size is bigger (I used 12pt)

Now the viz just needs to be tidied up by

  • Hide field labels for rows
  • Rotate label of the League field
  • Format the font of the Team text
  • Reduce the width of the first three columns
  • Remove gridlines and zero lines
  • Adjust the row divider to be a dotted line at the 2nd level
  • Remove column dividers
  • Add an axis ruler to Rows
  • Uncheck Show Header on the axis to hide them
  • Tidy up the tooltip on the gantt mark type.

Building the Viz in Tooltip line chart

For this we need the Win % for every year, so we need

Wins per Game

SUM([Wins]) / SUM ([Games])

Add Team to Rows, Year to Columns (change to be a continuous, green pill) and Wins per Game to Rows

Add pYear to the Detail shelf, then add a Reference line to the Year axis to display the value of pYear as as dotted line

Right click on the reference line > format and adjust the alignment of the value displayed to be top centre.

Show mark labels and set to just show the min & max values for each line.

Colouring the lines

The colour of the line is based on whether the current year’s value is bigger or smaller than the previous year (ie the colour of the line matches the bar).

However, we can’t just use the fields we’ve already built, as because we have Year in the view, the data only exists against the appropriate year, so the difference can’t be computed. You can see this better if you build out the table below…

We need to ‘spread’ these values across every year for each team.

Wins per Game – CY Win Max

WINDOW_MAX(SUM([Wins – CY]) / SUM([Games – CY]))

Format this as you did above.

Add this to the view and amend the table calculation so it is computing by the Year field only. You should see that the value of this field matches the Wins per Game – CY value, but the same value is listed against every year now, rather than just the one selected.

Similarly, create

Wins per Game – PY Win Max

WINDOW_MAX(SUM([Wins – PY]) / SUM([Games – PY]))

format, and add this to the view too and adjust the table calculation as you did above.

So now we have this, we can work out whether the difference between these two fields is +ve or not

CY-PY Difference is +ve Win Max

[Wins per Game – CY Win Max]- [Wins per Game – PY Win Max]>0

Add this to the Colour shelf of the line chart. Verfify the table calculation is set to compute by Year only for both nested calculations, and then adjust the colours to match.

Finally tidy up the viz, to remove all headings, axis, gridlines, row/column dividers etc Set the background colour of the sheet to a light grey, then finally set the fit to be Entire View.

Adding the Viz in Tooltip

Go back the bar chart, and on the tooltip of the bar mark, adjust the initial text, then insert the line chart sheet via the Insert > Sheets > Sheetname option.

I adjusted the maxwidth & maxheight properties of the inserted text to be 350px each

If you hover over the bar chart now, you should get a filtered view of the line chart.

Final step is to put all this on a dashboard. My published viz is here.

Happy vizzin’!

Donna

Filter Challenge : How well do you know Tableau’s Order of Operations?

It was Erica Hughes’ turn to set the #WOW challenge this week. This ended up being a bit of a journey for me, because of one requirement – to use the max date of the dataset.

I typically use a FIXED LOD to determine the max date, which I did initially in this case, and after building a solution, tripped up when it came to adding some of the filters ‘to context’. The context filter meant my Max Date was changing depending on the filter and subsequently I wasn’t getting the right results for the ‘days since last purchase’.

So I ended up having to put my thinking cap back on, and after a lot of trial and error and reading on the internet (including this article by the Flerlage Twins) , I finally managed to get a solution that involved both LOD calculations (including a rarely used INCLUDE LOD) and Table calculations, and no context filters at all. This was a pretty complex solution. The table of data had to include the Order Date on the Detail shelf, resulting in multiple rows per customer showing, which meant I had to then use an INDEX()=1 filter to only show 1 row for each customer. I then used an additional INDEX() against each customer, so I could filter to show only the customers in position 1-10. My solution to this is published here.

After publishing, I checked Erica’s solution and found how she’d handled the max date requirement. It used a concept I haven’t had to use so far, so I decided to rebuild a less complex solution, which is what I will now blog about.

Capturing the max date of data set which isn’t affected by context filters

The usual way to define the maximum date in the data set is to create a FIXED LOD calculation, and this is still required

Max Date

{FIXED :MAX([Order Date])}

This returns 30 Dec 2021.

The problem is, once a context filter is applied to a sheet, the value of this field can change. For example, if Region is applied as a context filter and set to ‘South’, then what Tableau will do, based on the ‘order of operations’, is first filter all the data to just those records where Region=South. It will then work out the max date of these filtered records, and if there are no orders on 30 Dec 2021 for the South Region, then the value of the Max Date field will differ. This is because context filters get applied before FIXED LOD calculations. We need a way to ensure we can retain the 30 Dec 2021 without hardcoding it.

The solution is to use a parameter.

pMaxDate

A date parameter which is set to use the value of the Max Date field when the workbook is opened.

This is quite a sneaky but clever way to retain this, which is why I’m reworking my solution to use it, so I have something for future reference myself. When the workbook opens, the pMaxDate parameter will get set to 30 Dec 2021 and won’t ever change, whereas the Max Date field will change if context filters are used.

Building the Table

It may seem a bit odd, but I’m going to start with the table of data that’s displayed, as this requires the most calculations, and the most validation.

On a new sheet, add Customer Name, Customer ID to Rows and Sales onto Text. Order by Sales desc. Format Sales to $ with 0dp.

We need to determine the latest order date for each customer in order to work out other information.

Latest Order Date

{FIXED [Customer ID] : MAX([Order Date])}

This finds the maximum Order Date for each Customer.

And with this, we can now work out

Days Since Last Purchase

DATEDIFF(‘day’, [Latest Order Date], [pMaxDate]

Note this is comparing the Latest Order Date to the pMaxDate parameter instead of Max Date.

Format this to custom number, with 0 dp and a suffix of ‘ days’.

Add Latest Order Date to Rows (exact date, discrete) and Days Since Last Purchase into the table.

Now we need to work out

Latest Order Amount

{FIXED [Customer ID]: SUM(IF [Order Date]=[Latest Order Date] THEN [Sales] END)}

For each customer, if the Order Date is the same as the Latest Order Date, then retrieve the Sales value.

Format this to $ with 0 dp and add to the view.

Now add Customer ID to the Filter shelf and set to filter by the Top 10 based on Sales.

To validate the behaviour of the calculations add Region to the Filter shelf and select ‘South’. You’ll notice only 8 rows are shown, and not 10.

This is because the data has been filtered based on the top 10 customers with the most sales first, and then restricted those top 10, to those with sales in the South. Only 8 of the top 10 customers have sales in the South, hence the 8 rows.

To resolve this, we need to add Region to Context (right click on the filter and Add to Context . This has the effect of filtering all the data by Region = South first, and then displaying the top 10 customers by Sales. We now have 10 rows displayed.

Add State to the Filters shelf, and add that to context too. Test the table of results by selecting different combinations of regions and/or states and comparing to the results on Erica’s solution to verify all the calculations are behaving as expected.

Now we’re happy the table is displaying the data as expected, we can format it and make it ready for the dashboard :

  • Remove Latest Order Date & Customer ID
  • Set the Row Banding
  • Set the Row Dividers to Level 0, so only row lines appear at top and bottom
  • Remove Column Dividers
  • Format all text (row/column headings & text data) to be 8pt.
  • Alias Sales to be Total Sales (right click on the Sales title in the columns and Edit Alias)
  • Remove Region & State from the Filter shelf. These will get re-added later.

Building the Map

On a new sheet, double click State to load a map (you may need to set your location to United States : Map menu -> Edit Locations).

Change mark type to a Filled Map, add Region to Colour and adjust colours.

Remove all map layers (Map menu -> Map Layers and uncheck all options listed on left hand side). Change border on Colour shelf to white.

Remove row & column dividers, and remove all map options (Map menu -> Map Options and uncheck all options).

Drag a new instance of State onto the canvas and Add a Marks Layer. This will create a 2nd marks card on the left. Change the colour of the circles to black. Add Sales to the Size shelf and adjust. Adjust the tooltip of both marks card (you’ll need to add Sales to the Tooltip on the map marks card).

Building the Legend

On a new sheet add Region to Columns and type in MIN(1) into the Columns shelf too. Add Region to the Label shelf, and the Colour shelf. Adjust the height of the rows, so you can see the text.

Edit the axis so it is fixed from 0 to 1. Then format the Label so the font is larger and centred.

Hide the axis and the Region (uncheck show header). Remove row & column dividers and any gridlines. Adjust the border on the Colour shelf to be white. Set the tooltip not to show.

Building the Bar Chart

On a new sheet, add Sub-Category to Rows and Sales to Columns. Order by Sales desc. Add Sub-Category to the Filter shelf and set to show Top 10 by Sales. Reduce the Size of the bars, and align the row labels to be left aligned, and the font to be 8.

Format the axis, so the values are displayed in $K.

Hide the row label, and adjust Tooltip. You may need to create an additional calculated field based on Sales to add to the Tooltip which you can then format to $ with 0dp.

Adding the interactivity & context filters

Add the sheets onto a dashboard. You’ll need to use a mixture of vertical & horizontal layout containers, inner and outer padding and background colours to get the required layout.

Add a dashboard filter action which runs on Select when the Legend sheet is selected, which affects all other sheets and shows all values when unselected.

Add another filter dashboard action, which this time runs on Select when the Map sheet is selected, which affects all sheets except the Legend sheet, and also shows all values when unselected.

Now click on one of the regions in the legend, then click on a state in the map. This has the effect of adding filters to the shelves on the other sheets. Navigate to the Table sheet, and add the 2 ‘Action’ filters to context

Do the same for the Bar char sheet.

And you should now have a completed viz. My published version based on this solution is here.

Happy vizzin’!

Donna

Let’s analyse wildlife strikes

Sean Miller began the start of #WOW2022 with this challenge where the focus was on layout, interactivity and maps.

  • Building the map
  • Building the bar
  • Building the area chart
  • The Unknown indicator
  • Adding interactivity

Building the map

I’m starting with this as in order to build the map, I found after a bit of trial and error I needed to build a data model which related the wildlife strike data source provided by Sean with the spatial file data source provided via the link to the community post. I downloaded all the files, but found the link to the us_ak_hi_territories_shift_conformal_faux_WM.hyper.zip was the file I needed once unzipped (the middle file to download from Sarah’s post on 19 July 2020 (see below)

Once I unzipped the downloaded file I copied the us_ak_hi_territories_shift_conformal_faux_WM.hyper file to my usual data sources repository on my laptop.

I then connected to Tableau and built a data model by first connecting to the wildlife strikes csv file, then adding a relationship to us_ak_hi_territories_shift_conformal_faux_WM on State Name = Name

To make things clearer, I created a fields to store the number of incidents

Wildlife Incidents

COUNT([2022_01_05_WW01_FAA Wildlife strikes (1990-2021).csv])

This is simply referencing the ‘count’ field that is automatically generated that is related to the wildlife strike data source.

I then built the map by

  • add Geometry and Name to Detail
  • add Wildlife Incidents to Colour

This should have created the below

Remove all the background imagery via the Map > Map Layers menu – uncheck all the options from the left hand pane.

Add Name to the Filter shelf and exclude American Samoa, Guam, Northen Marianas, Puerto Rico and the Virgin Islands. This will remove the cluster of shapes to the right (I’m not sure if this is the expected method or not..).

Change the colour palette to use the red-gold colour range.

Finally amend the Tooltip accordingly and also remove the row and column dividers.

Building the bar

The bar chart displays the top 10 incidents by species type, with the rest all grouped under ‘other’, and displayed at the bottom. We need to create a set for this. Right click on Species Type and Create > Set. Create a set based on the top 10 of the count of the wildlife incidents data source.

Species Type Set

We then need a field to display the info in the bar

Species Type to Display

IF [Species Type Set] THEN [Species Type] ELSE ‘Other’ END

ie if the Species Type is in the Species Type Set then display the Species Type, otherwise display Other.

Add Species Type Set and Species Type To Display to Rows and Wildlife Incidents to Columns and sort descending (just click the sort descending button in the toolbar)

Add Species Type Set to the Colour shelf and adjust accordingly. Remove the column and row dividers and the row gridlines. Adjust the Tooltip.

The final step we need is to make the title dynamic and display a state name if filtered.

Firstly we will need a parameter to capture the selected state

pSelectedState

A string parameter defaulted to <empty string>

We will use a parameter action to populate this parameter later. We need an additional field to use in the chart title

Title: Selected State

IF [pSelectedState] <> ” THEN ‘in ‘ + [pSelectedState] ELSE ” END

Add this field onto the Detail shelf, then adjust the chart title

Building the area chart

Add Incident Year to Columns and Wildlife Incidents to Rows and change to mark type = Area. Adjust colour accordingly, and remove the column gridlines. Adjust the Tooltip.

Again the chart title needs to be dynamic based on state name and the species type selected, so we’ll need another parameter

pSelectedSpecies

string parameter defaulted to <empty string>

Add Title: Selected State to Detail and adjust the title as below

The Unknown indicator

On a new sheet double click in the space below the marks card to create a ‘type in’ pill

Enter the text ‘Unknown Location’ (including the single quotes) and the add this pill onto the Text shelf.

Change the mark type to square and adjust the Size to the maximum.

Add Wildlife Incidents to the Tooltip shelf. Then add Name to the Filter shelf and filter to only show the Null values. Adjust the colour and the Tooltip.

Adding interactivity

Create a dashboard and use layout containers to position the charts in the relevant places. The colour legend and the ‘unknown’ indicator will need to be ‘floated’ into position.

We’re going to need 4 dashboard actions; one to set the selected State, one to set the selected Species Type, one to filter the bar and area chart based on the the selected state, and one to filter the map and area chart based on the selected species.

Select Species

this is a parameter action to set the pSelectedSpecies paramater on selection of the bar chart, using the value in the Species Type To Display. The parameter should be reset to <empty string> when unclicked.

Select State

similar to above, but runs on selection of the map chart, and passes the Name field into the pSelectedState parameter.

Filter by State

This is a filter action that runs on selection of a state in the map chart. It affects all other charts on the dashboard except the unknown sheet. It should only filter on the Name field and not All fields.

Filter by Species

Another filter action, that runs on selection of the bar chart. This one does impact all the other charts on the dashboard, but again only filters based on the Species Type to Display field rather than all fields.

Hopefully, with all this, you should have a working solution. My published viz is here.

Happy vizzin’!

Donna

Can you make Spine Charts?

Sean Miller provided the challenge for this week, resurrecting a challenge originally set by Emma Whyte in 2017. Revisiting these older challenges is great fun, as often newer product features provide a different way of solving. For me, I also like the fact I know I’ve already solved it once, and have my own work to reference if I get stuck – ha ha!

Sean hinted that this wasn’t a challenge to ‘overthink’ – no table calcs or LoDs required. You need to be able to display average responses per question per university alongside the overall average response for the question. Simply filtering by university isn’t going to cut it, as the quick filter will immediately eliminate all the data that isn’t associated to the selected university, which means you can’t compute an ‘overall average’ without using LoDs.

The key to this challenge is to use a parameter to drive the University selection. Create this by right clicking on the University field -> Create > Parameter. This will create the parameter dialog box, prepopulated with all the university values. Set the default to University of Liverpool.

pUniversity

With this, we can now create calculated fields to store the values associated to the selected university only.

Sample Size

AVG(IF [University]=[pUniversity ]THEN [Sample Size] END)

Note 1, there is already field called Sample Size in the data set. The actual name of this field is <space>Sample Size<space> which Tableau sees as a different name. In hindsight I should have just renamed the original field, so I could then have ‘Sample Size‘. Be mindful of this when I refer to the field later; unless I call it out, I’m referring to my version.

Note 2, I chose to apply the AVG aggregation within the calc rather than changing the default aggregation on the pill when added to the view. There was a reason I did this, but I can’t recall what it was, and think it wasn’t necessary in the end….

University Avg

AVG(IF [University] = [pUniversity ] THEN [% Agree] END)

formatted to percentage, 1 dp

We can also then define the overall average for comparison

Overall Avg

AVG([% Agree])

formatted to percentage, 1 dp

and with that can calculate the variance between the two

Delta

([University Avg]) – ([Overall Avg])

This is custom formatted to 0.00%▲;0.00%▼ (I use this site to get the arrow characters)

And then we need a field to define how the mark needs to be coloured

Colour

[Delta]>=0

We can put these all out in a view

  • Question Number (which I renamed to No) on Rows
  • Question Text on Rows
  • Sample Size on Rows (set to be a discrete blue pill)
  • University Avg on Rows (set to be discrete)
  • Overall Avg on Rows (set to be discrete)
  • Delta on Rows (set to be discrete)
  • University Avg on Columns (continuous green pill)
  • Change Mark Type to Circle
  • Add Colour to the Colour shelf and adjust

Now we need to work on adding the various lines and bands on the chart. This is all managed by adding reference lines (or bands).

Drag % Agree to the Detail shelf, and change to be AVG.

The drag the same field % Agree on Detail again, this time change to MIN. Repeat again, and change to MAX.

Right click on the University Avg axis > Add Reference Line. Create a line, per pane using the AVG(% Agree) field.

Add another reference line (by right clicking on the axis again). This time create a band that starts at MIN(% Agree) and ends at MAX(% Agree). Set the Fill colour to light grey.

We need to create some new fields for the quartile values.

Lower Quartile

PERCENTILE([% Agree],0.25)

Upper Quartile

PERCENTILE([% Agree],0.75)

Add both these fields to the Detail shelf again, then add another reference line (band) similar to that above, but referencing the quartile fields. Set the Fill colour to be a darker grey.

Adjust the formatting and set the tooltips and you’ve got the main chart…. well almost…

In the solution, the first column, the question no, is not labelled. I couldn’t figure out how to do this, which is why I relabelled to simply No. I tried various things, including using text boxes as column headings on the dashboard, but the layout just didn’t work.

BUT I’ve now found out how to do it… because I googled, and I didn’t yesterday when I was building 😦 Andy Kriebel explains it all here. He searches for a ‘zero width space’ character on this site , and then copies the resulting ‘character image’ displayed and pastes into the label of a calculated field. Watch the video to see it in action, but I’ve noted the steps here, just as much for my own benefit when I can’t remember what to do in future… I can see this type of feature cropping up often 🙂

The legend utilises a lot of the concepts above, but we don’t what the mark changing with each university selection. So let’s just hardcode

Legend Avg

AVG(IF [University] = ‘Middlesex University’ THEN [% Agree] END)

and we’ll need a dedicated field for the colour

Legend Colour

[Legend Avg] – [Overall Avg] >=0

The legend sheet can then be built just by plotting the Legend Avg pill on the Columns shelf, with a mark type of circle, the Legend Colour on the Colour shelf, and the same pills used in the reference lines above on the Detail shelf.

When adding the reference lines and bands this time, you will need to add labels and format their position.

The quartile band, also has dotted lines indicating the end of the band, which you can apply as part of the band properties. However the quartile band also has one label left aligned, while the other is right aligned. For a single reference band, the labels can either both be formatted left aligned or both right aligned. To resolve this, don’t add a label to the ‘band to’ section. Create another reference line for the Upper Quartile value, and you can then format the label of this independently.

My published via associated to this challenge is here.

My version based on the original challenge from 2017 is here. The requirement was a bit more complicated it would seem, and it looks like I utilised FIXED LODs quite heavily.

Happy vizzin’! Stay Safe!

Donna

What happens if? Can you update sales forecast and targets using only parameters?

So after Ann’s gentle workout for week 6, newly crowed Tableau Zen Master Lorna, hit us with this challenge, and I confess, I struggled. The thought of then having to write this blog about it even brought a little tear to my eye 😦

But here I am, and I will do my best, but I can’t promise I understood everything that went on in this. I truly am amazed at times how some people manage to be so creative and bend Tableau to their will. It really is like #TableauBlackMagic at times!

So I read the challenge through multiple times, played around with Lorna’s published viz, stared at the screen blankly for some time…. I found the University Planning Dashboard viz by Ryan Lowers that Lorna had referenced in the challenge as her inspiration (she’d linked to it from her published viz). I played around with that a bit, although that took a while for me to get my head round too.

I also did a google search and came across Jonathan Drummey‘s blog post : Parameter Actions: Using a parameter as a data source. This provided a workbook and some step by step instructions, so I used this as my starting point. I downloaded the workbook, copied across the fields he suggested and tried to apply his instructions to Lorna’s challenge. But after a couple of hours, it felt as if I was making little progress. I couldn’t figure out whether I needed 2 or 4 parameters to store the ‘list’ data source variables (one each to store the list of selected categories for forecast, the list of selected categories for target, the list of selected forecast values, and the list of selected target values, or one each to store the list of selected categories and forecast values combined, and selected categories and target values combined). Suffice to say I tried all combos, using a dashboard to show me what was being populated on click into all the various fields/parameters I’d built. But it just wasn’t giving me exactly what I needed.

I downloaded the University Planning Dashboard and tried to understand what that was doing. And finally I shrugged my shoulders, and admitted defeat and cracked open Lorna’s solution. When I finally get to this point in a challenge, I try just to ‘have a peak’, and not simply follow verbatim what’s in the solution. I gleaned that I did need only 2 parameters, and that what I had been doing with my attempts with Jonathan’s example was pretty close. It made me feel a bit better with myself.

How things then transpired after that I can’t really recall – it was still a lot of trial and error but I finally got something that gave me the Sales Forecast data and associated select & reset functionality (by this time I’d probably spent 4 hours or so on this over a couple of evenings). Once I’d cracked that, the target was relatively straight forward, so by the time I’d finished on the 2nd day, I had a dashboard that allowed the selections/resets and simply presented the data in a table on screen. I chose to keep that version as part of my published solution, just for future reference (see here). I then finished off the next day, building the main viz.

What follows now, is just an account of the fields etc I used to build my solution. So let’s get going….

Building the Sales Forecast Selector

I’m going to start by focusing on building the left hand side of the viz, setting and resetting the Sales Forecast values for each Category.

We need 2 main parameters to start with:

Forecast Param

An integer parameter defaulted to 70,000. This is the parameter that stores the value of the forecast to set.

Forecast List

A string parameter defaulted to empty. This is the parameter which will ‘build up’ on selection of a category, to store a delimited list of category + forecast values – ie the data source parameter.

Oh, and I also used a 3rd parameter, Delimiter, which is just a string parameter storing a :

The delimiter needs to be a distinct character that mustn’t exist in the fields being used. The Category field nor the Forecast Param field will contain a ‘:’, so that’s fine. But any other unused character would work just as well. Having this field as a parameter isn’t ultimately necessary, but it makes it easy to change the delimiter to use, if the chosen value doesn’t end up being suitable. It was also a field used in Jonathan Drummey’s solution I’d based my initial attempts on.

Now we need to build the viz to work as the category selector.

I simply put Category on the Rows shelf, sorting the pill by SUM(Sales) descending and set the Mark Type to circle. Oh – and I set a Data Source Filter to set the Order Date just to the year 2019.

I also needed the following

  • something to colour the circles based on whether the Category was selected or not
  • something to use to help ‘build up’ the List parameter ‘data source’
  • something to return the forecast value that had been selected against the specific Category

Category Exists in Forecast List

CONTAINS([Forecast List], [Category])

If the Category exists within the Forecast List string of text, this field will return true, and indicates the Category has been ‘selected’. This field is added to the Colour shelf, and the colour needs to be adjusted once parameter action has been applied to distinguish between true & false.

Add to Forecast List

if [Forecast Param]<>0 THEN
[Forecast List] +
[Category] + ‘_’ + STR([Forecast Param]) + [Delimiter]
ELSE ”
END

If the entered Forecast value isn’t 0, then append <Category>_<Forecast Value>: to the Forecast List parameter. Eg if the Sales Forecast value is $50,000 and Technology is selected, then Technology_50000: is added to the existing Forecast List parameter, which has started as blank.

If the Sales Forecast value is then changed to $10,000 say, and Office Supplies is selected, then the Forecast List parameter will become

Technology_50000:Office Supplies_10000:

This Append To Forecast List calculated field is used in conjunction with the Forecast List parameter within a Parameter Action on the dashboard to make all the ‘magic’ happen. The Append To Forecast List field must be in the view to be available to the parameter action, so it is added to the Detail shelf.

When a circle is selected the Append To Forecast List field is used to ‘set’ the Forecast List parameter, subsequently building up a string of Category_Value pairs.

Finally, on hover, the Category and the value of the selected sales forecast at the time must be visible on the Tooltip. To get the value at the point of selection, which isn’t necessarily the latest value visible in the Sales Forecast parameter displayed on screen, the following field is required:

Current FC Value

INT(if contains([Forecast List],[Category]) then
REGEXP_EXTRACT([Forecast List],[Category]+”_(-?\d+)”)
end)

This manages to pull out the number associated with the Category, so in the above example, would return 50000 for Technology and 10000 for Office Supplies.

This field has custom formatting applied : ▲”$”#,##0;▼”$”#,##0 and is added to the Tooltip shelf.

RegEx is a concept I have yet to really crack, so there is no way I’d have come up with the above on my own. I think it’s looking for the named Category followed by Underscore (_) followed by either 1 or no negative sign (-) followed by some numbers, and returns just the numeric part.

Finally, the circles shouldn’t be ‘highlighted’ when selected on the dashboard. To stop this from happening a calculated field of True containing the value True, and a field False containing the value False are required. These are both added to the Detail shelf, and a Filter Action is then required on the dashboard setting True = False. This is a technique that is now becoming a familiar one to use, having been used in earlier #WOW2020 challenges.

So my ‘selection’ sheet looks like

and when added to the dashboard, the parameter action looks like :

with the filter action looking like :

At this point, I’d suggest using a ‘test’ dashboard which contains the selection sheet, displays the Forecast List and Forecast Param, and has the dashboard actions described above, applied to get an idea of what’s going on when a circle is selected, and the values of the Forecast Param changed.

The final part to this set up, is the ‘reset’ button, which when clicked on, empties the Forecast List parameter.

Create a new sheet, change the Mark Type to Text, and on the Text shelf add the string ‘↺’. I simply typed this ‘into’ a pill, but you could create a calculated field to store the ‘image’, which isn’t actually an image, but a special string character, that I got off my favourite ‘go to’ unicode characters website.

You then need a calculated field

Forecast List Reset

that just contains an empty string. This is added to the Detail shelf.

Put this sheet on the ‘test’ dashboard, and create another parameter action

This takes the value out of the Forecast List Reset field and sets the Forecast List parameter, subsequently resetting the list to an empty string on click.

Verify this is all working as expected.

Building the Sales Target Selector

Subject to Sales Forecast selector working as expected, then apply exactly the same principles to create the Target selection sheet and associated parameters.

The only slight difference with the fields used in the Target selection is:

Add to Target List

if [Target Param]>0 THEN
[Target List] +
[Category] + ‘_’ + STR([Target Param]) + [Delimiter]
ELSE ”
END

This just applies the addition to the list if the entered target is a +ve number (ie > 0), rather than not 0 as in the forecast selection.

The Target also needs to be displayed on the Tooltip, and this time there is a default target value that should be displayed, even when no selection has been made. For this I created

Target

IF ZN(MAX([Current Target Value])) = 0 THEN
MIN(IF [Category]= ‘Furniture’ THEN 270000
ELSEIF [Category]= ‘Office Supplies’ THEN 260000
ELSEIF [Category]= ‘Technology’ THEN 250000
END)
ELSE MAX([Current Target Value]) END

which was formatted to a currency of 0 decimal places, prefixed by $. This was added to the Tooltip shelf.

At this point, you should now have both the ‘selection sheets’ working on the dashboard, so we can now focus on building the main viz.

Building the Bar Chart

Rather than building the bar chart, I first decided to build a tabular view that simply presented on screen all the bits of data I needed for the bar chart, this being

  • Sales value per Category (simply SUM(Sales))
  • Sales Forecast value per Category (ie Sales + selected Forecast value)
  • Selected Sales Target value per Category (this is the Target field described above)
  • % Difference between Sales & Target
  • % Difference between Sales Forecast & Target

So I created the following additional calculated fields:

Forecast

SUM([Sales]) + MAX([Current FC Value])

formatted to currency prefixed with $ set to 0 dp.

Forecast vs Target Diff

([Forecast]-[Target])/[Target]

custom formatted to ▲0%; ▼0%

Sales vs Target Diff

(SUM([Sales])-[Target])/[Target]

also custom formatted to ▲0%; ▼0%

Adding the table to the ‘test’ dashboard allows you to sense check everything is behaving as expected

Now its just a case of shifting the various pills around to get the desired view. Ensure at least one Sales Forecast Category has been selected, to make it easier to ‘see’ what you’re building.

Lorna stated the target should be displayed as a Gantt mark type, with the sales and the forecast displayed as bars. This means a dual axis chart is required, with sales & forecast on one axis and target on the other.

To get Sales and Forecast onto the same axis, we need to add Category to the Rows (sorted by Sales desc) and Measure Values to the Columns, filtering to only the two measures we need.

Set the Mark Type to bar, and add Measure Names to both the Colour and the Size shelf.

Adjust colours and sizes to suit.

You might have something like


where the measures are ‘stacked’, so the bar is the length of the Sales then the length of the Forecast. We don’t want this, so need to set Stack Marks to Off (Analysis menu -> Stack Marks -> Off).

Add all the necessary fields to the Label shelf and format accordingly (you may need to widen the rows to make the labels show against each row).

Note – in my solution I created some fields to make the opening & closing bracket around the Forecast v Target Diff value only show when a Forecast had been selected, however in writing this blog, I realise it was simpler just to change the formatting of the Forecast v Target Diff to add the brackets around the number. The custom formatting was changed to : (▲0%); (▼0%)

Adjust the Tooltip to suit too.

Now add Target to Columns alongside Measure Values. Set to Dual Axis and Synchronise the axis. Reset the Measure Values mark type back to bar if needed, and set the Target mark type to Gantt.

Remove Measure Names from the Colour and Size shelf of the Target marks card. Untick Show Mark Labels too. Adjust the colour of the mark to suit, and you should pretty much be there now…

Tidy up the final bits of formatting, removing/hiding the various axis, labels, gridlines etc etc.

When this is all put together on the dashboard, you might need to fiddle about a bit with layout containers to get the bar chart lined up with the Selector views.

And with that I’m done! My published version is here, along with the ‘check’ dashboard I used to sense check what was going on, as I’m sure if I ever looked at my solution again, I’d struggle to understand immediately 🙂

Once again, I just want to acknowledge those that manage to create this magic with Tableau. I applaud you!

Happy vizzin’!

Donna

Can you build a dynamic scatter plot with proportional brushing?

It’s #data19 in Vegas, so this week’s #WorkoutWednesday challenge, set by Curtis was run ‘live’ at the Conference. Not being there, I had to wait patiently at home until it was released and build solo 😦

Given it was a live challenge, Curtis wanted to ensure it offered the right balance for those newer to Tableau & #WorkoutWednesday, while still giving the more experienced participants something to get their teeth into. And I think he managed this well.

Building the scatter plot

The scatter plot is built to be dynamic, with both the axes and displayed marks changing based on user selection. This is driven by 3 parameters

Set Level of Detail

String parameter, containing 3 values : Product, Manufacturer, Customer

(quick side note here…. the challenge states to use the Superstore Sales dataset that comes with v2019.1. To get the Manufacturer field, you need to use the provided Tableau data source and not the excel file – this always trips me up!).

Set X-Axis

String parameter containing 4 values : Quantity, Sales, Orders, Discount

(another note … the challenge says to use Profit, but the solution provided used Orders – I chose to use Orders too so I could verify a match)

Set Y-Axis

The same as above (just duplicate the parameter and rename).

So with the parameters in place, we need to build calculated fields to reference them.

LOD

CASE [Set Level of Detail]
WHEN ‘Customer’ THEN [Customer Name]
WHEN ‘Manufacturer’ THEN [Manufacturer]
WHEN ‘Product’ THEN [Product Name]
END

X-Axis

CASE [Set X-Axis]
WHEN ‘Quantity’ THEN SUM([Quantity])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Orders’ THEN COUNTD([Order ID])
WHEN ‘Discount’ THEN SUM([Discount])
END

Y-Axis

CASE [Set Y-Axis]
WHEN ‘Quantity’ THEN SUM([Quantity])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Orders’ THEN COUNTD([Order ID])
WHEN ‘Discount’ THEN SUM([Discount])
END

(basically duplicate X-Axis and edit & rename)

With these we can now build the basic scatter : [X-Axis] on Columns, [Y-Axis] on Rows and [LOD] on Detail :

Changing the parameters and the scatter will change too.

So now its just a case of formatting and adding a few additions to match the display.

First up, the solution has the X-Axis scale on the top, rather than the bottom. This is achieved by dual-axis.

Duplicate the [X-Axis] on the Columns shelf (Press Ctrl, click on the [X-Axis] pill and drag next to it.

Make dual-axis and synchronise

Edit the bottom axis, deleting the title of the axis, and changing the Tick Marks to None.

The solution also shows each axis is labelled based on the parameter selection. To do this, edit both the left and top axis to remove the titles, then add the [Set X-Axis] parameter to Columns and [Set Y-Axis] parameter to Rows.

Now right-click on the displayed Set X-Axis and Set Y-Axis and ‘hide field labels for columns/rows’, and right-click on Discount and ‘rotate label’.

Finally, adjust the tooltip to contain :

<Parameters.Set Level of Detail> : <LOD>

That’s the core viz built, it just needs some formatting applied, to

  • remove column and row lines
  • remove gridlines (the 0-lines are retained)
  • Change mark type to circle, adjust colour to suit and add border

Bar Charts & Proportional Brushing

When it’s all together on the dashboard, the selection of marks in the scatter should cause the bar charts to update to show the % of each variable accounted for in the selection. This concept is referred to as proportional brushing, and makes use of the Set Actions feature. Tableau’s own KB article describing Set Actions and proportional brushing can be seen here.

The first thing we need to do is build a set : right click on the [LOD] field and select Create -> Set. In the Set dialog, name the set Selected LOD and randomly select some values (not all)

We also need various calculated fields :

# LOD

COUNTD([LOD])

Just counts the number of distinct items in the user selected LOD. When bringing the Selected LOD set into play, we can the count the number of items in or out of the set, and using a quick table calculation, change to get the % of total. I tend to start using data tables at this point, to sense check the data I need before I viz :

I’ve turned on Grand Totals for columns too, just so I can check the numbers are what I expect.

Adding the [X-Axis] and [Y-Axis] values into the table, and finding % of total too

and we’ve got all the % needed to make up the bars. At this point, edit each table calculation and fix it to compute by the In/Out Selected LOD (so if you shift pills around later, you won’t change the values).

While we could build the viz at this point, I noticed that the tooltip on the bar chart displays the same information whether you hover the ‘in’ or the ‘out’ portion of the bar

With the data we currently have, we couldn’t do this, so we need a few more fields/calculations. We need to be able to get the total value of the LOD/measure against each row, as well as the number and % of the ‘In’ items.

We’ll use table calculations for this.

Total #LOD

WINDOW_SUM([# LOD])

# LOD Selected

ZN(WINDOW_MAX(IF ATTR([Selected LOD]) THEN COUNTD([LOD]) END))

This counts the number of items selected in the set, and the WINDOW_MAX statement will then ‘spread’ that value across both the rows (as only 1 row contains a value).

% LOD Selected

[# LOD Selected]/[Total # LOD]

This is formatted to 1 dp.

Adding into the ‘check’ table, you can see how the values with table calculations are matching the initial set

A similar set of steps are the repeated to get the X & Y axis values

So now I’m happy I’ve got all the fields needed to build the bars, we can, and this is done in 3 separate sheets (it can be done on a single sheet, but I noticed some spacing on Curtis’ solution that suggested it wasn’t).

LOD Bar

You can build from scratch, or as I tend to do, duplicate the check sheets, remove the pills I don’t want, and shuffle the rest around. You ultimately want :

  • [# LOD] as % of total on Columns
  • [Selected LOD] set on Colour (adjust colours, and make sure the Out is listed before In on the colour legend, so the darker values show first)
  • [Total LOD], [# LOD Selected] , [% LOD Selected] on Tooltip

The format to remove axis title, show axis ticks etc.

Name the sheet, then duplicate to create an X-Axis version, dragging the equivalent pills over the top, and do the same for the Y-Axis version. If you do this, just double check all the values are matching your check sheet, and the table calc settings haven’t been lost.

Putting it all together on the dashboard

Build the dashboard, and add the 4 sheets (the scatter and 3 bars) into their appropriate place, and expose the 3 parameters too.

To get the proportional brushing technique to apply to the bars, add a Set Action to change the value of the Selected LOD set when marks are selected in the scatter plot. The Set Action needs to be set to ‘remove all values from set’ when the marks are unselected.

And that’s it! My published version is here. Enjoy!

Happy vizzin’

Donna