Tag: Scatter Plot

Can you create this ‘zoom and reset’ scatter plot?

Donna ColesLeave a comment

For this week’s #WOW challenge we’re focusing on parameter driven axes ranges.

Building the basic viz

After connecting to the dataset, add Sales to Columns and Profit to Rows. Add Customer Name to Detail and change the mark type to circle.

The chart is divided by reference lines which I chose to define as parameters (but these were additional to the ones mentioned in the challenge requirements).

pProfitRef

integer parameter defaulted to 0

pSalesRef

integer parameter defaulted to 5000

Add both parameters to the Detail shelf, the add a reference line on the Sales axis that refers to the pSalesRef value.

and then repeat and add a reference line to the Profit axis referencing the pProfitRef field.

To colour the 4 segments of the chart, create new fields

Sales per Customer

{FIXED [Customer Name]:SUM([Sales])}

and

Profit per Customer

{FIXED [Customer Name]: SUM([Profit])}

These capture the total sales / profit at the customer level, and we can then determine which quadrant each customer is in by

Cohort

IF [Sales per Customer]>=[pSalesRef] THEN
IF [Profit Per Customer]>=[pProfitRef] THEN ‘High Sales, High Profit’
ELSE ‘High Sales, Low Profit’
END
ELSE
IF [Profit Per Customer]>=[pProfitRef] THEN ‘Low Sales, High Profit’
ELSE ‘Low Sales, Low Profit’
END
END

Add this to the Colour shelf and adjust colours to suit. Then set the opacity to 50% and increase the size of the marks a bit.

To add matching coloured borders around the marks, add another instance of Profit to Rows. Change the mark type of the 2nd Profit marks card to Shape and change the shape to be an open circle. Set the opacity to 100%. Set the chart to dual axis and synchronise the axis.

Adjust the Tooltip and hide the right hand axis (uncheck show header).

Dynamically adjust the axis

The axes will change by adjusting them to refer to parameters. By default we need the axis to try to replicate what it gets set to automatically. For this we need to capture the maximum and minimum sales and profit values and add a ‘buffer’ to give the extra space. I played around with a few options for the buffer, so created a parameter to store this until I got the value that seemed to work best (again this was an additional parameter that I added to just help not having to change multiple calculated fields as I got the value I wanted.)

pBuffer

integer parameter defaulted to 2000

Then create 4 fields to define the max & min of the two measures +/- buffer

Min Sales + Buffer

{MIN([Sales per Customer]) – [pBuffer]}

Max Sales + Buffer

{MAX([Sales per Customer]) + [pBuffer]}

Min Profit + Buffer

{MIN([Profit Per Customer]) – [pBuffer]}

Max Profit + Buffer

{MAX([Profit Per Customer]) – [pBuffer]}

Then create 4 parameters which will define the values we ca use to set the axis

pX-Min

float parameter that is set to the Min Sales + Buffer field when workbook opens (selecting this will then populate the value)

Create further parameters

pX-Max

float parameter that is set to the Max Sales + Buffer field when workbook opens

pY-Min

float parameter that is set to the Min Profit + Buffer field when workbook opens

pY-Max

float parameter that is set to the Max Profit + Buffer field when workbook opens

Once all the parameters exist, edit the Sales Axis and change the axis to use a custom range that references the pX-Min and pX-Max parameters

Do the same for the Profit axis, but reference the pY-Min and pY-Max parameters instead.

Finally while we’re still on the workbook, create two new fields

True

TRUE

and

False

FALSE

and add these to the Detail shelf. We’ll need these later to stop marks highlighting when we click.

Name the sheet Scatter or similar.

Building the Reset button

This actually requires another sheet (even through the requirements says 1 sheet).

Create a new field

Reset Axis

‘Reset Axis’

Add this to the Text shelf of a new sheet. Change the mark type to shape and select a transparent shape (refer to this blog to understand how to create this).

Set the view to Entire View and align the font middle centre and increase the font size. Set the background of the whole worksheet to black. Adjust the tooltip. Add Min Sales + Buffer, Max Sales + Buffer, Min Profit + Buffer and Max Profit + Buffer to the Detail shelf, along with the True and False fields.

Name the sheet Reset or similar

Adding the interactivity

Add the Scatter and Reset sheets to a dashboard, removing any parameters/legends etc that get added. Create dashboard parameter actions to set the axis parameters when selections are made on the scatter plot:

Set X Max

On select of the Scatter sheet, set the pX-Max parameter, passing in the maximum value of the Sales field.

Set X Min

On select of the Scatter sheet, set the pX-Min parameter, passing in the minimum value of the Sales field.

Set Y Min

On select of the Scatter sheet, set the pY-Min parameter, passing in the minimum value of the Profit field.

Set Y Max

On select of the Scatter sheet, set the pY-Max parameter, passing in the maximum value of the Profit field.

Also create dashboard parameter actions to ‘reset’ the axis parameters when the Reset button is clicked:

Reset X Min

On select of the Reset sheet, set the pX-Min parameter, passing in the minimum value of the Min Sales + Buffer field.

Reset X Max

On select of the Reset sheet, set the pX-Max parameter, passing in the maximum value of the Max Sales + Buffer field.

Reset Y Min

On select of the Reset sheet, set the pY-Min parameter, passing in the minimum value of the Min Profit + Buffer field.

Reset Y Max

On select of the Reset sheet, set the pY-Max parameter, passing in the maximum value of the Max Profit + Buffer field.

All these 8 actions should now combine to drive the ‘zoom & reset’ functionality.

Finally, the last step to make the display ‘nicer’ is to deselect the marks from being highlighted when selected. Add a dashboard filter action

Deselect Scatter

on select of the scatter object on the dashboard, target the scatter sheet directly, passing the selected fields of True = False. Show all values when the selection is cleared.

Repeat an create a similar action for Deselect Reset.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you make a dynamically highlighting tooltip?

Donna ColesLeave a comment

For #WOW2025 Week 25, Kyle challenged us to make use of Set Actions to recreate a viz where the ‘viz in tooltip’ updates based on the option the user interacts with on the base viz.

Modelling the data

The excel file provided contained 3 sheets which needed to be combined to use for this challenge. My data model looks like this

Attendance is related to Divisions on the fields Tm = Team

Attendance is also related to Record on the fields Tm = Tm.

Building the Base Bar Chart

On a new sheet, add Division to Rows and Attend/G to Columns changing the aggregation to AVG. Sort the data descending.

