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)
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.
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.
For this week’s #WOW2023 challenge, guest poster Ervin Vinzon asked us to rebuild this visualisation based on data from his home country, The Philippines.
I have to admit, I did find this a bit tough this week – there was a lot going on and maps don’t come naturally to me. I actually wasn’t sure initially whether both the files were needed, as the requirements were a little bit sparse, and I managed to build pretty much the whole solution not using the zip file. I just couldn’t get the map label annotation to work, so ended up having to had to revisit and start again.
Modelling the data
You will need to download both the excel file and the zip file from the Ervin’s shared area.
In the data pane, connect to the Ph Pop 2020 excel file and add the Philippine Population sheet to the canvas.
Then add a connection to a spatial file and point to the zip file. Tableau will automatically identify the file it can use. Add the Provinces file to the canvas.
Create a relation that uses relationship calculations that maps from the Philippine Population sheet :
IIF([Province] = “Maguindanao del Norte” OR [Province] = “Maguindanao del Sur”, “Maguindanao”, [Province])
to the the Provinces sheet
IIF([ADM1_EN] = “National Capital Region”, “Metro Manila”, [ADM2_EN])
Thanks to Rosario Gauna for helping me with this logic, as I couldn’t figure out how the data needed to be related. I think this really needed to be included in the requirements… (Note the logic has been adjusted since I took the image below)
Building the Measure Selector
We can’t use a parameter directly for this, as the design of the ‘radio button’ is more fancy than just what you get with the basic parameter selection functionality.
So we need to ‘fake’ the selection and can use existing fields in our data set to help with this. The Island Group field contains 3 values, so we’re going to draw on these and build
Measure Selector
CASE [Island group] WHEN ‘Luzon’ THEN ‘By Population’ WHEN ‘Mindarao’ THEN ‘By Population Density’ ELSE ‘By Area’ END
Add to Columns and manually reorder. In the Rows shelf, double click and manually type MIN(0)Change the mark type to circle and add Measure Selector to the Label shelf. Resize the circles, and adjust the label to be aligned middle right. Change the view to Fit Width to see all the labels.
Create a parameter to capture the selected measure when this view is interacted with
pMeasureSelected
string parameter defaulted to By Population
Show the parameter on the sheet. Then create a calculated field
Is Selected Measure
[pMeasureSelected] = [Measure Selector]
and add to the Colour shelf. Adjust the colours to suit and add a grey border on the circles (via the colour shelf).
Stop the Tooltip from showing, hide the MIN(0) axis and the Measure Selector header and remove all gridlines/zero lines and any dividers. Name the sheet Measure Selection.
Building the bar chart
Firstly, we need to determine which measure we’re going to be displaying, so need
Measure to Display
CASE [pMeasureSelected] WHEN ‘By Population’ THEN SUM([Population]) WHEN ‘By Population Density’ THEN SUM([Density]) ELSE SUM([Area (sq km)]) END
Show the pMeasureSelected parameter on a new sheet, then add Island Group and Province to Rows and Measure to Display to Text. Sort the data descending.
Create a new calculated field
Measure Rank
RANK_UNIQUE([Measure to Display])
Change the Measure Rank field in the left hand data pane to be discrete. Add to the Rows and adjust the table calculation so it is computing by Province only. The Measure Rank should show sequential numbers from 1 upwards, but restart at the next Island Group.
Add another instance of Measure Rank to the Filter shelf. Select All intially to select all the numbers. Then adjust the table calculation to compute by Province only as above. Then re-edit the filter and just select numbers 1-10.
The bar visual displays the actual value in a coloured bar, along with the maximum value for the measure in a grey bar. So we need
Max Value
WINDOW_MAX([Measure to Display])
Add this to the table and adjust the table calculation to compute by Province.
Finally, we need some information to help with the labels
Label Strapline
CASE [pMeasureSelected] WHEN ‘By Population’ THEN ” WHEN ‘By Population Density’ THEN ‘persons per sq km’ ELSE ‘sq km’ END
Add this to Rows and then test the behaviour by adjusting the value of the pMeasureSelected parameter.
We now have the data needed to build the bars.
Move Island Group to Columns and manually reorder to be Luzon, Visayas, Mindanao. Move Province and Label strapline to Text. Move Measure to Display and Max Value to Columns. Set sheet to fit Entire View. Reduce the size of the bar to be relatively thin.
On the Measure to Display marks card, add Island Group to Colour and adjust to suit.
Set the colour of the bar on the Max Value marks card to be pale grey and remove the bar border. Remove the Label Strapline field and move the Province from label to Detail.
Make the chart dual axis and synchronise the axis. Adjust the axis (right click > edit axis) to be independent axis ranges for each row or column.
On the Measure to Display marks card, add Measure Rank and Measure To Display to the Label shelf. adjust the table calculation settings of the Measure Rank field to compute by Province only.
Adjust the label to be aligned top left, and then format the label text box, so the label is laid out as required (I used bold 8pt font). To make the label sit ‘above’ the bar, add carriage returns after the text in the label edit box (thanks to Sam Parsons for spotting this sneaky method – my original build was using a much more complex method to get the text sitting on top of the bars!).
Finally hide the axis and the Measure Rank and Island Group fields. Remove all gridlines/zero lines/axis & row and column dividers. Stop the tooltips from showing. Name the sheet Bars.
Building the Bar Header
On a new sheet, add IslandGroup to Columns and manually re-order. Then double click in Columns and manually type MIN(0.1). Set the mark type to Bar and set the view to fit Entire View. Add Island Group to Colour. Reduce the Size of the bar. Edit the axis and fix to end at 0.7. Add Island Group to the Label shelf, and align bottom left. Adjust the size of the font to be larger and then add multiple carriage returns above the label text to shift the label to sit under the bar.
Remove all headers/axis and row/column dividers and gridlines. Stop the tooltip from showing.
Adjust the title of the sheet to reference the pMeasureSelected parameter.
Name the sheet Bar Header.
Building the map
We will need another parameter to store the selected Province value.
pSelectedProvince
string parameter defaulted to nothing
On. a new sheet, double click on the Geometry field. This will automatically display a map of the Philippines. Remove all the unnecessary detail via Map > Background Layers and unchecking all the options.
Add Province to the Detail shelf and Region and Island Group to the Tooltip. Adjust the Tooltip.
Show the pSelectedProvince parameter and manually enter the province Leyte.
Create a new field
Is Selected Province
[pSelectedProvince] = [Province]
and then add to the Colour shelf. Adjust the colours to suit (set the NULL field to the same as False).
We need to capture the ‘geometry’ of the selected Province
Drag this field onto the canvas and drop it on the Add a marks layer section that displays. This will create a second marks card. Change the mark type to Circle and adjust the colour as required. Add pSelectedProvince to the Detail shelf.
Select the circle mark, and add an annotation against the mark (right click > Annotate > Mark). Reference the parameter pSelectedProvince in the dialog window.
Providing the pSelectedProvince is on the Detail shelf and is referenced in the Annotation, then changing the value of the pSelectedProvince parameter to Samar or any other province, should retain the annotation. Once again, thanks to Sam for figuring this out as I could just not see it, even when I looked at the solution.
Remove row & column dividers. Stop the map options from displaying (Map > Map Options and uncheck all selections). Update the title of the sheet, and then name the sheet Map.
Adding the interactivity
Add the sheets to a dashboard using horizontal and vertical layout containers to arrange the objects.
Update the title of the Measure Selection sheet and the Bar Header sheet to match the text being displayed.
Create a dashboard parameter action to define the measure selection on click
Set Measure
On selection of the Measure Selection sheet, set the pMeasureSelected parameter, passing through the value from the Measure Selector field.
Create another action for the Province
Select Province
On selection of the Bars sheet, set the pSelectedProvince parameter, passing through the value from the Province field. When the selection is cleared, reset to nothing.
To stop the Bar Heading sheet from being clicked on, just float a blank object over the top.
To prevent the other bars and the measure selections from all fading when clicked on, create a new field
HL
‘HL’
and add to the Detail shelf of the Bars sheet and the Measure Selection sheet.
Then back on the dashboard add a dashboard highlight action
Unhighlight
On selection of the Bars sheet and the Measure Selection sheet, target the Bars and the Measure Selection sheets using the HL selected field only.
Now when a bar is clicked, it will look ‘selected’ (has a black bar around it), but the other bars won’t become faded/greyed out. Similarly when a measure is selected, the other circles won’t fade.
Phew! That should be it. There’s a fair amount going on here and lots of tricky ‘gotchas’. My published viz is here .
Lorna delivered an exciting map based challenge this week, to recreate one of the most famous visualisations ever created. If you’re part of the data viz community and never heard of John Snow and his cholera map, then I suggest you go and google now 🙂
While I love map based challenges, as its an area I don’t get to use regularly, they’re also the ones that can give me the most frustrations/take the longest to complete, as the functions and concepts I need to use don’t come as readily as with other challenges. I had to refer back to my own blogs on previous map based challenges (specifically this one and this one), to help me out. However these were written before map layers were introduced, so not everything was applicable.
Modelling the data
Building the Avg Distance to Pump bar chart
Building the Total Deaths within Avg Distance bar chart
Building Map
Adding the dashboard interactivity
Modelling the data
3 sets of data was provided
Pumps – 1 row per pump with its location
Deaths – 1 row per death and its location (can be multiple rows for the same location)
Deaths aggregated – 1 row per location where at least 1 death was recorded, including the count of deaths at that location
This all needed to be ‘combined’ so we can work across all 3 sets of data and compare the locations. I used relationship calculations to create a relationship of 1=1 between the related data sets, which means all the aggregated deaths are mapped to each pump, and all the individual deaths are mapped to each pump too.
Building the Avg Distance to Pump bar chart
I chose to start building the bar charts first so I could get better acquainted with how the data was working together, and I knew the maps would take more effort.
To start we need to figure out the distance from each pump to each death, so we need to get the location of each pump, and the location of each death
Pump Location
MAKEPOINT([Pump Lat],[Pump Lon])
Individual Death Location
MAKEPOINT([Death Lat],[Death Lon])
These will both create calculated fields of a geographic data type as indicated by the globe icon to the left of the field.
And with these fields, we can then work out the distance between them in metres
Distance Pump to Death
DISTANCE([Pump Location], [Individual Death Location],’m’)
Now we want the average distance per pump
Avg Distance
{FIXED [Pump ID]: AVG([Distance Pump to Death])}
I formatted this to 0 dp and added a suffix of ‘m’
And with this, we can build the first bar chart – Pump ID on Rows and Avg Distance on Columns. You need to apply formatting
Change the font – I set the font to Times New Roman at the Workbook level, to make life easier (Format menu > Workbook).
Set the worksheet background colour. I used #f5f1f0
Add a black border to each bar (via the Colour shelf)
Show labels on bars (via Label shelf)
Hide Avg Distance axis (uncheck show header on the Avg Distance pill)
Remove all row & column borders
Set the Axes Ruler on Rows to black
Set the Tooltips
A parameter is needed to store the selected pump
pSelectedPump – an integer defaulted to 1
With this, we can then create a field to indicate which pump is selected
Is Selected Pump?
[Pump ID]=[pSelectedPump]
Add this field to the Colour shelf and set the True option to black and the false option to match the background colour you used.
Building the Total Deaths within Avg Distance bar chart
We now need to identify if the death occurred within the average distance that has been calculated.
Number Deaths within Avg Distance
SUM(INT([Distance Pump to Death] <= [Avg Distance]))
This is a bit of short hand instead of writing IF [Distance Pump to Death] <= [Avg Distance] THEN 1 ELSE 0, and summing up the result. It relies on the the fact that the equation [Distance Pump to Death] <= [Avg Distance] returns a boolean of true or false, which can be converted to an integer as true =1 and 0 = false.
At a row level, the field just returns 1 or 0, but just plot this field against Pump ID, you get the desired count
And the easiest way to build this bar chart, is to duplicate the one created above (on the worksheet tab, right click & duplicate sheet), then replace the Avg Distance pill with the Number Deaths within Avg Distance pill (simply drag the latter from the data pane and drop directly onto the former in the columns shelf). Then adjust tooltips etc to suit. Doing it this way preserves (most of) the formatting.
Building the Map
We’re going to use 4 layers in creating the map, and I’m documenting from the bottom upwards (although this isn’t the order in which I actually built the viz in practice – I shuffled things round afterwards).
We’ll start with the density layer – the blue coloured area on the map.
On a new sheet, add Individual Death Location and Death ID to the Detail shelf. This should automatically create a map view with the auto generated lat & long fields. Then
Change the mark type to Density
Change the colour to Density Blue-Teal Light
Alter the Intensity to 75%, the Opacity to 60%
Increase the Size to maximum
Delete the info in the tooltip
We need to set the background map
Via the Map > Map Layers menu option, set the Style to Normal and uncheck all options and set Streets, Highways, Routes
Then set the worksheet background to the colour we’ve used before
Onto Layer 2 – the pump buffer circle.
We’ve already identified the selected pump, but now we need to get it’s location, and for that we need the lat and long of the selected pump.
Drag this Pump Buffer field onto the map and drop it onto the ‘Add a Marks Layer’ section that should appear. This will create an additional marks card.
Set the colour to the same background colour previously used, and drop the opacity to around 5%. Add Pump ID to Detail and Selected Pump Avg Distance to the Tooltip shelf. Adjust tooltip to match.
Onto Layer 3 – the pumps
Drag Pump Location onto the map and Add a Marks Layer.
Add Pump ID to Text and Location to Tooltip to the relevant marks card. Change mark type to circle, increase the size and change colour to red and add a dark grey border. Adjust text to be centred, and set the tooltips accordingly.
Onto the final layer now, the aggregated death locations. We need a further location field
Drag this onto the map to Add a Marks Layer then on the subsequent marks card add Loc ID to the Detail shelf. Change the mark type to circle, and add Deaths to the Size shelf – adjust accordingly. Change the colour to black with a white border. Adjust tooltip.
Adding the dashboard interactivity
Add the 3 sheets onto a new dashboard sheet. Create a new dashboard action to Change Parameter, that runs on select of any of the 3 charts, and targets the pSelectedPump parameter by setting it with the value from the Pump ID field.
Now if you select a pump on the map, or a bar on either of the bar charts, all the charts will reflect the selection made.
The other interactvity you may notice, is that on click, of the bars, or the pumps, the other data ‘fades’ (or the selected data is highlighted). We don’t want this.
To resolve, create a calculated field
True
True
and another
False
False
Add both these fields to the Detail shelf on both bar chart sheets and to the Detail shelf on the Pumps marks card.
Then on the dashboard, create a dashboard filter action which filters on selected fields setting True = False (see below). You’ll need to create one of these actions for each of the 3 sheets.
And hopefully, you now have a working viz. My published version is here.
I’ve been on annual leave for a few weeks. I’ve managed to catch up on all the challenges but haven’t blogged a solution for a while. It’s been a real struggle to get back into things to be honest – back to work, back to school, back to football clubs for my kids, and I’m wondering where I found the time before 😦
Trying to think about when/if I was going to get this blog out has caused me a bit of stress, which I don’t need, and so I need to change my mindset a bit…try to relax a bit … if I don’t manage to post a blog, just accept it and move on. I’ve also got to try to reduce the time it takes me to blog. I’m a ‘detail’ person, so often end up documenting to such a low level, but for my own sanity, I’m going to have to make an effort to be a bit briefer. I can’t guarantee I’ll stick to this though.. will just have to see how things pan out.
Going forward, I’m going to try to focus on the points that I think are key to the challenge, or those I found a bit tricksy.
So onto this week’s challenge. Sean Miller returned as guest poster with a COVID-19 related distance challenge.
Note – I connected to the provided hyper extract file rather than the csv file.
The core points I’m going to discuss are
Identify counties within n miles
How to select a county without set actions
How to handle ‘All’ counties being selected
Colouring based on ‘percentile’ of hospital beds
How to only show county borders of counties within selected range
Identify counties within n miles
The number of miles is stored within an integer parameter, n miles, that is defaulted to 100.
A county to act as the ‘start point’ is stored in a set, Selected County, based on the County, State field.
The long/lat coordinates of this selected county need to be captured.
Selected County Lat
{FIXED : AVG(IF [Selected County] THEN [Latitude] END)}
Selected County Long
{FIXED : AVG(IF [Selected County] THEN [Longitude] END)}
By using a FIXED LoD calculation, the values are stored against every ‘row’ in the data set.
With these, the starting point can be determined
Selected County Start Point
MAKEPOINT([Selected County Lat],[Selected County Long])
The position of every other county – the ‘destination’ / end point – also needs to be determined
End Point
MAKEPOINT([Latitude],[Longitude])
With these, the distance between can be computed
Distance
DISTANCE([Selected County Start Point],[End Point],’miles’)
And the counties can then be restricted by
Within n miles
[Distance]<= [n miles]
which can be used as a filter and set to True.
How to select a county without set actions
This is managed via the set control feature; right click on the Selected County set and choose Show Set to display the list of counties with the option to select which ones are in or out of the set. Change the display of the control via the dropdown arrow (top right) to be Single value dropdown which automatically provides as ‘All’ option or the ability to select a single set only.
How to handle ‘All’ counties being selected
When ‘All’ is chosen via the Set Control selector, this has the effect of adding all the State, County values into the set, which means we don’t really have a starting state. So the n miles parameter is essentially redundant. But we need to make the Within n miles filter understand this.
We can manage this by first identifying how many values we have in the set
Count Selected States
{FIXED : COUNTD(IF [Selected County] THEN [County, State] END)}
There will either be 1 or if All is selected, then they’ll be 3000+, and we use the FIXED LoD, so the total is stored against all rows.
We can then update our Within n miles filter to consider this value
Within n miles
([Count Selected States]=1 AND [Distance]<= [n miles]) OR [Count Selected States] > 1
This now returns true if either one State, County is selected and the other records are within n miles OR all the states are selected (the count is > 1).
Colouring based on ‘percentile’ of hospital beds
This caused me a little bit of headscratching. I assumed ‘percentile’ would be based on a percentage of the total num_staffed_beds (note I simply renamed this field to Hospital Beds), associated to the state,counties being displayed (ie within n miles). But after building the calculation I thought, and adding it to colour and choosing the Green-Gold colour range, I didn’t get the same spread as shown in the solution.
I messaged Sean to question whether I’d made the right assumption, but while waiting for a response, I did an online search for “Tableau Percentile Rank”, and quickly spotted that a Table Calculation exists. As you can guess, I don’t use this particular calculation very much at all 🙂
So to colour, add Hospital Beds , and simply add a Percentile table calculation
How to only show county borders of counties within selected range
When working with Maps, you can use the options within the Map Layers menu to select which features of the map should be displayed. One of these options is State/Province Borders, which you might think is what is needed.
However, this will show all the state borders, which is evident if you zoom out a bit, including those for states that don’t have counties within n miles.
This isn’t the requirement – we only want to show the borders of the states which have counties within the desired range. So instead, we don’t want to show any State/Province Borders via the Map Layers. And we’ll utilise a dual axis instead, by first identifying the states that are within n miles
States within n miles
If [Within n miles] THEN [State] END
Duplicate the Latitude (generated) field on the Rows, remove all the fields on that mark, and then add the States within n miles to the Detail shelf. Set the colour of this mark to white, and swap the Latitude (generated) fields if needed, so the States are on the top, as below.
Then make dual axis 🙂
And that’s the key points that I hope will help you solve this challenge. My published version is here, which is available to be downloaded. If there’s anything I haven’t covered that you’re not sure about, feel free to contact me.
Week 10 of #WOW2020 was set by guest challenger Sean Miller, who chose to demonstrate a ‘hot off the press’ feature released in v2020.1 (so having this version is a prerequisite to completing this challenge).
I was excited to see this as I don’t use maps often in my day job, and I love being able to have the opportunity to try the new stuff.
Sean provided references to two blog posts, which are a must read as they will definitely help guide you through the challenge, and explain in more detail what’s going on ‘under the bonnet’. I’m not therefore going to repeat any of this.
Sean provided 2 versions for the challenge with supporting datasets.
Intermediate challenge – Can you isolate pubs within 500m of a hotel?
For this we are provided with a set of hotels in London and a set of pubs. The requirement is to only include on the display the pubs which are within a 500m radius (ie buffer) of each hotel.
Join the data
The provided data consisted of a sheet of Pubs with a Lat & Lon field, and a sheet of Hotels with a LAT & LON field
These 2 data sets need to be Inner Joined together as
In the join clause window, you have the option to Edit Join Calculation which lets you type the calculation you need
Mapping the Hotels
Whilst the join has been made, we will need the ‘buffer’ calculation to display on the viz, so create
Buffer Hotel
BUFFER(MAKEPOINT([LAT],[LON]),500,”m”)
Then double click the Latitude (generated) and Longitude (generated) fields which will automatically display a map on screen.
Add Buffer Hotel to the Detail shelf and you’ll get the following (and the mark type will change to Map)
The circles look to be representing each hotel, but if you hover over one circle, all get selected. Add Hotel Name to Detail to allow individual selection.
Add Number of Records to the Label shelf, and format to suit.
Change the Colour of the mark to be pale orange and adjust the Opacity to suit.
Set the map background by choosing Map -> Map Layers from the menu and selecting Streets from the background style section
Mapping the Pubs
As with the hotel, we’re going to need the Pub Location spatial point to display on the viz, so create
Pub Location
MAKEPOINT([Lat],[Lon])
Duplicate/drag another instance of Latitude (generated) onto the Rows shelf.
On the second marks card, remove all the fields, and change the mark type to circle, then add Pub Location onto the Detail shelf, along with Pub Name.
You might be struggling to see the marks, but they are there – change the colour to grey, add a white border and adjust the size… found them?
The Tooltip on the pub marks, displays the distance from the hotel to the pub, so create
which is the distance in metres from the Pub Location to the Hotel Location (I could have used my Pub Location field and created a Hotel Location field to put into this calculated field.
Add Distance to the Tooltip field for the pub marks, and adjust to match.
Now make dual axis
Hotel List – Viz in Tooltip
On hover over the hotel buffer circle, a full list of the pubs in range is displayed. This a managed using another sheet and the Viz in Tooltip functionality.
Create a basic table with Hotel Name, Pub Name on Rows and Distance on Text. Type in the word ‘Distance’ into the Columns to make a ‘fake’ column label.
Hide Hotel Name from displaying by unchecking Show Header on the field, then Hide Field Labels for Rows and Hide Field Labels for Columns. Format to remove the column divider
Name the sheet Pubs or similar
On the Tooltip of the hotels buffer marks, adjust the initial text required, then insert the sheet by Insert -> Sheets -> <select sheet>
This will insert text as below
At the point it says ‘<All Fields>’, delete the text, then Insert -> Hotel Name
Now, if you hover over the buffer circle on the map, the list of pubs associated to just that hotel should display.
Note – when adding the sheets into the viz in tooltip, or changing the fields to filter by, always use the insert & select options rather than just typing in, as I find it doesn’t always work otherwise….may be just me though….
Phew! That’s the intermediate challenge completed (well once you’ve tidied and added to a dashboard of course.
onto the next….
Jedi Challenge – Can you find the pubs closest to a chosen hotel?
Sean provided a separate pre-combined dataset for this, as the display needs to show all the pubs, regardless of which hotel is selected, whereas in the intermediate challenge, the spatial join meant all the pubs outside of the buffer zones were excluded.
The map itself follows very similar principles. We need a dual axis, where one axis is plotting a selected hotel with it’s buffer, and the other axis, the pub locations.
The selected hotel is ultimately going to be derived from a parameter action, but we’ll set that later. For now, let’s just create the string parameter, Selected Hotel, to store the name of the hotel, which is just set to a ‘default’ value of “The Hoxton – Shoreditch”
Additionally, the buffer radius can be changed in this challenge, so we have another parameter, Buffer Radius, this time an integer with a max value of 500, and defaulted to 500 as well.
To draw the selected hotel with buffer on the map, we first need to isolate the selected hotel’s latitude & longitude, to determine the location, and store it against every row in the dataset via a LoD calculation
MAKEPOINT([Selected Hotel Lat],[Selected Hotel Long])
Now we know the location, we can create the buffer around it
Hotel Buffer
BUFFER([Selected Hotel Location],[Buffer Radius],’m’)
The Hotel Buffer and the Selected Hotel parameter are needed to display the hotel on the map.
We then need to create the fields used to display the pubs.
Pub Name
IF [Location Type]=’Pub’ THEN [Name] END
Pub Location
IF [Location Type]=’Pub’ THEN MAKEPOINT([LAT],[LON]) END
You should now be able to create the map following the steps outlined above in the intermediate challenge. One axis will show the buffer around the selected hotel, the other will show all the pubs.
The pubs need to be sized & coloured based on the distance from the selected hotel, so we need
Distance Selected Hotel-Pub
DISTANCE([Selected Hotel Location],[Pub Location],’m’)
Add this to the Size & Colour shelf of the pubs marks card, and adjust to suit (you’ll need to reverse the colour range). Also note, there are 2 pubs named Alchemist, so add Neighbourhood to the Detail shelf too to make sure the distance calcs returns the correct values. Update the tooltip on the pubs mark too.
Finally
update the tooltip on the pubs mark
add the Selected Hotel parameter to the Label of the hotel mark and adjust font to suit
remove the tooltip from the hotel mark
At this point the main map is built, but Sean has added a bit extra to this challenge, a bar chart to drive the hotel selection with a sort selector to drive the ranking of the hotels; all of this is wrapped up in a collapsible container – phew!
Let’s break this down and start with the bar chart.
Hotel Selector Bar Chart
Build a bar chart as follows :
Name, Yelp Rating (as discrete field), Price Rating on Rows
Yelp # of Ratings on Columns
Location Type = Hotel on Filter
Is Selected Hotel on Colour
Show mark labels so Yelp # of Ratings is displayed at the end of the bars
Adjust formatting to match (remove column/row lines, set the row banding, hide headers etc)
Set the Alias of the Price Rating field, so Null displays as <blank>
Name the sheet Hotel List or similar.
On a dashboard, add the Hotel List and the Map, so we can create the parameter action (Dashboard -> Actions -> Add Action -> Set Parameter) to interact between the list and map.
Clicking a hotel in the bar chart should now change which hotel is selected in the map.
Bar Chart Sort Selector
The bar chart can be sorted based on the 3 measures displayed; Price Rating, Number of Ratings, YELP Rating. We need to build the selector to allow a choice, and then change the bar chart based on the selection. This again is parameter actions, and builds on techniques used in previous WoW challenges blogged about here and here and here.
As a result, I’ll be relatively brief about how the selector is built, as the blogs should help with this.
I used 3 instances of MIN(0.0) on the Columns, and aliased the Measure Name of these to ‘ Yelp Rating ‘, ‘ Price Rating ‘, ‘ Number of Ratings ‘ (Note the spaces either side). I also adjusted the axis of each measure to make them all appear left aligned,(this was a bit trial & error).
I also needed a parameter Selected Sort Measure defaulted to ‘ Price Rating ‘
Three calculated fields are used to set the Shape of the displayed mark for each measure
Sort – Price Rating
[Selected Sort Measure] = ‘ Price Rating ‘
Sort – Number of Ratings
[Selected Sort Measure] = ‘ Number of Ratings ‘
Sort – Yelp Rating
[Selected Sort Measure] = ‘ Yelp Rating ‘
I also added the True = False url action trick to ensure the marks all appeared ‘selected’ when only one was selected.
To invoke the sort on the bar chart itself, create a calculated field
Chart Sort
CASE [Selected Sort Measure] WHEN ‘ Yelp Rating ‘ THEN SUM([Yelp Rating]) WHEN ‘ Price Rating ‘ THEN SUM([Price Rating Sort]) * -1 WHEN ‘ Number of Ratings ‘ THEN SUM([Yelp # of Ratings]) END
Note the Price Rating Sort field is multiple by -1 to ensure it displays from lowest to highest on the sort, whilst the other fields will display highest to lowest.
Alter the Hotel Name field on the Hotel list bar chart to sort descending by Chart Sort
Add the Sort Selector sheet to the dashboard, and add a parameter action
You should now be able to play around, selecting a sort option to change the order of the hotel list, then selecting a hotel to change the map.
Hiding the hotel list / sort selector
On the dashboard add a vertical container, then place the Sort Selector sheet and the Hotel List bar chart inside.
Remove the chart titles, set the background of the container to white, then set the container to be floating and select the container option to Add Show/Hide Button.
A Cross image will appear, select to Edit Button and change the button style to TextButton
In the Title section enter the required text for when the section is displayed (Item Shown) and then for when the section is collapsed (Item Hidden). Adjust the font too.
After hitting apply, the button section, will need resizing to get the text to display
The show/hide functionality needs to be manually selected on Desktop. When on server the interactivity will work. So to close the container, on the button menu, select Hide
and the container with the selector and the bar chart will disappear
Now it’s all just about finalising the dashboard to display all the objects in the appropriate locations. The colour/size legend and Buffer parameter are also within a container, which is floated and positioned bottom left.
Hopefully I’ve covered everything. There’s a fair bit going on in this Jedi version!
This week’s #WorkoutWednesday challange was a mash up with my other favourite weekly challenge, #PreppinData, the idea being to manipulate the data for the challenge in Tableau Prep, then create the viz in Tableau with the output.
I don’t plan to document this challenge to the nth degree, but just pull out the bits that I think are of most relevance… always happy for anyone to reach out though if there’s something I don’t mention that they’re not clear on 🙂
Anyway, so onto the challenge. Let’s start with the data prep….
#PreppinData Week 25
If you haven’t yet discovered it, #PreppinData is a set of weekly challenges to help you learn Tableau Prep. Carl Allchin and Jonathan Allenby set this up inspired by the success of #WorkoutWednesday. I had barely used the tool before they set the challenges up, and it’s definitely a great resource to get you started.
So this week’s challenge is defined here, and consisted of the following input
a file with 1 row per concert involving either Ed Sheeran or Ben Howard (ie the artists) including concert location
a file with lat/long data for each location
a file with the artists’ hometowns and lat/long
The basic requirement was to
Join the concert/location data together
Find the fellow artists who performed at the concert along with Ben and Ed, and expand the data so there was 1 row per concert per fellow artist as well as row for the artist themselves.
Remove some ‘obvious’ duplicate rows
Add in the home location for the artist
1.Join concert/location data
The location data contained Latitude and Longitude data in a single field LongLats in the format below
Location
LongLats
Rochester Hills, MI
42.680396, -83.133654
Camden, NJ
39.925640, -75.121151
So before combining with the concert data, this needed to be split up, which I did using the Split Values -> Automatic Split function
This automatically created 2 fields, splitting the data by the comma (,) which I renamed Long & Lat.
Warning : I don’t use mapping data that often and am therefore not overly familiar with what a typical lat/long number looks like. Given the field was originally named LongLats, I assumed the first number was Longitude and the second Latitude. This was an incorrect assumption, which I only discovered later when I displayed in Tableau to find the marks spread up and down the viz rather than across. So I came back and rectified my flow with the correct labels.
I then joined the concert data with the location data using a ‘left outer’ JOIN, to ensure all concert data was retained, even if a match to location couldn’t be found.
2.Find fellow artists and expand 1 row per concert & fellow artist
This bit took some thinking.
The Concert field contained a string of text in varying forms, that was either
blank
contained the name of the artist on their own, eg Ben Howard
contained the name of the concert eg Multiply Tour
contained a string of ‘fellow artists’, where each artist was separated by a forward slash ( / ). The number of artists listed could vary considerably
eg Ed Sheeran / Taylor Swift or
Mumford & Sons / Felice Brothers / Ben Howard etc
The instruction was that if the Concert field didn’t contain a slash ( / ), then there were no fellow artists.
I knew that ultimately I was going to need to split the Concert field up, pivot the data to expand 1 row into 1 or more rows, and then delete the unnecessary rows which had no fellow artist data. This is because when a field is split, you end up with however many columns are necessary for the longest string. So for the examples above, 3 split fields would be created due to example 2 above, but example 1 would also have 3 fields, with the 3rd field being blank. When this data is then pivoted, there would be 3 rows per original row, so I’d want to delete the rows where fellow artist was then blank. HOWEVER, if there had been no fellow artist in the first place (ie the Concert field did not contain a / ), I also needed to ensure I wouldn’t inadvertently be deleting rows of data I actually wanted.
I approached this in the following way (by all means there may be something more straightforward, but this is just how my brain was working at the time 🙂 )
Created the Fellow Artist field, and set it to <blank string> ( ” ) if the Concert field was NULL or did not contain a slash ( / ). Otherwise I stored the Concert data.
This involved the use of FIND() and ISNULL() functions
Removed the name of the artist if he also appeared in the listing (as I wasn’t sure it was always there).
This involved the use of REPLACE() to replace the artist with <blank string>
TIP : To manipulate a single field in multiple steps, you don’t need to keep creating a field with a new name each time, and then remove/rename. You just name the calculated field the same as the one you’re working with (eg Fellow Artist in this instance), and you can refer to the field itself in the calculation. This isn’t something you can do in Tableau Desktop, so might not be obvious if you’re familiar with that.
Added the name of the artist back in to every row, including the ones where there was no artist listed. This may seem odd, given I’d just removed it, but as I said above I couldn’t be sure that the artist themselves was always present in the original list, and I wanted to ensure the Fellow Artist field in every row contained the name of the artist once only. This would ensure that when it came to deleting rows later, I wouldn’t remove rows I still needed (ie those relating to Concerts with no fellow artists).
If the Fellow Artist did not contain ‘ / ‘ , then store Artist else add Artist/ to the front of the Fellow Artist field.
I then used the Custom Split function to split all fields separated by /
This generated several SPLIT calculated fields based on the maximum number of ‘ / ‘ found in the string. NOTE – if my input data then changed and there was an even longer string containing more / , then I’d have to manually add further calculated fields to cope with this.
I could now add a PIVOT step to transpose the data from 1 row into multiple, ensuring I was pivoting columns to rows and adding all my ‘split’ fields as the columns to pivot
This generated 15 rows for every original row, with the automatically named field Fellow Artist Split containing either blank or the name of a single artist. Adding a step to then filter all the rows where Fellow Artist Split <> ” then removed all the unnecessary rows in the data.
3. Remove some obvious duplicate rows
Determining what the authors had defined a ‘duplicate’ took a couple of attempts. They had stated in the requirements that the provided Concert ID field could not be relied upon, but that was the only clue. In my mind the Date, Artist , Fellow Artist Split and Location was probably enough, but although when eyeballing the data, this seemed to be a sensible grouping, this didn’t yield the expected number of records. In the end it seemed Date, Artist, Fellow Artist Split, Concert, Location,Venue were the fields deemed to define uniqueness.
So I created an AGGREGATE step, grouping the data by the fields listed above, and then I stored the minimum value of Concert ID against each summarised row. I then joined this aggregation back to the step before the aggregation joining on the Concert ID fields. This resulted in the expected number of records stated in the challenge.
I then applied a few tidy up steps – removing unwanted columns, renaming columns as appropriate and most importantly (to match the requirements), setting the Fellow Artist field to NULL if it only contained the name of the Artist himself.
4. Add in Home Location of Artist
This was a simple step to Inner JOIN to the Artist’s hometown location file, using the Artist field as the linking field.
I then saved the output. Complete flow below.
#WorkoutWednesday Week 31 – hub and spoke chart
So now the data was prepped, I could start to tackle Lorna’s challenge to visualise Ed Sheeran’s & Ben Howard’s tour data. The full challenge is defined here, with the core requirements of
Group Locations into Regions
Create Hub & Spoke maps
Create a Word Cloud of Fellow Artists
In addition to the above, there are also charts summarising the number of concerts per artist per region, and a cumulative count over time. These are pretty standard / typical outputs, so I’m not going to describe how to build these, but you may notice that the numbers I present differ from those on Lorna’s solution.
This is due to the way I decided to calculate the Number of Concerts (# Concerts). As part of the #PreppinData challenge, I still had a Concert ID in my output. Due to the way I approached the challenge, the Concert ID ended up being a unique value per Concert in my output, so I chose to use this as my way to count the # Concerts (using COUNTD() function).
I did check Lorna’s solution to see what she did, but decided to proceed with my definition, knowing that ultimately it didn’t really make that much difference to the visualisations required.
1. Group Locations into Regions
I did this simply by manually selecting marks on a map.
Simply double clicking on my Lat & Long fields, then unchecking Aggregate Measures on the Analysis menu automatically presented me with a map of the world. I then selected the marks in Europe with the intention to use the automatic Group function, but it wasn’t available.
This puzzled me momentarily, but then I realised I needed a dimension (blue pill) in the view, so I added Location to the Detail shelf, and now I had the Group function visible. I repeated the process with the other marks to create multiple groupings which I renamed appropriately.
2. Create Hub & Spoke Maps
This was the primary focus of this week’s challenge, providing the ability to try out some very new features – the MAKEPOINT() and MAKELINE() functions which have only been released in v2019.2.
Being someone who doesn’t work with map data very much at all, Lorna quite helpfully added a pointer to this Tableau blog which introduced the new functions and how to use them.
So following the instructions, I created a field called Route as follows:
MAKELINE() requires a start & end point. In the above the start is the point associated to the artist’s hometown, defined by the first MAKEPOINT() function, and the end is the point associated to the location of the concert, defined by the second MAKEPOINT() function.
Simply double-clicking on this new field, automatically generated the display below
This then needed to be split by Artist, filtered & coloured by Region, sized by # Concerts, and have Venue and Concert added to the Detail shelf. The Tooltip was then added and the sizing adjusted to get a better differentiation.
To get the circles at each destination, a dual axis map is required, where the second axis is just showing the point related to the Concert location. This is basically the 2nd part of the MAKELINE function above, so I had to create a new field Destination which is
MAKEPOINT([Lat],[Long])
In hindsight, to make things clearer, I probably should have created a Start field representing the starting point, and a Destination field representing the end point, and then adjusted the Route field to reference these…
Anyway, to get the circle, I did the following:
Duplicated the Longitude(generated) field on Columns
On the second Marks card, replaced the Route field with the Destination field, and removed the # Concerts from the Size shelf
Set the circle to have a white border (via the Colour shelf properties)
Made the chart dual axis
Set the background style of the map to Dark (via the Map -> Map Layers menu)
Unticked all the map option features to prevent the pan/zoom/selector toolbar from showing when you hover over the map (via the Map -> Map Options menu)
NOTE – I think there may be a bug in v2019.2.2 as whilst the map toolbar stopped being visible on the worksheet view, when I later added the map to the dashboard, it was visible again 😦 Testing the property in v2018.3.3, which is the version I use for work, I didn’t get this behaviour.
I tidied up by applying various other formatting features to meet the required display, then duplicated the sheet and adjusted the filters for other regions.
3. Create a Word Cloud of Fellow Artists
I’ve never actually had a need to create a word cloud before, so figured it would be useful to document, just as much as for my future reference 🙂
Add Fellow Artist to Text shelf
Add Fellow Artist to Size shelf, then change to be measure Count
At this point, the chart will change to be a treemap style, so you need to change the Mark type to Text
Then just apply the various formatting/colour etc to match the requirement
Obviously there’s more views that need to be built for this challenge, and then they all need to be placed on a single dashboard with the relevant formatting and dashboard action being applied to make the Word Cloud change when a summary value is selected.
Hopefully these tasks should be quite straightforward and this is already a very lengthy post. If you’ve got this far, then thanks very much for taking the time to read 🙂
Donna + DataViz 