For this week’s challenge, Yoshi introduced the new 2026.2 feature – the drive time selection tool. As a result you’ll need at least Desktop or Desktop Public v2026.2 to complete it. At the point of writing, it’s not available via Tableau Public web authoring, which also means you can’t interact with my published version on Tableau Public. You’ll need to download it to try it out.
Building the map
After connecting to the grocery store data, we need to define the location of each store to add to the map
Store Location
MAKEPOINT([Latitude],[Longitude])
Add this to the Detail shelf and the set the map background layer (Map menu > background layers) to streets, with a 30% washout. Uncheck all options except the top 3.
Add Object ID to Detail and Storename and Address to Tooltip. Change the mark type to circle and adjust colour as required, reducing opacity to 75% and adding a border. Adjust size as required too.
Create a new location for the Union Station, using the coordinates provided in the requirements
Union Station
MAKEPOINT(38.8977, -77.0063)
Drag this onto the map and drop when the ‘Add a marks layer’ option appears. This will create another marks card.
Change the mark type of this card to shape and select a star shape. Change colour and size. Adjust tooltip to read the word ‘Union Station’.
We now need to isolate the District of Colombia region. I confess, I got so far with this, but couldn’t get the area outside of the map to not be visible, so had to check out Yoshi’s solution.
Connect to a version of Superstore Data. Create a field
DC
IF [State/Province] = ‘District of Columbia’ THEN [State/Province] END
Make sure this field is assigned a geographic role of State/Province (right click field > Geographic role)
Drag DC onto the canvas to create another map layer. Change the mark type to Map. The shape of DC should be displayed.
Reduce the opacity of the colour to 0% and add a border.
Create another field
Not DC
IF [State/Province] <> ‘District of Columbia’ THEN [State/Province] END]
Set this to have a geographic role of State/Province.
Add this to the canvas, and set the mark type to Map.
Set the colour of this to a pale grey at 100% opacity with no border or halo. The move this marks card so it is below the DC one. This should make the border appear as the ‘DC’ card is now ‘on top’. Remove row/column dividers.
Set Disable Selection against both the DC and Not DC marks cards via the context menu against the card name
Adjust the map via zoom to ensure it fills as much as the canvas as possible, then update the map options (Map menu > map options) to ensure you can’t pan/zoom. Map should be pinned into position, and only the selection toolbar should be enabled, which includes the drive time selection tool.
Building the KPI
The number of stores is just the auto-generated count of the dataset. But we’re going to use a Set to capture the stores selected.
Create a set by right clicking o Object ID > Create > Set
Selected Stores
Don’t make any selections initially
Create field
# Selected Stores
SUM(IIF([Selected Stores],1,0))
Add this field and dc-grocery-clean.csv(Count) to Text. Change the mark type to shape, and assign a transparent shape. Set the canvas to Entire View and format the text as required and align the label top left.
Adding the interaction
Add the 2 sheets side by side on a dashboard using layout containers. Create a dashboard set action
Select Stores
On select of the Map sheet, add values to the set Selected Stores. When the selection is cleared, remove all values.
And that should be it. My published viz which you can download is here.
Kyle took inspiration from the 2026 FIFA World Cup for this week’s challenge, looking at how many miles each team need to travel between their base camp and the stadiums where their group matches are being held. He sourced some data which he provided for download.
Modelling the data
The data provided contained 3 sheets. Kyle gave some hints on how he’d used the data, but nothing specific, so I just related the sheets in the following way, to see whether this would be enough.
I related Schedule to Base Camp, but BEFORE I applied the relationship join condition, I pivoted the Schedule data (as Kyle had hinted this may be required). I did this by
Adding Base Camp to canvas
Then adding Schedule to canvas
Then clicking on the Team A and Team B columns in Schedule, and selecting Pivot
The renamed Pivot Names field heading to Team A | B
and renamed Pivot Values field heading to Team1 (I couldn’t call it Team as this field already exists in Base Camp)
I then created a relationship between Base Camp.Team and Schedule.Team1
The add Venues to the canvas a relate to Schedule on the Venue field.
Creating the Map with all locations
For the maps we need to work with spatial data, and need to define the location of each base camp and each venue. The data sets had longitude and latitude fields for both types of location, but for some reason, both the longitude fields were resolving as string fields. So I had to change these to geographic fields by the following steps
right click Base Camp > Longitude, and change data type > number (decimal)
then right click field again , geographic role > latitude
repeat same 2 steps for the Venues > Laitude (Venues) field.
On a new sheet, add Venue Location to Detail, and Venue to Detail. Change the mark type to circle. Adjust the map background layers so only the Base, County/Region Names and State/Province border options are selected (Map menu > background layers)
Create a parameter to store the name of the selected team we want to focus the data for
pTeam
string parameter defaulted to ‘Austria’
Create a field
Is Selected Team?
Team = [pTeam]
Add this to Colour and Size and adjust accordingly. Make sure the ‘true’ is listed first so that the marks for the selected team are ‘on top’ . This will require the Size to ‘be reversed’
Remove all text from the Tooltip
Drag Base Location onto the display and drop it when ‘Add a Marks Layer’ option appears, which will create a 2nd marks card
Drag this card to be beneath the Venues one. Change the mark type to circle. Add Team to Detail. Add Is Selected Team to Colour and Size. Add Training Site and City to Tooltip and update accordingly.
Then click on the context menu of the Venues marks card, and disable selection
Nowt drag Line on to the canvas to make another marks layer. Again move this marks card to the bottom of the list, so it’s beneath the Base Camp marks card. Add Team to Detail and Is Selected Team to Colour. Create a copy of Is Selected Team (right click field and duplicate to create Is Selected Team (copy)) and add this to Size and adjust. I found I needed a copy so I could have different sizes between the circles and the lines Add Training Site and Distance to Tooltip and update the Tooltip accordingly. Remove row & column dividers.
Creating the ‘Team specific’ map
The easiest way I found to do this initially, was just to duplicate the sheet with the above map, and then add Is Selected Team to the Filter shelf, and set to True. This gives us the display we need, but tooltips need changing.
Re-enable the Venues marks card. We’ll need to display information about the match on the venues tooltip, wihich includes details for the teams playing.
As we initially pivoted the data, this information is now across 2 rows, so we need to create fields to capture both the teams on each row. I used FIXED LoDs for this:
Team A
{FIXED [Match]:MIN(IF [Team A | B]=’Team A’ THEN [Team_1] END)}
Team B
{FIXED [Match]:MIN(IF [Team A | B]=’Team B’ THEN [Team_1] END)}
Add these to Tooltip, along with Training Site, Date, Time (Local) and Distance. Format the Date field to “Month, Day Year” format, and custom format the Time (Local) field to h:nn AMPM. The adjust tooltip to suit.
Now do similar to the Line marks card – add Match to the Detail shelf as a blue disaggregated discrete pill, and then add Team A, Team B, Venue, Date and Time(Local) to the Tooltip and update accordingly.
Create the Bar Chart
On a new sheet, add Is Selected Team and Team to Rows and Distance to Columns. Sort descending. Order so Is Selected Team : True is listed first.
Make the rows a little wider. Add Is Selected Team to Colour. Add Team to Label. Adjust Label so the team name is aligned left and coloured in white bold text. Adjust the Tooltip. Add a Reference line to the Distance axis to show per cell the value of the sum of the distance. Don’t show line or tooltip.
Format the reference line, so the numbers are aligned in bold font, middle right
Hide the Is Selected Team, Team and Distance headers & axis (right click pill > uncheck show header). Then remove all row/column dividers and gridline, axis rulers etc.
Finally create 2 fields
True
TRUE
False
FALSE
and add these to the Detail shelf (we’ll need them later to ensure the bar doesn’t highlight when we select values).
Creating the match cards
On a new sheet, add Is Selected Team to Filter and set to true.
We need to identify which match is match 1st, 2nd and 3rd. I did this using the dates, but in hindsight could have used the Match field which just contains an unique number per match… anyway…
Match 1 Date
{FIXED [Team_1] : MIN(Date)}
Match 3 Date
{FIXED [Team_1] : MAX(Date)}
Match No for Team
IF [Date] = [Match 1 Date] THEN ‘Match 1’ ELSEIF [Date] = [Match 3 Date] THEN ‘Match 3’ ELSE ‘Match 2’ END
Add Match No for Team to Filter and set to Match 1.
Chang the mark typeto shape and set to use a transparent shape (see here for more details on this). Then add Match No for Team, Match, Venue, City (Venues), Distance, Date and Time(Local) to Label. Format the date and time field as you did above.
Create a field
Opponent
IIF([Team_1]=[Team A],[Team B],[Team A])
and add this to Label too. Then adjust the label as required, and align middle left. Don’t show tooltips.
Duplicate this sheet and change the filter to select Match 2 for match 2, and then repeat for Match 3 so you have 3 separate sheets for the matches.
Creating the ‘team’ sheet
On a new sheet, add Is Selected Team to Filter and set to True. Set the mark type to shape and use a transparent shape. Add Team, Distance, Training Site and City to Label and adjust and format accordingly, aligning left middle. Don’t show tooltips.
Building the dashboard and adding the interactivity
I set the background of the dashboard to dark grey and then used layout containers to organise the content, using padding and rounded corners to style as required. The layout of my dashboard is pictured below. It will take some time to get this layout just right, and you might find that in Desktop some text doesn’t display but will when published on Public.
Add a parameter dashboard action to change the team when the bar chart is clicked on
Set Team
on select of the bar chart sheet, set the pTeam parameter with the value from the Team field.
To prevent the selected bar from being ‘highlighted’ when clicked, add a dashboard filter action
Deselect Bar
On select of the bar chart on the dashboard, target the bar chart sheet directly, setting fields true = false.
For this week’s community challenge, we’re recreating radar charts using fellow Tableau Ambassador and Visionary, Johan de Groot‘s, map layers technique, which he has blogged about here.
Modelling the data
I provided 3 sets of data in one excel workbook
Gridlines : a basic template to help build the radial gridlines
Engagement Survey Data – Filled : for use with the filled area chart radar display
Engagement Survey Data – Line: for use with the non-filled line chart radar display
The Engagement Survey Data – Line data contains an extra ‘Dummy’ theme which has values that exactly match the values associated to Theme ID 1. This helps ensure the lines ‘join up’ once plotted.
Each Engagement Survey Data needs to be related to the Gridlines data by a custom calculation of 1 = 1
Building the Filled Area Radar Chart
Model the data as described above using the Engagement Survey Data – Filled data set.
Create a new parameter
pArea
string parameter, which can be populated as a list using the Add values from option, and then removing the Whole Company and Benchmark options. Default to Finance
Drag Point to the Detail shelf. Change the mark type to Polygon and add Theme ID to the Path shelf.
Set opacity on Colour to 30% and add a black border. Set the the Background Layers (Maps > Background Layers) to show nothing: untick all options. Also remove all map options ( (Maps > Map Options -> uncheck all options.) Remove row/column dividers.
Create a new field
Area to Display
IF Area = ‘Benchmark’ OR [Area] = ‘Whole Company’ OR [Area] = [pArea] THEN [Area] END
and add to Colour. Exclude the Null option that display (right click -> exclude; this will automatically add the pill to the Filter shelf). Adjust colours as required.
Add this to the view as a new marks layer (drag onto canvas and drop when you get the option displayed)
This will create a new marks card – add Theme ID to the Detail shelf.
Adjust the size and colour of the spokes as required.
Create a new field
Outer Points
MAKEPOINT(cos([Angle]), Sin([Angle]))
Add this as another marks layer. Change the mark type to circle. Reduce the opacity to 0% and size to as small as possible. Add Themes to the Label shelf.
(If you wish, you can format the text to wrap a bit, by creating
Label – Theme formatted
REPLACE([Themes],” “, “ “)
and putting this on the Label shelf instead
You can adjust the labels using the label alignment options, but you may need to manually move the labels to get them positioned in an acceptable place.
Create a new field
Radar Labels
IF [Theme ID]= 1 AND [Position] <=10 THEN MAKEPOINT(cos([Angle])*[Position], Sin([Angle]) * [Position]/10) END
and add as another marks layer and change mark type to circle. Again reduce opacity to 0% and size as small as possible.
Create a new field
Value Axis
IF [Position]%2 = 0 THEN [Position] * 10 END
and add to the Label field of the Radar Labels marks card as a Continuous dimension (unaggregated green pill)
To add gridlines, create
Grid Lines
IF [Position]<=10 THEN MAKEPOINT(COS([Angle])*[Position]/10, SIN([Angle]) * [Position]/10) END
and add as another marks layer. Change the mark type to polygon, and add Position to Detail and Theme ID to Path. Set the colour to pale grey at 5% opacity and add a darker grey border.
Finally add Value to the Tooltip of the original Point map layer marks card and adjust tooltip. Then make all the other may layers ‘disabled’ so the can’t be clicked on.
Show the pArea parameter and change the values to see the areas change. This is now the core ‘filled’ radar, which you can add to a dashboard.
If you duplicate the sheet for the radar chart you’ve just made, and change the original Points marks card from Polygon to Line, you will find that the lines don’t join up. This is why we need to adjust the source data to introduce a ‘Dummy’ theme with the values that match the first theme.
Model the data as described above using the Engagement Survey Data – Line data set.
Create all the same fields as detailed above, but some are adjusted as follows :
There is a slight difference here, in that 1 is subtracted from the count of themes, as we don’t want to be counting the ‘Dummy’ theme, otherwise we get an extra ‘spoke’.
Label – Theme Formatted
IF [Theme ID]<> 10 THEN REPLACE([Themes],” “, “ “) END
adjusted to only return labels that aren’t related to the Dummy theme.
When you add the Point field as the first map layer, change the mark type to line instead of polygon to get the display below.
Then continue to build as described above. Adjust the colourings and amount of opacity as you see fit.
For the bikes, we need to know how much charge it has left
Charge %
SUM([Current Range Meters]) / SUM([Max Range Meters])
format this to % with 0 dp
Add Bike Location to a new sheet. Add Bike Id to Detail and change the mark type to circle. Add Charge % to Colour, and adjust the colour palette as required, and also edit so it is fixed to range from 0 to 1 (ie 0-100%).
Adjust the opacity of the colour to around 70% and add a pale grey border around the circles. Click on the 1 null indicator to the bottom left and select filter data to exclude that record from the display.
Select Map > Map Options from the menu and uncheck all the values to prevent the map from bing manually zoomed in/ changed. Then select Map >Background Layers from the menu, and set the Style to dark and click the Streets,Highways etc map option.
Drag Station Location onto the canvas and drop when the Add a Marks Layer option appears. Add Name the Detail shelf and Num Bikes Available to Colour. Change the mark type to square and adjust the colour palette as required and fix to range from 0 to 50.
Adjust the opacity of the colour to around 70% and add a pale grey border around the circles. Then move the stations marks card so it is listed below the bikes marks card. This means the bikes are displayed ‘on top’
Identifying the selected bike
Create 3 parameters
pSelectedBike
string parameter defaulted to <empty string>
pLat
float parameter, defaulted to 38.9358 (this is the central point mentioned in the requirements)
pLon
float parameter defaulted to -77.1069 (this is the central point mentioned in the requirements)
Note – originally I planned to just capture the ID of the selected bike then determine the lat & lon of that bike using a FIXED LOD to in turn determine the selected bike’s location, but that really hampered the performance, so I just used the parameter action to capture the required Lat & Lon directly
Show the 3 parameters on the sheet.
Update the 3 entries with a Bike Id and its associated Lat & Lon values (eg Bike Id = 8ec444bc696c2c8837ca0dcad39de819 , Lat = 38.8965 , Lon = -77.0334)
We need to identify the selected bike on the map
Is Selected Bike
[Bike Id]=[pSelectedBike]
Add this to the Size shelf on the bikes marks card. Adjust the sizes so True is listed before False and the sizes are therefore reversed. You may need to adjust the slider on the Size shelf too.
Zooming in to the selected bike
Create a new field
Selected Bike Location
MAKEPOINT([pLat],[pLon])
then create a buffer of 2000m around this (the requirements state 1000m, but I found that there were free bikes that were over 1000m from their nearest station, and if they were clicked on in the grid, the map didn’t display).
Selected Bike Buffer
BUFFER([Selected Bike Location],2000,’m’)
We want the map to ‘zoom’ into this buffer area if a bike has been selected, but show all bikes & stations so we need
Within 2000m
([pSelectedBike]=”) OR ((INTERSECTS([Bike Location],[Selected Bike Buffer])) AND (INTERSECTS([Station Location],[Selected Bike Buffer])))
Add this to the Filter shelf and select True
The map should zoom in, and the bike selected should be quite central to the display (the middle point of the buffer). To verify this, create
Buffer for Zoom
IF [pSelectedBike] <> ” THEN [Selected Bike Buffer] END
Add this to the map as another marks layer, and the circular buffer ‘zone’ will be displayed (we’ll keep this here for now for validation purposes).
Reset the pSelectedBike to <empty> and set pLat and pLon back to their default values – the buffer circle disappears.
Kyle hinted that we need to make sure that on ‘zooming out’ the display should be centred on the default values. To ensure this, we want to create a buffer around that central point that encapsulates all the stations and bikes. So we need
Default Location
MAKEPOINT(38.9358,-77.1069)
Default Buffer
BUFFER([Default Location],30,’km’)
Choosing a 30km buffer was just trial and error.
Now update the Buffer for Zoom field to
IF [pSelectedBike] = ” // then we’re in the default ‘show all’ view THEN [Default Buffer] ELSE [Selected Bike Buffer] END
A buffer zone for the whole display is now shown
Ensure the buffer marks card is displayed at the bottom, reduce the opacity of the colour to 0 and remove any border to make the circle disappear. Then click on the eye symbol to the left of the marks card name to make the map layer disabled, so it doesn’t show up on hover.
Finally adjust the Tooltips on the relevant marks cards and then name the sheet Map or similar.
Building the Bike Selector Grid
To build this we will need to identify the closest station to each bike. First we need the distance between each bike and each station
Distance Bike to Station
DISTANCE([Bike Location], [Station Location],’m’)
and then we can create
Distance Bike to Closest Station
{FIXED [Bike Id]:MIN([Distance Bike to Station])}
On a new sheet add Bike Id to Detail and Distance Bike to Closet Station to Colour. Change the mark type to square. Sort the Bike Id by the field Distance Bike to Closet Station ascending.
Add Lat and Lon to the Detail shelf, and update the Tooltip as required. Name the sheet Bike Grid or similar.
Adding the interactivity
Add the two sheets onto a dahsboard, then create 3 dashboard parameter actions
Select Bike
On select of the Bike Grid sheet, set the pSelectedBike parameter with the value from the Bike Id field. When the selection is cleared, reset to <empty string>
Set Bike Lat
On select of the Bike Grid sheet, set the pLat parameter with the value from the Lat field. When the selection is cleared, reset to 38.9358
Set Bike Lon
On select of the Bike Grid sheet, set the pLon parameter with the value from the Lon field. When the selection is cleared, reset to -77.1069
And with that, hopefully the map should zoom in and out as required, albeit a bit slowly… (gif below recorded on Desktop)
For this week’s challenge, we’re using the data from a previous challenge and visualising it using Sam Parson’s ‘satellite’ chart idea – see here.
Modelling the data
Connect to the Food Self-Sufficiency csv file then add another connection to the Circle_Scaffold excel file. Relate the two together using a relationship calculation where 1 = 1 (ie relate every row in left hand data source to every row in the right hand).
Since we only care about data from FY2019, add a data source filter to set Fiscal Year = FY2019.
This saves us from having to apply that filter to each sheet we build.
Building a single spiral
On a new sheet, add Prefecture to the Filter shelf and select Akita-ken (which is near the top of the list and has a % value over 200%).
Create a parameter
pMinRadius
integer parameter defaulted to 500
To build the spiral, we need to plot a mark for every percentage point from 0 up to the Food Self-Sufficiency % value. For this we will need to determine an (x,y) coordinate value for each point, which will require some trigonometry, based on the diagram below
For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. As we are building a spiral, the radius of the circle will increase as we move around each percentage point, so we need
Radius
[pMinRadius]+[Path Percent Point]
We then need to determine the angle θ. As a circle is 360°, and a complete circle represents 100%, then 1% is 360/100, so the angle (in radians) for each % point plotted round the circle can be calculated as
Angle
RADIANS([Path Percent Point] * (360/100))
X
(SIN([Angle]) * [Radius])/360
Y
(COS([Angle]) * [Radius])/360
Now create the point
Spiral Point
MAKEPOINT([X],[Y])
Note– the X & Y values are divided by 360 due to the spiral we’re building and the increasing radius when displayed using map layers. If we were just plotting X against Y and not using map layers, this wouldn’t be required.
Double click on Spiral Point to automatically add Longitude and Latitude fields to the sheet.
Change the mark type to line and then add Path Percent Point to the Path shelf.
Add Prefecture to the Detail shelf, as it’ll be needed later when we build the trellis and remove the filter.
At this point, the spiral is showing 3 complete revolutions, as the data in the circle_scaffold data set contains info for up to 300%. We need to restrict it so we only show up to the Self-sufficiency ratio… so we need
Filter Percent PointDisplayed
[Path Percent Point] <=[Self-sufficiency ratio for food in calorie base 【%】]
Add this to Filter shelf and set to True.
We now want to colour the spiral based on the percentage point associated to each mark plotted being <100%, between 100% & 199% or >= 200%, so we can use
Colour – Spiral
FLOOR([Path Percent Point]/100)
which will return an integer of 0, 1, or 2
Change this field to be discrete and then add it to the colour shelf and adjust colours accordingly.
Now obviously, you might be thinking things aren’t quite right – we’re not starting at the top and rotating differently. Simply pressing the swap rows and columns icon in the menu bar will resolve this, but if we do that too early, we lose the ability to add map layers, so leave as is for now.
Add the label map layer
Create a 0 point
Zero
MAKEPOINT(0,0)
Drag this onto the canvas and drop when the Add a Marks Layer option appears
This has the effect of creating a 2nd marks card
and now we have this, we can press the swap rows and columns icon in the menu bar to get the start of the spiral at X=0
Change the mark type to circle and add Self-sufficiency ratio… and Prefecture to the Label shelf. Adjust the font style and align centrally. Set the colour of the circle to white and increase the size. Move the Zero marks card to be below the Spiral Point marks card.
Rename the marks cards if you wish.
Add the starting point map layer
We need to create a point for the start of each line which is at the 0% mark
Start Point
MAKEPOINT((IF [Path Percent Point] = 0 THEN [X] END), (IF [Path Percent Point] = 0 THEN [Y] END))
Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Colour the mark to the same base colour you used for your <100% range and remove the border. Rename the marks card to 3.Start Point
Add the end point map layers
Create a new point to represent the end of each line
End Point
MAKEPOINT((IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [X] END), (IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [Y] END))
Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Add Colour-Spiral to the Colour shelf as a discrete dimension (blue disaggregated pill) and remove the border. Rename the marks card to 4.End Point Outer.
Add another instance of End Point as another marks layer. Again change the mark type to circle and adjust the size so it is smaller than the previous circle, and set the Colour to white. Rename the marks card to 5.End Point Inner.
Tidy up by
removing the Tooltip from each layer
disabling selection of each map layer (so nothing happens when you hover over it)
Hide the axis
Remove axis rulers and gridlines, but make sure the zero lines are shown
Hide the null indicator
Name the sheet Single Spiral or similar.
Building the trellis
Duplicate the single spiral (so if things go awry, you can get back to this). Then start by adding Prefecture to the Detail sheet of all the marks card.
When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created
Cols
INT((INDEX()-1)%10)
Rows
INT((INDEX()-1)/10)
and make both fields discrete.
Add Cols to Columns and Rows to Rows. Adjust the table calculation setting of each field so it is computing by Prefecture only, and custom sorted descending by Self-sufficiency ratio…
Then show the Prefecture filter and select all values to display the ordered set of Prefectures.
Hide the Rows and Cols fields, remove row & column dividers. Adjust the size of the start and end point circles to suit, and if the zero lines aren’t showing, reduce the size of the label circle map layer and fit to Entire View.
Then name this sheet Trellis or similar and add to the dashboard.
For this week’s #WOW2025 challenge, I looked to recreate part of Kathryn McCrindle’s TC25 Iron Viz Final dashboard. The main focus was building the ‘cockpit dials’ – the concentric donut charts, but I also wanted to use the challenge to highlight a couple of techniques Iron Viz finalists use to make the build quicker – background images and custom themes, a new feature introduced in v2025.1. I provided 3 files for this challenge, accessible from here.
A simplified dataset
An image for the background
A json file for the custom theme.
Checking the numbers
To start, we’ll build out some of the calculations needed to sense check the logic.
After connecting to the data, make sure all the fields are defined as dimensions (above the Measure Names line on the data pane) – if they aren’t drag them up. The only field in the Measure Values section should be the count of the records.
We first need to identify how many times each part was recorded in a strike (or not). Drag Part to Rows and Struck? to Columns. Create a new field
# Strikes
COUNTD([Strike ID])
Add to Text. This gives our basis for the inner ring.
The True values above, form the basis for which we then want to understand the split between whether the struck part was then damaged or not. On a new sheet, add Part to Rows and Damaged? to Columns, then create field
# Struck
COUNTD([Strike ID – Is Struck])
and add to Text. Additionally add row grand totals (Analysis menu > Totals > Show Row grand totals). The total for each row should match the True value from the previous sheet (pop the sheets side by side on a dashboard to sense check).
Once again, the Damaged? =True values, form the basis for which we then want to understand the split between whether the struck part was then substantially damaged or not. On a new sheet, add Part to Rows and Substantially Damaged and Part Damaged? to Columns, then create field
# Damaged
COUNTD([Strike ID – Is Damaged])
and add to Text and show row grand totals. Add this sheet to the dashboard too. The total for this sheet should match the True values from the Damaged sheet.
Building the Donuts
Now we’re happy with the numbers, we can start to build the viz. We’ll be using map layers for this, but as we don’t actually have a geometric datatype field in the data set, we need to create one. I’ll be following the core principles discussed in this blog post I wrote for the company I work for, Biztory. In the blog post, we’re just building 1 donut chart, so created a Zero point using MAKEPOINT(0,0). In this instance, I want multiple donuts arranged in rows and columns. This can be done in 2 ways:
use the MAKEPOINT(0,0) method and then create 2 dimension fields added to Rows and Columns that represent the row and column for each Part (akin to what we do when building trellis/ small multiple charts) or
use the MAKEPOINT method to define the point to position the donut. This is the method I’m using, but will break the fields required down, so it’s clear.
Create a new field
Row
CASE [Part] WHEN ‘Engine’ THEN 1 WHEN ‘Nose’ THEN 1 WHEN ‘Windshield’ THEN 1 WHEN ‘Lights’ THEN 2 WHEN ‘LandingGear’ THEN 2 WHEN ‘Radome’ THEN 2 WHEN ‘WingOrRotor’ THEN 3 WHEN ‘Fuselage’ THEN 3 WHEN ‘Propellor’ THEN 3 WHEN ‘Tail’ THEN 4 WHEN ‘Other’ THEN 4 END
Column
CASE [Part] WHEN ‘Engine’ THEN 1 WHEN ‘Nose’ THEN 2 WHEN ‘Windshield’ THEN 3 WHEN ‘Lights’ THEN 1 WHEN ‘LandingGear’ THEN 2 WHEN ‘Radome’ THEN 3 WHEN ‘WingOrRotor’ THEN 1 WHEN ‘Fuselage’ THEN 2 WHEN ‘Propellor’ THEN 3 WHEN ‘Tail’ THEN 1 WHEN ‘Other’ THEN 2 END
Ensure both of these are dimensions, by dragging above the Measure Names on the data pane if required. These fields are simply ‘hardcoding’ the position of each Part (and can be used if adopting the ‘trellis’ route).
Then create a field
Grid Position
MAKEPOINT([Column],[Row])
On a new sheet, add Grid Position to Detail and for now, add Part to the Label shelf so we can see what Part each mark represents.
Change the mark type to Circle.
Drag another instance of Grid Position on to the canvas and drop when you see the option Add a Marks Layer. This will create another marks card called Grid Position (2).
Now we have 2 layers, we can reposition the marks. Note – doing the next steps before adding a 2nd marks layer, won’t let you then create any layers.
Use the swap axis button to change the axis so Latitude is on Columns and Longitude on Rows. Edit the Longitude axis (y-axis) and set the scale to be reversed. The Parts should all now be in the right place.
Now we can start to make the donuts. When using map layers, its good practice to rename the marks cards, so start by renaming the marks card Grid Position to be Centre and Grid Position (2) to be Struck Ring.
On the Centre marks card, change the colour to be dark grey (#44505b) and increase the size. Create a new field
Label – # Substantially Damaged
COUNTD(IF [Substantially Damaged and Part Damaged] THEN [Strike ID – Is Damaged] END)
Add this to the Label shelf and move Part to Detail instead. Position the label middle centre, but don’t adjust the font style in any way.
Create new fields
Tooltip – #Damaged
COUNTD(IF [Damaged?] THEN [Strike ID – Is Struck] END)
and
Tooltip – #Struck
COUNTD(IF [Struck?] THEN [Strike ID] END)
and add these to the tooltip shelf of the Centre marks card, and adjust Tooltip text – again don’t adjust the font style.
Click on the Struck Ring marks card. Drag it so it is under the Centre marks card. Add Part to Detail. Increase the Size until you can see a ring larger than the centre circle. Change the Mark Type to Pie and add #Strikes to the Angle shelf. Add Struck? to Colour. Adjust the colours, and re-order the colour legend so True is listed first.
Disable Selection on the Struck Ring marks card
Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Inner Dark Grey Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Circle and set the colour to dark grey. Disable selection of the card.
Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Damaged Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Pie, add #Struck to Angle and add Damaged? to Colour and adjust. Reorder the colour legend so True is listed first. Disable selection of the card.
Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Outer Dark Grey Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Circle and set the colour to dark grey. Disable selection of the card.
Add the final layer by dragging Grid Position on to the canvas. Rename this marks card to Substantial Damage Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Pie, add #Damaged to Angle and add SubstantiallyDamaged and Part Damaged? to Colour and adjust. Reorder the colour legend so True is listed first. Disable selection of the card.
Note – you may find you have to tweak the sizes of each layer – when published to Tableau Public, you can do this more accurately by setting a size % – on Desktop you just have to guess the slider position
Add in the custom theme
On the Format menu, select import custom theme and select the json file provided. When prompted select Override to apply the theme.
The font style and other properties of the chart should immediately update
Hide the axis, remove axis rulers and re-name the sheet.
Create the dashboard
Create a dashboard and set it to 1000 x 900. Add an Image object onto the dashboard, and select the background image provided, setting it to fit and centred. Remove the outer padding from both the image object, and the tiled container, so that the image object is exactly 1000 x 900.
You can see, that the labels for each donut, are actually part of the background image. So we now need to add the viz so it is positioned in the right place.
Change the dashboard objects to be ‘floating’ and add the Donut viz. Remove the Title, and delete the colour legends. Adjust the position and height/width to be x=448, y=104 and width=516 and height = 762 (this was done by trial and error – Iron Vizzers will have practiced so many times, they will know what they want these numbers).
But the background isn’t visible through the chart, so right click on the Donut sheet (while its on the dashboard), and Format and then set the background to be set to None.
And that is the crux of the challenge. I then added a floating title along with my standard footer. My published viz is here.
This week’s #WOW2024 challenge was run live at the #Datafam Europe event in London and was a combo with the #PreppinData crew. If you want to have a go at shaping the data required for this challenge yourself, then check out the PreppinData challenge here. Otherwise, you can use the data provided in the excel workbook from the link in the #WOW2024 challenge (I’m building based on this).
Modelling the data
There are 3 data sources for this challenge which we need to relate together. We have
Attraction Locations – a list of attractions in London with their lat and long coordinates
Tube Locations – a list of tube stations in London with their lat & long coordinates
Attraction Footfall – a list of attractions with their annual footfall
Connect to the Excel file and add Attraction Locations to the canvas. Then add Tube Locations and then create a relationship calculation of 1=1 to essentially map every attraction to every tube station.
Then add Attraction Footfall to the canvas and relate it to Attraction Locations by setting Attraction Name = Attraction
Finally, in the viz we have to understand the distance between a selected attraction (the start point) and other attractions (the end point), so we need to have an additional instance of Attraction Locations to be able to generate the information we will need between the start and end. So add another instance of Attraction Locations and set the relationship as Attraction Name <> Attraction Name
To make things a bit easier for reference purposes, rename Attraction Locations to Selected Attraction and Attraction Locations1 to Other Attractions (just right click on the data connection in the canvas to do this).
Building the Footfall Bar Chart
On a new sheet add Attraction Name (from Selected Attraction) to Rows and add 5 Year Avg Footfall to Columns. Change this from SUM to AVG (as the data consists of multiple rows per year and this value is the same for each row associated to an attraction). Sort the chart descending.
Click on the 2 nulls indicator and select to filter the data which will remove the bottom two rows and automatically add 5 Year Avg Footfall to the Filter shelf.
Manually increase the width of each row. Set the format of the 5 Year Avg Footfall to be in millions (M) to 2dp, and then show mark labels and align middle left.
Create a parameter to capture the selected attraction
pSelectedAttraction
string parameter defaulted to St Paul’s Cathedral
show the parameter on the screen.
We need to identify which attraction has been selected, so create
Is Selected Attraction
[Attraction Name]=[pSelectedAttraction]
and then add this to the Colour shelf. Adjust the colours accordingly and set an orange border. Then add Attraction Rank to Rows. Set it to be a discrete dimension (blue pill) and move it to be in front of Attraction Name.
Set the font of the row labels to be navy, hide the row label names (hide field labels for rows), hide the axis (uncheck show header), don’t show tooltips, and remove all row/column dividers, gridlines and zero/axis lines. Set the background of the worksheet to be None (ie transparent). Update the title of the sheet and then name the sheet Footfall or similar.
Building the map
We’re going to use map layers for this, and will build 4 layers
the selected attraction
the other attractions
the tube stations
the buffer circle
When using map layers we want to work with spatial data, so we’ll start by creating a point for the selected attraction
Double click on this and it will automatically generate a map. Add Is Selected Attraction to the Filter shelf and set to True so only 1 mark should display, Add Attraction Name to Detail. Show the pSelectedAttraction parameter. Change the mark type to shape and select a filled star. Set the Colour of the shape to navy and add an orange halo. Update the Tooltip.
For the buffer, we need another parameter
pDistance(miles)
float parameter defaulted to 1 that ranges from 0.5 to 2 with a step size of 0.5
And drag this onto the canvas and drop when the Add Marks Layer option appears
This will create a new marks layer, which we can rename to Buffer. Reduce the opacity of the colour to 0%. Move the marks layer so it is at the bottom (below the other marks card) , and set the disable selection option so when you move the cursor over the map the buffer circle does not highlight.
Adjust the background layers of the map so only the Postcode Boundaries are visible.
To add the tube stations, we first need to create
Tube Station Point
MAKEPOINT([Station Latitude],[Station Longitude])
Then drag this onto the canvas to create a new marks layer. Add Station to the Detail shelf of this new marks card, and move the marks card so it is below the Selected Attraction marks card.
We don’t want all the stations to display. We just need to show those up to 1.5x the buffer distance, so we need
Distance to Tube Station
DISTANCE([Selected Attraction Point], [Tube Station Point], ‘mi’)
format to a number with 2 dp and then create
Tube Station Within Range
[Distance to Tube Station]<= 1.5 * [pDistance(miles)]
Add this to the Filter shelf and set to True.
We want the size of the displayed stations to differ depending on whether they’re inside the buffer or not, so create
Tube Station Within Buffer
[Distance to Tube Station] <= [pDistance(miles)]
and add this to Size. Change the mark type to circle, then adjust the size as required. Change the colour to orange and add a white border. Add Distance to Tube Station to Tooltip and update. You may want to adjust the size of the shape on the Selected Attraction marks card too, so it’s bigger than the tube stations.
The stations need to be labelled based on the closest x number of stations that are within the buffer. For this we need a parameter
pTop
integer parameter defaulted to 5 that ranges from 5 to 20 with a step size of 1.
We need to rank the stations based on the distance, so create
Station Rank
RANK(SUM([Distance to Tube Station]), ‘asc’)
We’re also going to label the stations with a letter based on their rank
Rank Stations as Letters
CHAR([Station Rank] + 64)
but we only want to show labels for the ‘top’ ranked stations, so create
Label Stations
IF MIN([Tube Station Within Buffer]) AND [Station Rank]<=[pTop] THEN [Rank Stations as Letters] END
and add this to the Label shelf. Adjust the table calculation settings, so the calculation is computing by both Station and Tube Station Within Buffer.
Set the labels to be aligned middle centre, and allow labels to overlap other marks. If things are working as expected, then if you increase the buffer distance to 1.5 miles and the pTop parameter to 20, you should see that not all stations within the buffer circle are labelled
To add the other attractions, we need to create
Other Attraction Point
MAKEPOINT([Attraction Latitude (Attraction Locations1)],[Attraction Longitude (Attraction Locations1)])
and drag this onto the canvas to Add a marks layer. Move this layer so it is beneath the Selected Attraction marks card, and add Attraction Name (from the Other Attractions) section to Detail
Once again, we want to limit what attractions display, so need
[Distance to Other Attraction]<= 1.5 * [pDistance(miles)]
and add this to the Filter shelf and set to True.
Add Distance to Other Attraction to the Tooltip shelf and update. Change the mark type to shape. The shape needs to differ whether it’s within the top x closest attractions that’s inside the buffer or not. So we need
Rank Other Attractions
RANK(SUM([Distance to Other Attraction]), ‘asc’)
and then
Top X Attraction in Buffer
IF [Rank Other Attractions] <= [pTop] AND MIN([Other Attraction within Buffer]) THEN MIN([Attraction Name (Attraction Locations1)]) ELSE ‘Not Top X’ END
Add this to the Shape shelf. Set the table calculation so it is computing explicitly by both Attraction Name and Other Attraction Within Buffer. Setting the specific shape for each of the named attractions that could show is fiddly, so I just chose to leave as per the default values listed. The only shape I explicitly set was the Not Top X which I set to a filled circle. I set the colour of the shapes to dark grey and added a halo of the same colour to make the shape more prominent. The shapes also need to differ in size based on whether they are in the buffer or not, so need
Other Attraction Within Buffer
[Distance to Other Attraction] <= [pDistance(miles)]
Add to the Size shelf and then adjust sizes to suit.
Set the background of the worksheet to None, remove all row/column dividers and name the sheet Map or similar. Finally remove all the Map Options (Map > Map Options > uncheck all selections) to prevent to toolbar from displaying on hover. Test the map functionality by changing the various parameters and entering a new starting location.
Note– in subsequent testing I found that for some attractions where there were either no tube stations or other attractions within the range, the map would disappear. If I get time I’m going to try to work on a solution for this, but I’ll leave as is for now (Lorna’s published solution has the same issue).
Building the Tube Station Rank Bar
On a new sheet add Station to Rows and Distance to Tube Station to Columns. Add Is Selected Attraction to Filter and set to True. Sort the chart ascending, so closet is listed first.
We only want to display the stations that are within the buffer, so add Tube Station Within Buffer to Filter and set to True.
We also want to restrict this list to just those that are the closest ‘x’ to the attraction based on the pTop parameter. Add Station to the Filter shelf and on the General tab, select Use all and then select the Top tab and add the condition to display the bottompTop by Distance to Tube Station.
However, this doesn’t quite show the correct results, as the Top n filtering has been applied BEFORE the other filters on the shelf. To resolve this we need to add Is Selected Attraction and Tube Station Within Buffer to context (right click each pill on the filter shelf).
Add Station and Distance to Tube Station to the Label shelf, and adjust the label to display the text as required and align middle left. Change the mark type to bar and manually widen the width of each row so the labels are readable. Adjust the colour of the bars.
For the circle labels, we need a ‘fake’ axis – double click into Columns and manually type MIN(-0.05). Move the pill that is created to be in front of the Distance to Tube Station pill.
Change the mark type of the MIN(-0.05) pill to circle and remove the fields from the Label shelf. Add Rank Stations as Letters to the Label shelf instead and adjust the table calculation so it is explicitly computing by Station. Format the label and align middle centre.
Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.
Don’t show the Tooltip, remove all row/column dividers, hide the axis and the Station column. Hide all gridlines, axis lines, zero lines. Format the background of the workbook to be None (ie transparent).
Update the title of the sheet referencing the parameters as required, and name the sheet Tube Station Rank Bar or similar.
Building the Tube Station Rank Bar
On a new sheet add Attraction Name (from the Other Attractions data set) to Rows and Distance to Other Attraction to Columns. Add Is Selected Attraction to Filter and set to True. Sort the chart ascending, so closet is listed first.
We only want to display the other attractions that are within the buffer, so add Other Attraction Within Buffer to Filter and set to True.
We also want to restrict this list to just those that are the closest ‘x’ to the attraction based on the pTop parameter. Add Attraction Name to the Filter shelf, on the General tab, select Use all and then select the Top tab and add the condition to display the bottompTop by Distance to Other Attraction.
Add Is Selected Attraction and Other Attraction Within Range to context.
Add Attraction Name (from the Other Attractions data set) and Distance to Other Attraction to the Label shelf, and adjust the label to display the text as required and align middle left. Change the mark type to bar and manually widen the width of each row so the labels are readable. Adjust the colour of the bars.
Double click into Columns and manually type MIN(-0.1). Move the pill that is created to be in front of the Distance to Other Attraction pill.
Change the mark type of the MIN(-0.1) pill to shape and remove the fields from the Label shelf. Add Attraction Name to the Shape shelf. Set the colour of the shape. Edit the shape for each Attraction so it matches the shapes assigned to the attractions on the Map sheet. Unfortunately, this is a bit fiddly and just a case of trial and error which involves changing the parameters to try to ensure all the options are presented at least once of each of the charts. There is probably a better way, but I’d have to rebuild something so sorry!
Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.
Don’t show the Tooltip, remove all row/column dividers, hide the axis and the Attraction Name column. Hide all gridlines, axis lines, zero lines. Format the background of the workbook to be None (ie transparent).
Update the title of the sheet referencing the parameters as required, and name the sheet Tube Attraction Rank Bar or similar.
Adding the interactivity
Add the sheets onto the dashboard making use of layout containers to get the objects positioned where required. Format the dashboard to set the background to the light peach colour. How I’ve organised the content is show by the item hierarchy below
Create a parameter dashboard action
Select attraction
On select of the footfall bar chart, set the pSelectedAttraction parameter with the value from the Attraction Name field. Keep the value when the mark is deselected.
And at this point, you should hopefully now have a functioning dashboard. My published version is here.
It was Kyle’s turn to set the challenge this week. Like him, I don’t have a need to use map / spatial data much, so whenever there’s a WOW challenge involving them it always makes me think a bit harder (and usually refer to some documentation).
Connecting to & modelling the data
I followed the links in the challenge requirements and downloaded the Shapefile option from each page
This downloaded zip files (one did take some time to download). I then extracted the zip files which generated several files.
In Desktop, I then chose to connect to the Spatial file option and when I navigated to the file location where I had unzipped the data, only the .shp file was available for selection.
I connected to the School District Characteristics data source first, then clicked the ‘carrot’ to access the context menu of the data source, and selected open to access the physical layer of the data canvas
I then clicked Add against the connections section to add another spatial file data source, selecting the School Neighbourhood Poverty file this time and changed the join type between the two data source fields to use the intersects option.
Building the bar chart
On a new sheet add Statename to the Filter shelf, and select Washington. Add Lea Name to the Rows. Create a new field
and add this to Columns and sort descending. Widen each row slightly, and increase the width of the Lea Name column a bit. Remove all gridlines, and remove the axis title, and hide the Lea Name column heading. Update the Tooltip as required and update the sheet title.
Building the map
Create a new sheet. Add the Geometry field from the School District Characteristicsset of data to the Detail shelf.
Go back to the bar chart sheet, and update the Satename filter so that it also applies to the sheet you’re building the map on. The map should now be filtered to Washington too. Add Lea Name to the Detail shelf and # Schools to the Tooltip and adjust accordingly.
From the Map > Background Layers menu option, uncheck the options on the Background Map Layers section, so just the Cities and Streets, Highways/Motorways.. options remain selected. Adjust the Colour of the map (via the colour shelf)
Then drag the Geometry field from the School Neighbourhood Poverty data source section onto the canvas and drop it when the Add a Marks Layer section appears
This will add a second marks card. Name this marks card Schools and the other one Districts.
On the Schools marks card, add Name to the Detail shelf and then update the tooltip as required. Remove the row & column dividers.
Adding the interactivity
Add the 2 sheets onto a dashboard side by side and show the Statename filter. Add a dashboard filter action
Filter District
On Select of the bar chart, target the Map passing all fields. Show all values when selection is cleared.
Clicking on a bar should now filter the map and ‘zoom in’ just to that district with the relevant school marks visible.
For those of you who are regular readers of my blog, you’ll know that working with maps and spatial data isn’t something I do often, so challenges like this always start with me feeling a little bit daunted by what’s required.
Side Note – I originally built this challenge using Tableau Desktop v2024.1, but encountered some issues with getting the data on the map updated as I made changes to the selections – the selection changes were visible on other tabular sheets, just not on the map, unless I forcibly refreshed the data source. Recreating in Tableau Desktop v2023.3 was fine. And the version published from v2024.1 to Tableau Public also worked fine on Tableau Public. I have raised this to Tableau via Slack channels I have access to, so if you experience similar issues, that may be why…
Understanding the data and the requirement
I initially spent some time trying to understand how the data matched up to the information I could see on the viz, specifically what was being listed in the Arrival Station selection box.
I found, every Station was associated with a Line, but the Station could be associated to more than one Line. Every Line was associated to a Branch, but again, the Line could be associated with more that one Branch. Picking some specific Stations as an example…
Amersham Station is associated to 1 Line (Metropolitan) which is associated to 1 Branch (Metropolitan Line Branch 0) – so Amersham is associated to 1 Branch
Bank Station is asscociated to 3 Lines (Central, Northern, Waterloo) which in turn are only associated to 1 Branch each – so Bank is associated to 3 Branches
Acton Town Station is associated to 2 Lines (District and Piccadilly); District is associated to 1 Branch which Piccadilly is associated to 2 Branches – so therefore Acton Town is associated to 3 Branches.
The list of possible Arrival Stations is based on the set of Stations associated to any of the Branches the Starting Station is associated to.
So for Amersham, we’re looking for all those Stations on the metropolitan branch 0 Branch
For Bank we’re looking at Stations on the central 0, northern 1 and waterloo 0 Branches
and for Acton Town, we’re looking at stations on the district 0, piccadilly 0 and piccadilly 1 Branches.
So first we need to find a way to
Identify the Starting Station
Identify the Branches the Starting Station is associated with
Identify the Stations associated to these Branches.
Identifying the Arrival Stations
To start with, we need to capture the starting station, which we can do with a parameter
pStart
String parameter which is a List object that populates from the Station field when the work book is opened, and is defaulted to Bank.
For the rest, we’ll build up what we need step by step, so on a new sheet add Branch and Station to Rows and display the pStart parameter.
I’m first going to identify the possible Branches associated to the pStart station, and ‘spread’ this across all the stations in that Branch
Possible Branches
{FIXED [Branch] : MIN(IF [Station] = [pStart] THEN [Branch] END)}
If the Station in the row matches that in pStart, then get the Branch for that row, then ‘spread’ that across all the rows with the same Branch (via the {FIXED [Branch]: …. } statement.
Add this onto Rows and you’ll see the name of the Branch is listed against all the stations associated to the branch that the pStart station is related to
Now we can define a field to capture the stations that have a Possible Branch
Possible Destination Stations
IF NOT ISNULL([Possible Branches]) THEN [Station] END
Add this to Rows too, and stations should only be listed against those rows with a Possible Branch
We can use this field to then create a Set. Right click on Possible Destination Stations > Create > Set
Destination Stations Set
Select Epping from the list displayed
Add the field to the Colour shelf (the Epping row should be coloured IN the set). Then click on the pill on the Colour shelf and select Show Set
The list of possible options in the Destination Stations Set should be displayed. Change the control type to be single value dropdown
Now test the behaviour of the set by changing the value of the pStart parameter eg select Amersham. Epping remains selected but is now contained in ( ) as it’s not a valid value. The other options to select though should all now have changed.
This is the ‘relative values’ only type behaviour required.
Determining the number of stops
While we’re working with a ‘check sheet’, let’s finalise the other calculations we’re going to need to build the final viz; firstly the number of stops between the two selected stations. We’re going to use the Path Order field to help with this.
Firstly, if it’s appearing as a string in the data set, convert it to a numeric whole number field, then add it to Rows between Branch and Station It should be a discrete dimension (blue disaggregated field). A unique number should be listed against each record; this record is effectively an index defining the order of the Stations on the Branch.
Let’s reset the station parameters to start at Bank and end at Epping These stations are on the Central 0 Branch, and Bank is at Path Order 47 and Epping at 61
The number of stations is the absolute difference between these two numbers. To determine this, we need to capture the Path Order for the starting station against every row.
Now, it’s possible that the stations are on multiple branches, so we need to make sure we have a handle on the Branch we care about
Selected Branch
{FIXED: MIN(IF [Destination Stations Set] THEN [Branch] END)}
Get the Branch associated to the selected destination station, and then ‘spread this’ across all rows.
Add this to Rows.
Now we can get the number associated to the pStart station on the Selected Branch, and spread this across every row
Starting Station Path No
INT({FIXED: MIN(IF [pStart] = [Station] AND [Branch] = [Selected Branch] THEN [Path Order] END)})
as well as
Destination Station Path No
INT({FIXED: MIN(IF [Destination Stations Set] AND [Branch]=[Selected Branch] THEN [Path Order] END)})
Add both of these as discrete dimensions to Rows
Then we can create
No. of Stops
ABS([Starting Station Path No] – [Destination Station Path No])
which is just the absolute difference between the two
Identifying the stations between start & end
The final piece of the puzzle, that we’re going to need is just to isolate all the Stations on the Branch that lie between the pStart station and the station in the Destinations Station Set. As this is going to be used to highlight the section of line on the map, I called this
Highlight Line
[Path Order] >= MIN([Starting Station Path No],[Destination Station Path No]) AND [Path Order] <= MAX([Starting Station Path No], [Destination Station Path No])
Here I utilised the rarely used (at least in my case) feature of the MIN and MAX functions, that allows you to supply multiple values and return a single value – the MIN or the MAX of the options provided. So in this case, I want to flag all the rows as being true if the Path Order sits between the Starting Station Path No and the Destination Station Path No. Add this onto Colour instead of the In/Out set and we can see all the rows between the two endpoints are highlighted.
Test by trying different start and ends, so you’re happy how the behaviour is working.
Building the tube map
This did take a bit of time to get right, and I did end up referring to Tableau’s own KB article on creating paths between origin and destination to get some pointers (although I didn’t follow it to the letter…)
Create a new sheet, then create a spatial field
Station Location
MAKEPOINT([Right Latitude], [Right Longitude])
and double click to automatically add the field to the new sheet. Longitude and Latitude fields are automatically generated and a basic layout is immediately visible
Add Branch to Detail then change the mark type to Line.
Add Path Order to Path. The lines should all now join up as expected
Delete all the text from the Tooltip, but ensure Show Tooltip is still enabled.
Set the background of the map to dark (Map menu > Background Maps > Dark). Adjust the Colour of the line to whatever suits (I used #01e6ff)
Add a 2nd map layer – drag Station Location onto the canvas and drop when the Add a marks layer option appears
Change the Mark type of this 2nd marks card to circle, then add Station and Line to the Detail shelf. Change the colour to same as the line and adjust the Size if required. Update the Tooltip as required.
To highlight the stations between those selected, create a new spatial field, just for those stations
Selected Stations
IF [Highlight Line] THEN [Station Location] END
Drag this on to the canvas to make a 3rd marks layer.
Add Branch to Detail, change the Mark type to line and add Path Order to Path. Change the Colour to something contrasting (I chose #ff00ff). Adjust the Size so the line is a bit thicker than the other lines.
To label the start & end station, create
Label – Stations
IF [Station] = [pStart] OR [Destination Stations Set] THEN [Station] END
Add to the Label shelf, and change to be an attribute (rather than dimension) so it doesn’t break up the line. Adjust the font accordingly. I set it to Tableau Medium 8pt bold in white, aligned top centre. All the labels to overlap other marks.
Show the pStart parameter and the Destination Stations Set list (just right click on the field in the data pane on the left and select Show Set – this is now an option as there are fields already on the viz that reference that set). Test the display by changing the options.
Add No of Stops to the Detail shelf, then update the title to reference the field. Set the font to white and align right.
Format the background of the whole worksheet to black, remove row/column dividers. Hide the null indicator field, and remove all map options (Map menu > map options, uncheck all the fields).
The viz should now be ready.
Add it onto a dashboard, which is also formatted to have a black background. Display the pStart parameter and the Destination Stations Set as floating objects. Update the title of each and format the latter so it has a black shading to the body of the control. Remove the ‘all’ option from the arrival station control (customise > uncheck show ‘all’ value).
My published version is here. Hopefully I’ve built it in a way that supports the impending Part 2…
As community month draws to a close, long term participant Deborah Simmonds set us a map based challenge based on TC23 data.
I chose to use the data that was associated to the solution workbook, rather than take the direct source, so bear that in mind if you’re following along.
Examining the data
As there were quite a few fields in the data set which were directly related to the solution, I just wanted to familiarise myself with what I was working with initially. Essentially we have a row per person (Names Full) with the hotel they stayed at (Label Hotels), and how many steps (Steps) they took each day (Date). The details of the convention centre (Label Convention Centre) exist against every row.
The LAT and LON fields contain the location details of the hotels, while Convention LAT and Convention LON contain the location details of the Mandalay Bay Convention Centre.
Names Full already had the logic applied to create names for users based on their User ID if the Name was NULL, so I didn’t need to do anything for that requirement.
We only want to consider records relating to those people who attended ‘In Person’ and who had provided a hotel. Consequently I added the following as data source filters (right click data source -> edit data source filters) to exclude unrequired records from the whole analysis.
Did you attend in person or virtual? : In person
Label Hotels : excludes NULL
Building the BANs
The BANs have 3 metrics – the number of attendees, the number of steps, and the distance in metres (based on the logic that 1 step = 0.75m).
Attendees
COUNTD([User ID])
Distance (m)
[Steps] * 0.75
On a new sheet add Date to Filter as a range of dates and show the filter control. Add Attendees, Steps and Distance (m) so the measures are displayed in a row.
Change the mark type to shape and add a transparent shape (see this blog for more details). Add Measure Names to Label too and then adjust label accordingly and align centrally.
Hide the header row (uncheck show header), remove row dividers and don’t show tooltips. Name the sheet BANs or similar. Adjust the date slider and the values should adjust.
Building the bar chart (viz in tooltip)
On a new sheet add Names Full to Rows and Steps to Columns. Apply the same Date filter (from the BANs worksheet, set the filter to apply to this new worksheet too).
The viz needs to display a reference line showing the overall average of the steps per person across the selected dates, regardless as to whether the records are filtered to a hotel or not.
What do I mean by this… well if I add a standard average reference line to the viz above, the average for the whole table is 78.2k steps.
If I now filter this by a Label Hotel, then the average changes, and I don’t want that – I still want to see 78.2k.
But the average does need to change if the date range changes
This took a bit of effort to get right, but I needed
Format this as a number with 1dp set to the K (thousandths) level
and I also needed to add the Date field on the Filter shelf to context.
So reverting back to the initial view of the bar chart… right click on the Date filter and Add to Context. Add Avg Steps to the Detail shelf. Then add a reference line (right click the Steps axis > add reference line) that displays the Avg Steps with a dark dashed line with a custom label.
Format the reference line to position the label at the top and adjust the font style.
To colour the bars we need
Steps above average
SUM([Steps]) >=[Avg Steps]
Add this to the Colour shelf, change the colours and adjust the opacity to about 75%.
Hide the column heading, and adjust the font size of the names and the axis. Name the sheet Bars or similar.
Building the initial map
I decided to create 2 maps – one for the initial display of all the hotels and the convention centre, and then one for the selected location and buffer.
To plot the hotels on the map I created
Hotel Locations
MAKEPOINT([LAT],[LON])
On a new sheet, add this to the Detail shelf. A map will automatically generate with Latitude and Longitude fields. Change the Mark Type to Circle, then add Label Hotels to Label and align left middle. Edit the map Background Layers (via the map menu) to add Streets, Highways etc to the display
Add Steps, Attendees and Distance(m) to the Tooltip. Add Attendees to the Size shelf and adjust the size to vary by range
Apply the Date filter from the other worksheets to this sheet too. For the tooltip, We need to know about the min and max dates in the range selected. Create
Min Date
MIN([Date])
and custom format simply as dd (the day only)
Also create
Max Date
MAX([Date])
and custom form this as dd mmm yyyy
Add both of these fields to the Tooltip shelf. Adjust the text in the Tooltip and add a reference to the Bars sheet as a viz in tooltip (Insert > sheets > ). Adjust the height of the sheet to be 900.
The viz in tooltip should now display nicely on hover
To add the mark for the convention centre, we need
Conf Location
MAKEPOINT([Convention LAT], [Convention LON])
Drag this onto the map, and drop it when Add A Marks Layer displays
This will create a 2nd marks card. Change the mark type to shape and select the Tableau sparkle image if you have it stored. If not, just use another shape or circle (coloured differently).
Add Attendees to Size and add Label Convention Centre to Label and align left middle. Add Steps and Distance(m) to Tooltip and adjust to suit. Hide all the map options (map menu -> map options -> uncheck all the selections). Name the sheet Map – Initial or similar.
Building the ‘selected’ map
This will use parameters to identify what’s been selected – a hotel or the convention centre, so we need
pSelectedHotel
string parameter defaulted to empty string
and
pSelectedCentre
string parameter defaulted to empty string, just like above
The intention is that either both these parameters will be empty or only one will be populated.
To plot on a map we need
Selected Hotel Location
IF [pSelectedHotel] = [Label Hotels] AND [pSelectedCentre] =” THEN [Hotel Locations] END
and
Selected Centre Location
IF [pSelectedHotel] = ” AND [pSelectedCentre] =[Label Convention Centre] THEN [Conf Location] END
Duplicate the initial map sheet and name it Map – Selection. Show the two parameters and verify both are empty.
On the Hotel Locations marks card, drag the Selected Hotel Location field and drop it straight onto the Hotel Locations field. On the Conf Location marks card, drag Selected Centre Location and drop straight onto the Conf Locations field. Your map shouldn’t display anything…
Manually type ‘Luxor’ into the pSelectedHotel parameter. A mark should display. Adjust the Label of the Selected Hotel marks card so it is larger font, and aligned top middle. Set the colour to orange and remove the halo.
Remove the text from the pSelectedHotel parameter and manually type ‘Mandalay Bay Convention Centre’ into the pSelectedCentre parameter. A mark should display. Adjust the Label of the Selected Centre marks card so it is larger font, and aligned top middle. Remove the halo from the Colour shelf.
To create the buffer circle, we need to define the buffer radius, which is the attendee steps in metres.
Buffer Distance (m)
{FIXED : SUM(IF [pSelectedHotel] = [Label Hotels] OR [pSelectedCentre] = [Label Convention Centre] THEN [Steps] END)} * 0.75
and then we create
Buffer
IF [pSelectedHotel] <> ” THEN BUFFER([Selected Hotel Location], [Buffer Distance (m)], ‘m’) ELSEIF [pSelectedCentre] <> ” THEN BUFFER([Selected Centre Location], [Buffer Distance (m)], ‘m’) END
Add this as another marks layer.
Reduce the opacity on the colour shelf to 0% and set the border to be orange. We don’t want to allow any interactivity with the buffer, so disable selection of the layer, and also move down so it is listed at the bottom of the 3 map layers.
Show the Date filter control and adjust the dates to see the buffer adjusting. Test the behaviour with a hotel too (you may find you want to add some more detail to the background layers of the map).
We will use dynamic zone visibility on the dashboard to decide whether to display the initial or the selected map. To control this, we need
Show Initial Map
[pSelectedCentre]=” and [pSelectedHotel]=”
and
Show Selected Map
[pSelectedHotel]<>” OR [pSelectedCentre]<>”
Adding the interactivity
Create a dashboard, add the BANs and both the map sheets.
Create a dashboard parameter action
Set Hotel
On selection of the Initial Map set the pSelectedHotel parameter passing in the value from the Label Hotels field. When the selection is cleared, reset to ”.
and another parameter action
Set Conv Centre
On selection of the Initial Map set the pSelectedCentre parameter passing in the value from the Label Convention Centre field. When the selection is cleared, reset to ”.
Select the Initial Map object, and from the Layout tab, set the visibility to be controlled by the Show Initial Map field
Then select the Selected Map object, and set the visibility to be controlled by the Show Selected Map field. Only one of the maps should display based on the interactivity.
This is all the core functionality of the map, but Deborah threw in a couple of extra asks…
Building the Distance Legend
We’re using map layers again for this. Create a new field
Zero
MAKEPOINT(0,0)
Add it to the Detail shelf of a new worksheet to create the 1st map layer, then immediately add another map layer, by adding another instance of Zero to the sheet.
Switch the axis, and the map will disappear, and you’ll have axis displayed instead. Change the mark type of the first map layer to circle and colour orange.
Change the mark type of the 2nd map later to circle and colour pale grey with an orange border. Increase the Size of this circle so it appears as a ring around the filled orange circle. Move the 2nd marks layer down to the bottom and disable both marks from being selectable.
Hide the axis and gridlines/zero lines.
Right click on the central circle and annotate point. Don’t enter any text into the annotation box, just click OK. You should get the annotation box with a line.
Move the box so the connector line is horizontal, then format the annotation so the shading is set to none and the line is formatted to be a darker dashed line. Update the title of the sheet.
Building the Size Legend
Apply a similar process to that described above, but this time create 3 mark layers where the mark type is an open circle shape which is coloured blue, and for each layer, the size of the circle is slightly bigger. This time show the zero lines.
Set the background of both the legend sheets to be none (ie transparent), then add them as floating objects onto the dashboard. Use the control visibility feature to only display the Size legend when Show Initial Map is set, and only display the Distance legend when Show Selected Map is set. Set a background against each object of light grey, that is then set to 80% transparency.
With this you should have a completed challenge. My published version is here.
Donna + DataViz 