Change the Colour of the bars to dark grey and show mark labels to display the average attendance per game value. Add W-L% and Est.Payroll fields to the Tooltip shelf and set both to be AVG. Format Est. Payroll to be $ with 0dp. Format W-L% to be formatted to 3dp, but then adjust again and use a custom format to remove the leading 0 (,##.000;-#,##.000)

Format the label to be bold and to match mark colour. Format the row labels, remove the row label heading (right click > hide field labels for rows). Hide the axis and remove all gridlines, axis rulers etc. Update the viz title and name the sheet Bar-Division or similar.

Build the Scatter Plot

On a new sheet add W-L% to Columns and Attend/G to Rows, setting both the use an AVG aggregation, and then add Tm (from the Attendance table) to Detail. Adjust the W-L% axis so it doesn’t always include 0 (right click axis > edit axis> uncheck include zero). Adjust the title of the axis too. Adjust the title of the Attend/G axis too. Change the mark type to circle and increase the size.

We need the chart to show a difference between the marks related to a selected Division and those which aren’t. Create a set from the Division field (right click the field > create > and select NL West.

Add Division Set to Colour and adjust accordingly. Add a dark grey border to the circles. Remove all gridlines and name the sheet Scatter or similar.

Build the Attendance by Team Bar Chart

On a new sheet, add Tm (from the Attendance table) to Rows and Attend/G to Columns, setting the aggregation to AVG. Sort descending. Add Division Set to Colour.

Create a new field

Label Attendance

IF [Division Set] THEN [Attend/G] END

and add to the Label shelf. Format the label so it is bold and set to match mark colour. Hide the row label heading and the axis. Hide all gridlines, zero lines et. Name the sheet Bar-Team or similar.

Adding the Viz in Tooltip

Navigate back to the Bar-Division sheet. Update the Tooltip to reference the Division and the other top level measures. I used a double tab between the headings on one line and again on the values on the next line to make the information line up on hover (even though they look misaligned on the tooltip dialog).

To add each sheet to the tooltip, use Insert > Sheets button in toolbar and select the Scatter sheet and then press tab and select the Bar Team sheet.

Then adjust the text inserted so both filtering sections state filter=”None”. This stops the VIT filtering out by default all the data that isn’t associated to the selection you’re coming from. Adjust maxwidth and maxheight to 350 (I had it set larger, but then it didn’t display properly on Tableau Public, so had to adjust there.

Adding the interactivity

Create a dashboard and add the Bar-Divison sheet. At this point showing the tooltip on each bar will always show the information related to NL West since that was the option selected when we created the set. To fix this, create a new dashboard set action

Set Division

On hover of the Bar-Division sheet, target the Division Set, and assign values to the set. When the selection is cleared, keep set values. Only allow this to be run on single-select only

And now as you hover over different bars, the highlighted circles and bars in the viz in tooltip will change.

And that’s it. My published viz is here.

Happy vizzin’!

Donna

Can you create a scatter plot investigation?

Donna ColesLeave a comment

This week’s #WOW2024 challenge was set by a guest poster, Robbin Vernooij, who wanted us to build a scatterplot with additional features to aid analysis. The main focus was on using Set Actions, so that’s what I used throughout the challenge, although parameters could be also be used.

Modelling the data

I took the simpler route when combining the data sources. After connecting to the Life Expectancy (lex.csv) data source, I deleted all the columns relating to the years except 2022 (Ctl Click to multi select the columns, and then right click and ‘hide’) . I then renamed the column from 2022 to Life Expectancy. The data source just contained 2 fields Country and Life Expectancy.

I then added the Co2 Pcap Cons.csv data source and related it via the Country field. Again I removed all the unnecessary year fields except the 2022 column, and renamed this to  Co2 Pcap Cons.

Building the Scatter Plot

On a new sheet, add Co2 Pcap Cons to  Columns and Life Expectancy to Rows. Add Country to Detail.

Hide the null indicator.

We need to identify a ‘selected’ country. We could use a parameter for this, but as mentioned above, I’ll use a set.

Selected Country Set

Right click on Country > Create  > Set.  Select a single country from the list (I chose Russia).

From this we need to determine the Life Expectancy and Co2 Pcap Con values for the selected country, but this value needs to be associated to every Country in the data set (ie every row of data), so we can use a FIXED LoD.

Selected Country Co2

{FIXED:SUM(IF [Selected Country Set] THEN [Co2 Pcap Cons] END)}

Selected Country Life Expectancy

{FIXED:SUM(IF [Selected Country Set] THEN [Life Expectancy] END)}

With these, we then want to define a min and max range for each measure so we can build the reference bands. The tolerance for this range wasn’t mentioned in the requirements, so I checked the solution to ensure I could validate other calculations later on.

Min Co2

[Selected Country Co2] – 1

Max Co2

[Selected Country Co2] + 1

Min Life Expectancy

[Selected Country Life Expectancy] – 4

Max Life Expectancy

[Selected Country Life Expectancy] + 4

Add all four fields to the Detail shelf.

Add a reference band to the Co2 Pcap Cons axis (right click axis > add reference line). Select band and set it to be from the Min Co2 field to the Mx Co2 field.

Repeat the steps for the Life Expectancy axis, to run from the Min Life Expectancy field to the Max Life Expectancy field.

In the example above, I have Russia as the selected country. We now want to identify all the countries that are falling within the bands.

Within Co2 Band

[Co2 Pcap Cons] <= [Max Co2] AND [Co2 Pcap Cons]>=[Min Co2]

Within Life Expectancy Band

[Life Expectancy]<= [Max Life Expectancy] AND [Life Expectancy] >= [Min Life Expectancy]

And with this, we create another set

Within Band Set

Select the Condition tab, and enter the formula

MIN([Within Co2 Band]) OR MIN([Within Life Expectancy Band])

Add Selected Country Set to Colour, to Size and to Shape. Adjust shape and size to suit. Then add Within Band Set to Detail and then adjust the icon to the left of the pill to the Colour icon, so 2 pills are now on the Colour shelf. Adjust the colours to suit.

Then create

Label – Country

IF [Selected Country Set] THEN [Country] END

And add to the Label shelf. Align bottom centre, and allow labels to overlap other marks.

Hide the Tooltip, Hide all the gridlines and row/column dividers. Format Co2 Pcap Cons and Life Expectancy to 1 dp. Name sheet Scatter or similar.

Building the Average Bar

On a new sheet, add Selected Country Set to Rows. Add Co2 Pcap Cons  and Life Expectancy to Columns and change the aggregation of both from SUM to AVG. Manually reorder the In/Out header so Out is listed first. Show the labels. Add Selected Country Set to Colour on the All marks card and adjust accordingly.

Double click into Columns and type MIN(0.0), then move the pill so it’s the first one listed. Change the mark type of the MIN(0.0) marks card to shape. Add Selected Country Set to shape and adjust.

Create a new field

Header Label

IF [Selected Country Set] THEN [Country] ELSE ‘All others’ END

Add this to the Label shelf of the MIN(0.0) marks card. Align the label middle left.

Edit the MIN(0.0) axis to be fixed from -5 to 1 to shift the display to the right

Then remove the axis title, and set the tick marks to None so the axis for this section is hidden

Add Header Label to the Tooltip on the All marks card, and update the tooltip. Remove all gridlines, row & column dividers and hide the Selected Country Set pill on Rows (uncheck show header). Name the sheet Avg Bar or similar.

Building the Count Bar

On a new sheet, add Within Band Set to Columns and lex.csv(Count) to Rows. Add Within Band Set to Colour and Country to Detail. Adjust Colour and tooltip. Name the sheet Count Bar or similar.

Adding the interactivity

Add the sheets to a dashboard and arrange accordingly, Add a dashboard set action

Select Country

On hover of the Scatter chart, target the Selected Country Set. Only allow single selection. Assign values to the set on hover, and retain the values in the set when the selection is cleared.

And hopefully that should be it. My published viz is here.

Happy vizzin’!

Donna

Connected Scatterplots

Donna ColesLeave a comment

Sean set this challenge this week, to build a connected scatterplot to allow additional insights to be gained.

We need to show a circle per country related to the specified year, and then show the data for all years if a country is specified from the drop down, or ‘clicked on’ by the user; these data points are all then connected.

Let’s start by building up the various parameters and calculated fields needed to help with this.

Setting up the data

For the user inputs, I used parameters

pYear

Integer parameter, defaulted to 2000, displayed in a format so no thousand separators are shown. I populated the list using the values from the Year field.

pCountry

string parameter defaulted to All. I populated the list of entries by first adding values from the Country field. I then manually added an All entry to the bottom of the list and dragged it to the top. I could then set All as the default value.

On a new sheet, show these parameters.

We’re going to use a dual axis chart to display the viz, and for this, we’re going to get the relevant measures for the specific Year and for the specific Country.

To see what I’m aiming for, lets’ build out the data in a table. Add Country to Columns and Year to Rows. Display the values of Fertility Rate and Life Expectancy. This just gives us all the data points

But we only want the points related to the pYear (2000) or if pCountry if it’s not All (in this case Afghanistan).

So we create

Fertility Rate for Year

[Year] = [pYear] THEN [Fertility Rate] END

Life Expectancy for Year

IF [Year] = [pYear] THEN [Life Expectancy] END

format these to 1 dp and then add to the table. The fields only contain values for the specified pYear.

Create

Fertility Rate for Country

IF [Country] = [pCountry] THEN [Fertility Rate] END

Life Expectancy for Country

IF [Country] = [pCountry] THEN [Life Expectancy] END

format these to 1 dp and also add to the table. We now have these entries only existing for the selected pCountry.

If pCountry is set to All, the Fertility Rate for Country and Life Expectancy for Country are empty for every Country.

We now have the basics we need to build the viz.

Building the ScatterPlot

On a new sheet, show the parameters, then add Fertility Rate for Year to Columns and Life Expectancy for Year to Rows and Country to Detail. Change the mark type to circle. Adjust the Tooltip.

Create a new field

All Countries

[pCountry] = ‘All’

and add to the Colour shelf. Adjust the colours so when pCountry = All, the All Countries colour legend is True and displays a darker instance of a colour, as opposed to when pCountry is set to something else, and the All Countries colour legend is false.

Now add Fertility Rate for Country to Columns and Life Expectancy for Country to Rows. Change both fields to be Dimensions. On the 2nd marks card, add Year to Detail. and remove the All Countries field from the Colour shelf. Change the mark type to line and move Year to Path. Set the colour accordingly.

Then set both the Rows and Columns to be Dual Axis and synchronise both axis. Remove Measure Names from the colour shelf on the all marks card.

Adjust the Tooltip of the 2nd (line) marks card.

Add Year to the Label shelf of the 2nd marks card and update so it just displays for the Min & Max value. Adjust font size and style.

On the first marks card (the circle) add Country to Label and adjust so it only displays when selected.

Hide the right and top axis. Remove row & column dividers. Hide the null indicator and update the title of the axes. Name the sheet Scatterplot or similar.

Building the Dashboard

Add the sheet to a dashboard, and float the parameters into a suitable location. Add a floating text box that references the pYear parameter and position bottom left of the chart. Add a parameter action to update the pCountry parameter when a circle is clicked.

Click Country

On select of the Scatterplot Viz, set the pCountry parameter, passing in the value from the Country field. When cleared, set the parameter back to All.

Finally, if you click a circle to select a Country, you’ll find that the circles ‘fade out’ more than what you want – you want them to look the same as the colour when a country is selected via the dropdown. Essentially, you want all the circles to be ‘highlighted’ on click. To do this, create a new field

HL

“Highlight”

and add this to the Detail shelf on the scatterplot sheet. Then on the dashboard, add a Highlight action

HL marks on click

On select of the scatterplot viz, target itself, highlighting the HL selected field

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you dynamically zoom into a clustered area?

Donna ColesLeave a comment

For this week’s #WOW2024 challenge, Lorna revisited an old #WOW2021 challenge based on Hans Rosling’s chart used in his famous TED talk.

Modelling the data

The first part of the challenge involves modelling the data. Since I’d blogged a solution guide to the original 2021 challenge here, I thought I’d refer myself to my own blog. Re-reading it though, I found I originally had some issues getting all the data sources pivoted in the way I needed, and ended up having to create the csv files as extracted hyper files separately before putting them together. I encountered the same issues again (I was hoping that ‘maybe’ it had been a version problem).

However, I then watched the solution guide that was posted on the old blog page that Lorna had provided, and I found where the problem was.

When I was trying to add connections to the additional data sources into Tableau, I was using the Add option, browsing for the file, and then dragging it into the pane, where I was then unable to pivot it

However, as the csv files I needed are all located in the same directory, the files were already listed on the left hand pane, and dragging from there allowed me to do what I needed, and I’ll talk through that now. Why, adding via the Add button does not work, I don’t know….

The data provided consists of 4 files

  1. Life Expectancy csv
  2. Population csv
  3. Income csv
  4. Geographies (Region Mapping) excel file

The first 3 files are stored as a matrix of Country (rows) by Year (columns) with the appropriate measure in the intersection. This isn’t the ideal format for Tableau so the data needs to pivoted to show 3 columns – Country, Year and the relevant measure (life expectancy, population or income depending on which file is referenced).

In Tableau Desktop, connect to the Life Expectancy file and check the Use Data Interpreter checkbox, so the top row of the file is understood to be the column headings.

Now we need to pivot this data; click on the 1800 column, then scroll across to the end, press shift and click on the final column to select all the year columns. While highlighted, right-click and select Pivot. Your data will be reshaped into 3 columns.

I then renamed each column as

  • Country
  • Year
  • Life Expectancy

Change the datatype of the Year column to be a number (whole), as we’ll need to relate the data on this field later, and working with numeric data is more efficient than strings.

Now from the left hand Files pane, drag in the Population file. By default it will pick up a relationship based on the Country field in each file

Once again, multi-select the columns from 1800 across to 2100, and pivot. Rename the fields to Year – Population and Population and change the datatype of the Year – Population field to a whole number. Add an additional relationship on Year = Year – Population

Next, drag in the Income file from the left hand pane and link to the Life Expectancy file. By default it should pick up a relationship based on the Country field in each file (if not add it). Once again pivot the date fields, and rename the fields Year – Income and Income. Change the data type of the Year – Income field to a whole number, and add an additional relationship on the Year fields.

Finally, using the Add option, add a connection to the Geographies/Region excel file and drag the list-of-countries-etc sheet onto the canvas and link to the Life Expectancy file. Add a relationship from County to Name.

Now the data is modelled, we can build out the viz.

Building the Scatter Plot

We only need to show information for the years up to the ‘current’ year. I created a parameter to represent ‘Today’, essentially hard coding a date.

pToday

date parameter defaulted to 16 Jan 2024

I then created a field

Year <= Current Year

[Year] <= YEAR([pToday])

and added this to the Filter shelf of a new worksheet and set the value to True.

Change the Year field to be discrete (right click > convert to discrete),then add to the Filter shelf, select All Values and then select 2024 from the list. Show the filter on the canvas, and change to a single value (dropdown) that displays only relevant values. Also Customise so the ‘All’ value does not show. Only options from 1800 – 2024 should be listed.

Create a new field to get the regions in the correct format

Region

UPPER(REPLACE([Eight Regions],’_’, ‘ ‘))

Now add Income to Columns and Life Expectancy to Rows and add Country to Detail and Region to Colour and adjust accordingly. Change the mark type to circle. Add Population to Size and adjust. Set the opacity of the colour to around 70%.

If you examine the Income axis on the solution, you’ll see the scale isn’t uniform. This is because it’s using a logarithmic scale instead, which you can set by right-clicking on the Income axis -> Edit Axis and selecting the relevant checkbox. Also, untick the Include zero checkbox, and the display should now start looking more like what’s expected.

Add Year to the Tooltip shelf and update the tooltip. Format the Year field so it’s a number with 0dp that does not include the thousand separators.

Also format the Population field to be a number with 1dp displayed in millions, and format the Life Expectancy field to be a number to 1dp.

Adjust the display of the Life Expectancy axis so that it is displayed without the decimal place. Right click on the axis > format, and on the axis tab on the left hand side, format the numbers to be Number standard

We need to be able to adjust the axis based on selection, so need to set the axis to be able to adjust. For this we will need parameters.

pIncomeMin

integer parameter defaulted to 500

pIncomeMax

integer parameter defaulted to 100,000

pLifeExpectancyMin

integer parameter defaulted to 0

pLifeExpectancyMax

integer parameter defaulted to 100

Right click on the Income axis and edit and set the range to be custom, selecting the pIncomeMin and pIncomeMax parameters

Do the same for the Life Expectancy axis, selecting the relevant parameters.

Hide the null indicator and name the sheet Scatter or similar.

Building the Viz in Tooltip

On a new sheet, add the Year <= Current Year field to Filter and set to True.

Then add Year as a continuous dimension field (green pill) to Columns and Country, Income, Life Expectancy and Population to rows. Add Region to Colour.

Edit each of the Income, Life Expectancy and Population axis in turn and select the Independent axis range for each row or column option.

Hide the Country column (uncheck show header) and remove all gridlines, zero lines, axis lines. Set the display to Entire View and name the sheet VIT or similar.

Back on the Scatter worksheet, edit the Tooltip and add a reference to VIT sheet, adjusting the height and width of the sheet to suit (after a bit of trial and error I used 700 x 450) and setting the filter to Country.

When you hover over a mark, the VIT chart should also be displayed, filtered to the country related to the mark hovered on.

Building the Legend

Create a simple ‘table’ with Region on Columns, Colour and Text. Hide the column heading (uncheck show header) and remove all row & column dividers. Align the text centrally and adjust the font to suit.

Name the sheet Regions or similar.

Adding the interactivity

Arrange the sheets on a dashboard and ensure the Year filter is displayed as a single value drop down that only shows relevant values and doesn’t show the All option.

To filter the chart by the Region, add a filter dasboard action

Filter Region

On select of the Region sheet on the dashboard, target the Scatter sheet on the dashboard, passing the selected fields of Region only. Show all values when the region is unselected.

To allow the chart to zoom in, we need to set the parameters referenced in the axis by using parameter actions.

Income-MinSelected

On selection of marks on the Scatter sheet, update the pIncomeMin parameter using the Minimum value of the Income field. When the selection is cleared, reset the field to 500.

Income-Max Selected

On selection of marks on the Scatter sheet, update the pIncomeMax parameter using the Maximum value of the Income field. When the selection is cleared, reset the field to 100,000.

Create 2 further parameter actions similar to above but referencing the pLifeExpectancyMin and Max parameters and resetting to their defaults of 0 and 100 accordingly.

Once done, the viz should be complete. My published version is here.

Note – I found that after publishing from Desktop to Tableau Public, the ‘zoom’ interactivity was lost, and when I edited my viz on Tableau Public the axis had lost their references to the parameters. I updated and republished the viz from Tableau Public. I don’t know why this happened, and whether it’s a known issue, but thought worth noting in case you encountered the same issue.

Happy vizzin’!

Donna

Can you build an interactive Viz in Tooltip?

Donna Coles1 Comment

This week’s #WOW2023 challenge was inspired by Sam Parson’s TC presentation where he demonstrated the concept of an interactive Viz in Tooltip (the workbook he presented is here).

I was aware when I set this challenge, that this was likely to be on the higher end of the difficulty scale, but WOW challenges to me have have always provided a source of inspiration and ideas to take forward into my day job. And by blogging the solutions, I provide myself with a guide to refer to when the need arises. When Sam presented the concept, I immediately wanted to understand how he’d done it, and by setting it as a challenge it provided me with the opportunity to dig into it, and get that documented ‘how to’ guide 🙂

As mentioned in the requirements, I built this using multiple sheets, so we’ll start by just building out most of those sheets.

Building the Scatter Plot

We’re only concerned with data over the last 2 years, so we need to define some measures relevant to these years

Current Year

ZN(IF YEAR([Order Date]) = YEAR({MAX([Order Date])}) THEN [Sales] END)

{MAX([Order Date]} is a Fixed Level of Detail calculation which returns the maximum date in the data set. This calculation is then comparing the Year associated to that date with the Year of each Order Date, and if they match, return the Sales. Wrapping in a ZN ensures a value of 0 in the event there are no Sales.

Prior Year

ZN(IF YEAR([Order Date]) = YEAR({Max([Order Date])}) -1 THEN [Sales] END)

YEAR({Max([Order Date])}) -1 returns the year associated to the latest date then decrements by 1 to get the value of the previous year.

We also need to calculate the difference between the sales across the 2 years and categorise based on the difference

Sales Performance

IF SUM([Current Year]) / SUM([Prior Year]) > 1.1 THEN ‘Increasing’
ELSEIF SUM([Current Year]) / SUM([Prior Year]) < 0.9 THEN ‘Decreasing’
ELSE ‘Static’ END

If the current year sales > 10% of the previous year sales then flag as ‘increasing’, else if current month sales < 90% of the previous year sales then flag as ‘decreasing’ else flag as ‘static’.

Add Prior Year to Columns and Current Year to Rows. Add Manufacturer, Sub-Category and Category to Detail. Change the mark type to Circle. Add Sales Performance to Colour, adjust colours to match and reduce opacity to around 80%. Re-order the colour legend to display Increasing at the top and Decreasing at the bottom. Name the sheet Scatter.

Building the bar chart

On a new sheet add Order Date at the discrete (blue) Month level to Columns. Add Current Year to Rows. Change the mark type to bar and add Sales Performance to Colour. Reduce the opacity of the colour to 80%.

Add Prior Year to Rows. Change the mark type on the Prior Year marks card to gantt bar. Remove the Sales Performance pill from the colour shelf on this marks card. Adjust the colour to black.

Make the chart dual axis and synchronise axis.

Hide the axis, remove all gridlines & row/column dividers. Format the months to be abbreviated to the first letter. Right click on the Order Date label at the top and hide field labels for columns. Set the sheet to Entire View. Name the sheet Bar.

Building the KPI

On a new sheet add Current Year to Text. Adjust the format (size & colour of font) and align middle centre. Set the sheet to Entire View. Name the sheet KPI.

Building the % Change Indicator

Firstly we need to capture the value of the % change in sales

Sales Performance % Change

(SUM([Current Year]) – SUM([Prior Year]))/ SUM([Prior Year])

Use custom formatting to format as ▲ 0.0%;▼ 0.0%

On a new sheet, add Sales Performance % Change and Prior Year to Text. Change the mark type to square and increase the size to as large as possible. Set to Entire View. Adjust the font size of the text to match the display and align middle centre. Add Sales Performance to Colour. Name the sheet % Change

Identifying the data to filter

The final sheet we need is one to serve the title of the Viz in Tooltip (ViT). It includes references to information related to the mark selected on the scatter plot, namely the Category, the Sub-Category and the Maufacturer. Before building this title sheet though, we need to understand how we’re going to identify the mark selected so we can filter other sheets based on it.

Typically, for most use cases, when you add a worksheet to the tooltip of another sheet, you want to ‘filter’ what’s displayed in the ViT based on the mark you’re hovering/clicking on.

So by default, when you add a worksheet via the Viz in Tooltip functionality (see Tableau KB here for more info), the markup that’s automatically added looks like below

<Sheet name=”My ViT Sheet” maxwidth=”300″ maxheight=”300″ filter=”<All Fields>”>

where the filter property is set to <All Fields> which is the instruction to pass information from the ‘parent’ sheet through to the ViT sheet.

For this challenge however, when a user first hovers on a mark on the scatter plot (the parent sheet), the information displayed in the Viz in Tooltip (ViT) sheets is for the whole unfiltered data set (ie display information related to the Current Year and Prior Year Sales across all manufacturers, sub-categories & categories). But once selected (clicked on) the information displayed in the ViT should be filtered just to that mark.

For this to work, we can’t use the filter property of the ViT markup. That property needs to be set to “” to ensure the resulting sheets aren’t filtered ‘on hover’. So we need another way to drive the filtering behaviour.

We need to use sets.

We’re going to use sets to capture the Manufacturer, Sub-Category and Category of the mark that is clicked on. So to start, right click on Manufacturer > Create > Set and select all values.

Manufacturer Set

Repeat the same steps to create a Category Set and a Sub-Category Set.

Create a dashboard and add the Scatter sheet to the dashboard. Then create dashboard set actions (Dashboard Menu > Actions > Add Action > Change Set Values)

Select Manufacturer

On select of the Scatter sheet on the dashboard, target the Manufacturer Set, assigning values to the set when the action is initiated, and adding all values to the set when the action is cleared.

Note – ‘assign value to set’ will replace any values already in the set (ie all values) with the relevant value based on the selection made, whereas ‘add values to set’ just appends the selected value to the values already in the set.

Repeat the above steps to create set actions for the Category Set and the Sub-Category Set

Navigate to the Bar sheet. Add Category Set, Sub-Category Set and Manufacturer Set to the Filter shelf, and click on each pill and Show Set to list the sets and their selected values on the left hand side (this is just so you can see what’s going on).

Initially you can see all values in all the sets are selected. Now navigate back to the dashboard and click on a single mark. Then come back to the bar sheet and check the results…

You should see a change to the bars as they are now being filtered by only the values which have been assigned to the set via the ‘on click’ action.

Building the title sheet

Now we have the sets established, we can build on these to generate the information needed for the title sheet.

To start with, we need to understand how many values have been captured in each set.

Count Categories

COUNTD([Category])

Count Sub-Categories

COUNTD([Sub-Category])

Count Manufacturers

COUNTD([Manufacturer])

Then we need to build up a title based on what’s been selected

Title

IF [Count Manufacturers] = 1 THEN MIN([Manufacturer])
ELSEIF [Count Sub-Categories] = 1 THEN ‘All ‘ + MIN([Sub-Category])
ELSEIF [Count Categories] = 1 THEN ‘All ‘ + MIN([Category])
ELSE ‘All Manufacturers’
END

and a sub title

Sub Title

IF [Count Manufacturers] = 1 AND [Count Sub-Categories] = 1 AND [Count Categories] = 1 THEN ‘(‘ + MIN([Category]) + ‘ > ‘ + MIN([Sub-Category]) + ‘)’
ELSE ‘* ‘ + STR([Count Manufacturers]) + ‘ Manufacturers across ‘ + STR([Count Sub-Categories]) + ‘ Sub-Categories’
END

On a new sheet, add Title and Sub Title to Text. Format the background of the worksheet to be orange, set to Entire View, then adjust the text and font format and align top left. Name the sheet Title.

Now navigate back to the Bar sheet, and for each of the fields in the Filter shelf (the set ones), make them apply to selected worksheets, KPI, % Change, and Title

As a result, across all 4 sheets (not the Scatter one), you should have the 3 set fields as filters with the ‘multiple worksheet’ symbol indicating a shared filter.

If you go back to the dashboard and click on a mark then check the Title sheet, the information displayed should update.

Building the Viz In Tooltip

Now we have (most of) the components we need, let’s start to put together the actual ViT.

On the Scatter sheet, click on the Tooltip button to open the Edit Tooltip dialog.

Start by deleting all the text.

Then from the toolbar, click Insert > Sheets > Title to add the Title sheet to the tooltip. You should have something like

The key to getting the tooltip to display ‘nicely’ is to consider the height and widths, and align the markup text. Sometimes this does take a bit of trial & error and can also look differently when published to Tableau Public.

Adjust the above, so the maxwidth =500 and maxheight = 100, filter = “” and the whole line of text is centred.

Then add the other 3 sheets, using carriage returns to add space between the sheets as required, and adjusting the heights and widths.

If you go back to the dashboard and hover on a mark, you should see the display below for All Manufacturers

and if you then click on a mark, the display should adjust to filter

Making the ViT interactive

Edit the Tooltip on the Scatter sheet, and add a section at the bottom that references the Category , Sub-Category and Manufacturer fields (add via the Insert menu again). Style the font as you wish

Now if you go back to the dashboard and click on a mark, you can then also click on one of the links added at the bottom. In this instance I clicked on the Chairs link and all the marks in the scatter plot related to chairs were highlighted and the ViT data all updated to show the values associated to the Chairs Sub-Category

This is happening ‘automatically’ due to the fact the Allow selection by category option on the Tooltip is checked. This is a feature (along with Include command buttons) I personally often switch off.

Now ideally, we’d be finished at this point, but we just need to add a final feature, due to the fact that some Manufacturers exist across multiple Sub-Categories. For example, below while I have clicked a mark that is related to the Global Manufacturer in Chairs, clicking Global in the links at the bottom highlight all the Global Manufacturers across all Sub-Categories, so we can’t get back to seeing the information just about the selected mark.

Adding the ‘Current Mark’ selection

We need to capture the ‘product hierarchy’ for each mark into a single field

Category | Sub Cat | Manu

[Category] + ‘|’ + [Sub-Category] + ‘|’ + [Manufacturer]

Add this to the Detail shelf of the Scatter sheet.

We will need a parameter to then capture the ‘product hierarchy’ for the selected mark

pSelectedMarkIdentifier

string parameter set to ”” ie empty string

On the dashboard, add a parameter action

Identify Current Mark

On hover of the scatter sheet on the dashboard, set the pSelectedMarkIdentifier parameter to the value stored in the Category | Sub Cat | Manu field. Keep the current value when the selection is cleared.

Finally, we need to have a link to select in the tooltip, so we need

Current Mark Identifier

IF [pSelectedMarkIdentifier] = [Category | Sub Cat | Manu] THEN ‘Current Mark’
ELSE ”
END

Add this to the Detail shelf of the Scatter sheet, and then update the Tooltip and add a reference to the Current Mark Identifier field

If you now go back to the dashboard and test by clicking on a mark associated to the Global Manufacturer, you should be able to click on Current Mark using the link in tooltip after clicking other links, and get back to what you would have seen in the tooltip when you first clicked on the mark.

It should just now be a case of sorting out the layout on the dashboard.

Congratulations on getting this far! My published viz is here.

Happy vizzin’!

Donna

Can you build a scatterbox?

Donna ColesLeave a comment

I’m back from my holibobs, so back to solving #WOW2023 challenges and writing up the solutions – it’s tough to get back into things after a couple of weeks of sunshine and cocktails!

Anyway, this week, Sean set this challenge from Felicia Styler to build a scatterplot / heat map combo chart, affectionally termed the ‘scatterbox’.

Phew! This took some thinking… I certainly wasn’t gently eased back into a challenge!

Modelling the data

We were given a hint that the data needed to be unioned to build this viz. I connected to the Sample-Superstore.xls file shipped with 2023.2 instance of Tableau Desktop. After adding the Orders sheet to the canvas, I then added another instance, dragging the second instance until the Union option appeared to drop it on.

The union basically means the rows in the Orders data set are all duplicated, but an additional column called Table Name gets automatically added

This field contains the value Orders and Orders1 which provides the distinction between the duplicated fields caused by the union. It is this field that will be used to determine which data is used to build the scatter plot and which to build the heat map.

Building out the calculated fields

Let’s start just by seeing how the data looks with the measures we care about.

Onto a sheet add Product ID, Product Name and Table Name to Rows (Note – there are multiple Product Names with the same Product ID, so I’m treating the combination as a unique product). Then add Quantity to Text. The drag Discount and drop onto the table when it says ‘Show Me’, which should automatically add Measure Name/Measure Values into the view. Aggregate Discount to AVG. We can see that we’re getting the same values for each Table Name, which is expected.

When plotting the scatter plot, we’re plotting at the Product level, so the values above is what we’ll want to plot. But when building the heatmap, we need to ‘bin’ the values.

For the Quantity, we’re grouping into bins of size 10, where if the Quantity is from 0-9 the bin value is 0, 10-19, the bin value is 10 etc.

Quantity Bin

FLOOR({FIXED [Product ID], [Product Name], [Table Name]: SUM([Quantity])}/10) * 10

The LoD (the bit between the {} is returning the same values listed above, but we’re using an LoD, as when we build the heat map, we don’t want the Product fields in the view, but we need to calculate the Quantity values at the product level (ie at a lower level of detail than the view we’ll build). Dividing the value by 10, then allows us to get the FLOOR value, which ’rounds’ the value to the integer of equal or lesser value (ie with FLOOR, 0.9 rounds to 0 rather than 1). Then the result is re-multiplied by 10 to get the bin value.

So if the Quantity is 9, dividing by 10 returns 0.9. Taking the FLOOR of 0.9 gives us 0. Multiplying by 10 returns 0.

But if the Quantity is 27, dividing by 10 returns 2.7. The FLOOR of 2.7 is 2, which when multiplied by 10 is 20.

We apply a similar technique for the Discount bins, which are binned into groups of 0.1 instead.

Discount Bin

FLOOR({FIXED [Product ID], [Product Name], [Table Name]: AVG(Discount)}*10) / 10

Add these into the table to sense check the results are as expected.

Next we’re going to determine the values we want based on whether we’re building the scatter or the heat map.

Qty – Scatter

IF [Table Name] = ‘Orders’ THEN {FIXED [Product ID], [Product Name], [Table Name]: SUM([Quantity])} END

ie only return the Quantity value for the data from the Orders table and nothing for the data from the Orders1 table.

Qty – Heatmap

IF [Table Name] = ‘Orders1’ THEN [Quantity Bin] + 5 END

so, this time, we’re only returning data for the Orders1 table and nothing for the Orders table. But we’re also adjusting the value by 5. This is because by default, when using the square mark type which we’ll use for the heatmap, the centre of the square is positioned at the plot point. So if the square is plotted at 10, the vertical edges of the square will be above and below 10. However, we need the square to be centred between the bin range points, so we shift the plot point by half of the bin size (ie 5).

Adding these into the table, and aggregating to AVG we can see how these values are behaving.

As we’re building a dual axis, one of the axis will need to be combined within a single measure, so we create

Qty to Plot

IF ([Table Name]) = ‘Orders’ THEN ([Qty – Scatter]) ELSE ([Qty – Heatmap]) END

Now we move onto the Discount values, which we apply similar logic to

Discount – Scatter

IF [Table Name] = ‘Orders’ THEN {FIXED [Product ID], [Product Name],[Table Name]: AVG([Discount])} END

Discount – Heatmap

IF [Table Name] = ‘Orders1’ THEN [Discount Bin] + 0.05 END

We’ll need is to be able to compute the number of unique products to colour the heatmap by. As mentioned earlier, I’m determining a unique product based on the combination of Product Id and Product Name. To count these we first need

Product ID & Name

[Product ID] + ‘-‘ + [Product Name]

and then we can create

Count Products

COUNTD([Product ID & Name])

The final calculations we need are required for the heatmap tooltips and define the range of the bins.

Qty Range Min

[Qty To Plot] – 5

Qty Range Max

[Qty To Plot] + 5

Discount Range Min

[Discount – Heatmap] – 0.05

Discount Range Max

[Discount – Heatmap] + 0.05

Now we can build the viz

Building the Scatterbox

On a new sheet, add Qty to Plot to Columns and change to be a dimension (so not aggregated to SUM) and Discount – Scatter (set to AVG) to Rows. Add Product ID and Product Name to Detail. Change the mark type to Circle and adjust the size. Adjust the Colour and reduce the opacity (I used #4a9aab at 50%)

Adjust the Tooltip.

Then add Discount – Heatmap to Rows. This creates a 2nd marks card. Change to be a dimension, and change the mark type to square. Remove Product ID and Product Name from the Detail shelf

Add Count Products to Colour and ensure the opacity is 100%. Adjust the sequential colour palette to suit and set the end of the range to be fixed to 300

Add Qty Range Min, Qty Range Max, Discount Range Min, Discount Range Max to the Tooltip shelf of the heatmap marks card. Set all to aggregate to AVG and adjust tooltip to suit.

Then make the chart dual axis and synchronise axis. Increase the size of the square heat map marks (note don’t worry how these look at this point, the layout will adjust when added to the dashboard. Right click on the Discount – Heatmap axis on the right and move marks to back. Hide that axis too.

Edit the Qty to Plot axis so the tick marks are fixed to increment every 10 units.

Adjust axis titles, remove row/column dividers and hide the null indicator.

Then add the sheet to an 800 by 800 sized dashboard. You will need to make tweaks to the padding and potentially sizing of the heat map marks again to get the squares to position centrally with white surround. I added inner padding of 60px to the left & right of the chart on the dashboard, to help make the chart itself squarer.

My published viz is here .

Happy vizzin’!

Donna

Can you build a dynamic drill down scatterplot in one view?

Donna Coles1 Comment

Retro month continues, with Kyle setting this challenge to recreate Andy Kriebel’s WOW challenge from 2017. A lot has moved on with the product since 2017 and this is a great example of how it can be simplified.

I completed the original challenge (see here) and was having a look to refresh myself… boy! it took a LOT more effort – sometimes I surprise myself that I managed it!

Now that we have parameters and parameter actions, the solution is WAY more simpler.

So let’s crack on…

As alluded to above, we’re going to need a parameter which is going to store the name of the state ‘on click’

pSelectedState

string parameter defaulted to ” (ie empty string)

We also need to display either the name of a State or a City dependent on the value of this parameter

Display Name

IF [pSelectedState] = ” THEN [State]
ELSE [City]
END

Pop these into a tabular view with Sales and Profit and show the pSelectedState parameter so we can test things out.

When the pSelectedState is empty, a row is displayed per State

but when pSelectedState contains the name of a State (or any text to be honest), a row is displayed per City (note all Cities are displayed, at this point, not just those for the State).

To restrict the list of Cities just to those that match the State in the pSelectedState parameter, we need

Records to Filter

[pSelectedState] = ” OR [pSelectedState] = [State]

Add this to the Filter shelf and set to True. Now the list should be restricted to the Cities in the State.

So lets’ start to build the basic viz.

Set the pSelectedState parameter to empty, then add Sales to Columns, Profit to Rows and Display Name to Text. Add Records to Filter To Filter and set to True. Change the mark type to Circle.

Create a new field

Profit Ratio

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

format to % with 1 dp and then add this to the Colour shelf.

Add this sheet to a dashboard, then add a dashboard parameter action

Set State

on select of a mark on the scatter plot chart, set the pSelectedState parameter with the value from the Display Name field.

If we now click on a state, the cities should be displayed instead – great! But if we now click a city, we don’t get what we want – boo! This is because the selection of a City has passed the name of the City which is stored in the Display Name field into the parameter, so the scatter is trying to display records relating to a State = City Name which doesn’t exist.

To resolve this, we need to pass a different field into the parameter action

Drill Value

IF [pSelectedState] = ” THEN [State] ELSE ” END

Add this to the Detail shelf of the Scatter plot viz, then update the dashboard action to pass this field into the pSelectedState parameter instead

Reset the pSelectedState parameter to empty string, and then test again – clicking on a state and then clicking on a city should get you back to the states.

And that’s the core functionality achieved with 1 parameter, and 2 calculated fields!

We just need some additional fields to provide the relevant display for the title & sub title

Title text

IF [pSelectedState] = ” THEN ‘by State’ ELSE ‘for ‘ + [pSelectedState] END

Subtitle

IF [pSelectedState] = ” THEN ‘Click a State to drill down to City level’
ELSE ‘Click a City to drill up to State level’
END

Add these to the Detail shelf of the scatter viz, and then update the title of the sheet to reference the fields

Update the Tooltip and adjust the size of the axis fonts, and tidy up the dashboard layout, and you should be good to go!. My published viz is here.

Happy vizzin’!

Donna

Can you use dynamic axis titles?

Donna Coles1 Comment

Lorna used this week’s challenge to showcase a new feature in Tableau v2023.1 – dynamic axis titles. As you can expect, you’ll therefore need this version of Desktop (or later if you’re reading this in the future ;-)) to complete the challenge.

Modelling the data

Download the file Lorna provided and connect to the 2023 sheet. Lorna hinted that a pivot would help, so in the data source canvas, multi-select all the measures (ctrl-click each column – there are several) then right-click and Pivot.

Rename Pivot Field Names to Measure and rename Pivot Field Values to Value.

Building the Basic Scatter Plot

We need 2 parameters to control the selection of the measures we want to display in the scatter plot. Right click on Measure > Create > Parameter

pMeasure1

string parameter defaulted to Metres, and all the possible other values should be listed

Repeat the steps again to create pMeasure2 which is defaulted to Tackles

To determine the value to plot on the axes based on the selections from the parameters we need

X-Axis Value

IF [pMeasure1] = [Measure] THEN
IF [Value] <> 0 THEN [Value] END
END

Note – the additional nested IF was added as I discovered while playing with the Lorna’s solution that marks didn’t display when the value was zero.

Similarly we need

Y-Axis Value

IF [pMeasure2] = [Measure] THEN
IF [Value] <> 0 THEN [Value]END
END

Add X-Axis to Columns, Y-Axis to Rows, Team Name to Rows and Name to Detail. This will give a basic scatter plot.

Change the mark type to circle, adjust the colour, and reduce the opacity to around 70%. Add a grey border to the circles.

The size is based on the number of games the player has played, so we need

Count Games

COUNTD([Game ID])

Add this to the Size shelf.

Adjust the Tooltip so it references the parameters and the other relevant fields

To change the axis titles, edit the x-axis (right click axis > edit axis) and from the menu arrow next to the word ‘custom’, select the pMeasure1 option.

Repeat the same for the y-axis, but select pMeasure2 instead.

Making the small multiples

For this we need to define which row and column each Team Name should sit in. As we’ve only got 12 teams to work with, and that number is static, and we know we’re working with a 3×4 grid, I’m going to ‘hardcode’ this a bit rather than use more dynamic calculations.

To see what’s going on, on a new sheet add Team Name to Rows. Then create a new field

INDEX

INDEX()

and add this to Rows and convert to discrete. It should provider a counter from 1 to 12 for each Team Name.

Based off this INDEX value we’ll work out which row and colum each team will sit.

Column

(INDEX()-1) % 3

Row

IF [INDEX]<=3 THEN 1
ELSEIF [INDEX]<=6 THEN 2
ELSEIF [INDEX]<=9 THEN 3
ELSE 4
END

Add these to the table as blue discrete pills

Back onto the scatter plot sheet, add Row to Rows as a blue discrete pill, add Column to Columns as a discrete pill, and move Team Name to Detail. Modify the table calculation settings of both the Row and the Column pill so that the calculation is computed using Team Name and Name (in that order) and at the level of Team Name

Hide the Column and Row pills (uncheck Show Header)

Adding the Team Name title

Create a new field

Ref Line

WINDOW_MAX(SUM([Y-Axis Value])) * 1.1

and add this to the Rows. This will create a second marks card. Set the table calcuation of the Ref Line pill to compute using Name and Team Name (in that order).

On the Ref Line marks card, remove the pill from the Size shelf, change the mark type to gantt bar, and reduce the size to the smallest possible, set the opacity to 0%, and border to none.

Add Team Name to the Label shelf, then set to label min/max values, by the X-Axis Value field and Label Minimum value only.

Each team name should only be displayed once. Edit the text of the label and add a few spaces to shift the label across and align it better.

Edit the Tooltip of this marks card, and delete all the text. Now make the chart dual axis and synchronise the axis. Then remove Measure Names from the All marks card, and hide the right hand axis.

Finally remove the 0 lines from displaying, and hide the nulls indicator (right click). Add the chart to a dashboard, and position the parameters as floating objects within the text of the title. I just used spaces within the text to leave room for where I wanted to place the parameterss.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you use Image Role & Dynamic Zone Visibility?

Donna Coles2 Comments

New year means only one thing – a change to the WorkoutWednesday hashtag! We’re now #WOW2023, and it was Lorna’s turn to kick the next year of challenges off with a focus on the latest Tableau features. This challenge uses the image role function introduced in 2022.4 and the dynamic role visibility feature introduced in 2022.3 (so you need to make sure you’re using at least 2022.4 to complete this).

Setting up the data source

Lorna provided an excel based data sheet which I downloaded. It contains several tabs, but the two you need to relate are Player Stats and Player Positions, relating as Lorna describes on both the Game ID and the Player Name

The data needs to be filtered to just the men’s competition, so I did this by adding a data source filter (right click on the data source -> Edit Data Source Filter) where Comp = Rugby League World Cup. Doing this meant I didn’t have to worry about adding fields to the Filter shelf at all.

Building the scatter plot

For the basic chart, simply add All Run Metres to Columns and Passes to Rows, then add Game ID and Name to the Detail shelf.

We need to identify which player and game is selected when a user clicks, so we need parameters to capture these.

pSelectedGameID

integer parameter defaulted to 0

pSelectedPlayer

string parameter defaulted to nothing/empty string/ ”

Show these parameters on the canvas, and then manually enter Brandon Smith and 21.

We now need calculated fields to reference the parameter values

Is Selected Player

[Name] = [pSelectedPlayer]

Is Selected Game

[Game ID] = [pSelectedGameID]

Both return a boolean True / False value, and we can then determine which combination of player/game each mark represents, which we need in order to colour the marks.

Colour : Scatter

IF [Is Selected Game] AND [Is Selected Player] THEN ‘Both’
ELSEIF [Is Selected Player] THEN ‘Player’
ELSEIF [Is Selected Game] THEN ‘Game’
ELSE ‘Neither’
END

Add this field to the Colour shelf, change the mark type to Circle and adjust the colours accordingly. Manually sort the order of the values so that the light grey ‘Neither’ option is listed last (which means it will always be underneath any other mark).

While not stated in the requirements, the marks for the selected player are larger than the others, so add Is Selected Player to the Size shelf, and adjust the size range to suit.

Adjust the tooltip and name the sheet Scatter.

Building the Bar Chart

On a new sheet, add Player Image and Game ID to Rows and All Run Metres and Passes to Columns. Add Is Selected Player to Filter and set to True. Edit the title of the sheet so it references the pSelectedPlayer parameter, and align centre.

Set the Player Image field to use the actual image of the player stored at the URL, by clicking on the ABC datatype icon to the left of the field in the data pane, and selecting Image Role -> URL

This will change the data type icon and update the view to show the actual player image

Add Is Selected Game to the Colour shelf on the All marks card and adjust accordingly. Remove the tooltips.

We need to show the axis titles at the top, rather than bottom. To do this add another instance of the All Run Metres field to the right of the existing one. Make dual axis and synchronise axis. Repeat with the Passes field by adding another instance to the right of the existing one and making dual axis again.

Set the mark type on the All marks card back to bar.

Right click on the All Run Metres bottom axis, remove the title, and set the tick marks to none. Repeat for the Passes bottom axis.

Right click on the All Run Metres top axis, and just set the tick marks to none. Again repeat for the Passes top axis.

Adjust the font of both axis title (right click axis -> format; I just set to Tableau Book) and then manually decrease the height of the axis.

Finally click the Label button on the All marks card and check the Show mark labels check box.

Then remove all column and row divider lines.

Adding the interactivity

On a dashboard, add a Vertical Layout Container, then add the bar chart into it and then add the scatter plot underneath. Remove the container that contains all the legends and parameters – we don’t need to show any of these. Hide the title of the scatter plot.

Add a dashboard parameter action to set the pSelectedPlayer parameter on select of a circle on the scatter plot. Set this parameter to pass the Name field into the parameter, and when clearing the selection, set the value to empty string/ nothing / ”

Select Player

Add another similar dashboard parameter action, which sets the pSelectedGame parameter in a similar way. This time, the Game ID field is passed into the parameter which is then set to 0 when the selection is cleared.

Select Game

If you test clicking on the scatter plot, you should now see the bar chart data disappear (although some white space about the height of the title remains) and you’ll also see that the circle selected is ‘highlighted’ compared to all the others.

To resolve the highlighting issue, navigate back to the scatter plot sheet, create a new calculated field called Dummy which just contains the string ‘Dummy’. Add this field to the Detail shelf.

Navigate back to the dashboard, and add a dashboard highlight action, that on select on the scatter plot, targets the same sheet on the same dashboard, but only focus on the Dummy field.

Deselect Marks

To make the whole section where the player bar chart is displayed, completely collapse, we need another parameter

pShowPlayerDetail

boolean parameter defaulted to false

We also need a boolean calculated field called

Show Player Detail

TRUE

Add this to the Detail shelf of the Scatter plot sheet.

Then navigate back to the dashboard, and create another parameter dashboard action, which on select of a circle on the scatter plot, sets the pShowPlayerDetail parameter based on the value from the Show Player Detail field. When the selection is cleared, reset the parameter to False.

Show Player Detail

Finally select the bar chart on the dashboard, then click the Layout tab on the left and side, and tick the Control visibility using value check box, and in the drop down select Parameters – > pShowPlayerDetail.

Now when a circle is clicked on, the bar chart section will completely disappear and the scatter plot will fill up the whole space.

Finalise the dashboard by adding the title and legend. My published viz is here.

Happy vizzin’!

Donna