Tag: DISTANCE()

Datafam Europe Live: London’s Top Attractions

Donna Coles1 Comment

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

Selected Attraction Point

MAKEPOINT([Attraction Latitude], [Attraction Longitude])

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

Then create

Buffer Attraction (x miles)

BUFFER([Selected Attraction Point], [pDistance(miles)],’mi’)

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

DISTANCE([Selected Attraction Point], [Other Attraction Point], ‘mi’)

and then

Other Attraction Within Range

[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 bottom pTop 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 bottom pTop 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.

Happy vizzin!

Donna

Can you use multiple mark layers?

Donna ColesLeave a comment

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.

Origin

MAKEPOINT([Origin Latitude],[Origin Longitude])

Destination

MAKEPOINT([Destination Latitude],[Destination Longitude])

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 Origin card 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.

My published version of the viz is here.

Happy vizzin’!

Donna

Can you visualise the Cholera Outbreak?

Donna ColesLeave a comment

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.

Selected Pump Lat

{FIXED: MIN(IIF([Is Selected Pump?],[Pump Lat],NULL))}

This essentially gets the latitude of the selected pump and sets that value across all the rows in the data.

Selected Pump Long

{FIXED: MIN(IIF([Is Selected Pump?],[Pump Lon],NULL))}

And with these we can get

Selected Pump Location

MAKEPOINT([Selected Pump Lat],[Selected Pump Long])

We also need to get the average distance of the selected pump spread across every row

Selected Pump Avg Distance

{FIXED :SUM( IF [Is Selected Pump?] THEN [Avg Distance] END)}

Now we have this, we can build the buffer field

Pump Buffer

BUFFER([Selected Pump Location],[Selected Pump Avg Distance],’m’)

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

Death Location

MAKEPOINT([DeathLat (deaths aggregate.csv)], [DeathLon (deaths aggregate.csv)])

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.

Happy vizzin’! Stay Safe!

Donna

Can you use spatial BUFFER() functions in two ways?

Donna Coles1 Comment

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

(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