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.

• Buffer Spatial Calculations – Marc Reid (Zen Master)
• Competitive Analysis with Buffer Calculations – Filippos Lymperopoulos (Tableau Product Manager)

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

(Pubs data) MAKEPOINT([Lat],[Lon])

INTERSECTS

(Hotels data) BUFFER(MAKEPOINT([LAT],[LON]),500,’m’)

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

Distance

DISTANCE(MAKEPOINT([Lat],[Lon]), MAKEPOINT([LAT],[LON]), ‘m’)

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

Is Selected Hotel?

[Name]=[Selected Hotel]

Selected Hotel Lat

{FIXED : MIN(IIF([Is Selected Hotel?], [LAT],NULL))}

Selected Hotel Long

{FIXED : MIN(IIF([Is Selected Hotel?], [LON],NULL))}

Selected Hotel Location

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 Text Button

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!

My published versions are here.

Happy vizzin’!

Donna