Lorna Brown provided a refresher on map layers and spatial calculations for the #WOW2022 challenge this week. You’ll need Tableau Desktop v2020.4 or later to complete this due to the functionality incorporated.
The data set provided contains rows of origin & destination airports including the latitude and longitude values for each.
I found there were some fields in the provided data set which I don’t think should have been there. It meant when I came to naming some of my fields, I had to be more creative due to the existing ones.
We’ll start off by setting up the calculations required.
Firstly we need to create a spatial object out of our Origin and Destination airports.
Next we’re going to need to have a line to connect these
Origin-Dest Line
MAKELINE([Origin],[Destination])
We’re going to need ‘buffer’ to define the circle displayed. The size of this is to be defined by the user, so we’ll need a parameter
pBufferSize
integer parameter defaulted to 1000
and with this, we can then define the buffer
Buffer from Origin
BUFFER([Origin],[pBufferSize], ‘miles’)
Finally we need to determine whether the destination airport is within the buffer ‘zone’.
Within Buffer
[Distance (Miles)]<=[pBufferSize]
Note: Distance (Miles) already existed within the downloaded data set. I was expecting to have to calculate the distance myself, given the nature of the challenge. If I had had to create it, I would have used the calculation DISTANCE([Origin], [Destination], ‘miles’)
This is all we need to build the viz. I’m going to start from the top – down ie Origin airport -> Destination airport -> Lines – >Buffer.
On a new sheet, add Origin Airport to Filter and set to LAS. Also add Within Buffer to the Filter shelf and set to True.
Then drag Origin onto the main canvas area and drop it when you see ‘Show Me’ displayed on the cursor. This will automatically add all the required fields into the relevant locations
Change the mark to a circle, increase the size and set the colour to black. Add Origin Airport to Label and Origin Name to Tooltip, and adjust the tooltip accordingly.
This is the first map layer.
Now drag Destination onto the canvas and drop it on the ‘stacked layer’ icon when you get the Add a Marks Layer option appear
This will create a new marks card on the left, and is the 2nd marks layer.
Add Destination Name to the Detail shelf, then change the mark type to square. Add Destination Airport, Origin Airport and Distance (Miles) to Tooltip and adjust tooltip to match.
Finally move the Destination marks card so it is below the Origin marks card. Click on the Destination card and drag to below the Origincard and drop when you see the orange line appear
Now drag Origin-Dest Line onto the canvas and drop it on the ‘stacked layer’ icon when you get the Add a Marks Layer option appear to create the 3rd marks layer. This will add all the ‘spokes’.
Change the colour to grey. Add Destination City to the Detail shelf. Add Origin Name, Origin Airport, Distance (Miles) and Destination Airport to the Tooltip and adjust accordingly. Finally, drag this marks card so that it is now below the Destination marks card.
Final layer now – the buffer zone.
Drag Buffer from Origin onto the canvas and drop it on the ‘stacked layer’ icon when you get the Add a Marks Layer option appear to create the 4th marks layer. Adjust the colour to light grey, and reduce the opacity to suit. Then move this marks card to the bottom, so its beneath the Origin-Dest Line marks card.
And that’s it. Add the sheet onto a dashboard, and show the Origin Airport filter as a Single Value dropdown so only 1 origin airport can be selected at a time. I also customised the control so the All option did not display either. Add the pBufferSize parameter to the display too, and test the viz by changing the size and the airport.
Ann Jackson’s husband Josh (@VizJosh) set the challenge this week, to build an ‘application’ to help visualise the scale of wildfires; that is when a fire is said to be 5000 acres, you can use the app to view how that compares to an area of the world you may know, so you can really appreciate just how large (or small) the fire is.
I have to hold my hand up here, and say that after reading the requirements several times, I was absolutely stumped as to where to start. We were provided with some ‘data’ to copy which consisted of 5 rows, which I duly copied and pasted into Desktop, but I then like ‘what now….?’ I knew I needed something geographic to build a map, but couldn’t understand the relevance of the 5 rows… I’ve said before I don’t use maps that often, so was unsure whether there was something I needed to do with this data. After staring at the screen for what seemed like an age, I ended up looking at the solution.
The data is just ‘dummy’ data and is just something to allow you to ‘connect’ Tableau to. You can’t build anything in Tableau without a data source. It could just have been 1 row with a column headed ‘Dummy’ and a value of 0. If it had been that, it might have been more obvious to me 🙂
Defining the parameters
Building the map
Apply button
Dashboard Actions
Defining the parameters
Ultimately the ‘data’ being used to build the viz is driven by parameters – the Location selector and the Latitude & Longitude inputs.
pLocation
An integer list parameter that stores values, but displays worded locations – wherever you choose. I opted for my hometown of Didcot in the UK alongside locations Josh had used, mainly so I could validate how the rest of the ‘app’ would work when I came to build it.
pLongitude
Float input, defaulted to the longitude of location 1 (ie Didcot) above.
I just googled Didcot Latitude and Longitude to find the relevant values
Note – Longitude W means an input of -1 * value. Similarly for Latitude S needs to be a negative input.
Then I created
pLatitude
Since we’re talking about parameters, there’s a couple more required, so lets create them now
Acres
Integer parameter defaulted to 5000
pZoom
Integer, list parameter with the values below, defaulted to 2.
Building the map
Now we have some lat & long values (in the pLatitude and pLongitude parameters), we can create some geographic data needed to build a map.
Location
MAKEPOINT([pLatitude], [pLongtitude])
This gives us the centre point which we want to build the ‘fire size’ buffer around. For this we need the calculation JOsh kindly provided :
Acres to Feet
SQRT(([Acres]*43560)/PI())
and then we can create the buffer
Fire Size
BUFFER([Location],[Acres to Feet],’ft’)
Double click on this field and it should automatically create you a ‘map’
Adjust the map ‘format’ via the Map > Map Layers menu option. I chose to set it to the dark style at 20% washout, then ticked various selections to give the details I needed (I added and removed options as I was testing against Josh’s version). I also set the colour of the mark via the Colour shelf to be pale red.
Also, as per the requirement, turn off the map options via Map > Map Options menu, and unchecking all the selections.
So this is the basic map, and you can input different lats & longs into the parameters to test things out.
The zoom had to be x times the size of the circle on the map, so achieved by
Zoom
BUFFER([Location],[pZoom] * [Acres to Feet],’ft’)
Add this a map layer (drag field onto the map and drop onto the Add a Marks Layer section that displays)
This has generated a 2nd circle and consequently caused the background map to zoom out. We don’t want this circle to show, nor to be selected, so on the Colour shelf, set the Opacity to 0%, and the Border and Halo to None. To prevent the circle from showing when you hover your mouse on the map, you need to Disable Selection of the Zoom marks card
Apply Button
On a separate sheet, double click into the space below the Marks card, and type ‘Apply’ into the resulting ‘text pill’ that displays, and then press return.
This will create a blue pill, which you can then add to the Label/Text shelf. Align the text to be middle centre
This view is essentially going to act as your ‘Apply’ button on the dashboard. When it is clicked on, we want it to take the Lat & Long values associated to the place listed in the pLocation parameter, and update the pLatitude & pLongitude parameter values.
For this, we need a couple of extra calculated fields
Location Lat
CASE [pLocation] WHEN 1 THEN 51.6080 WHEN 2 THEN 40.7812 WHEN 3 THEN 51.5007 WHEN 4 THEN 48.8584 END
Note – as before, all these values were worked out via Google as shown above.
Location Long
CASE [pLocation] WHEN 1 THEN -1.2448 WHEN 2 THEN -73.9665 WHEN 3 THEN 0.1246 WHEN 4 THEN 2.2945 END
Add both these fields to the Detail shelf of the Apply sheet.
Dashboard Actions
When you add the 2 sheets to the dashboard, you then need to add parameter actions to set the values of the pLongitude & pLatitude parameters on click of the Apply button
Set Lat
A parameter action that runs on Select of the Apply sheet, setting the pLatitude parameter with the value from the Location Lat field.
You need another action Set Long which does a similar thing by passing the Location Long field into the pLongitude variable.
Finally, you don’t want the ‘Apply’ button to look ‘selected’ (ie highlighted pale blue) once clicked. Create calculated fields True = True and False = False and add both of these to the Detail shelf on your Apply button sheet.
Then add a dashboard filter action that uses Selected Fields and maps True to False
Hopefully, this should provide you with all the core features to get the functionality working as required. Ultimately once I got out of the starting blocks, it wasn’t too bad…
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.
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!
Donna + DataViz 