Tag: Map Layers

Can you build a trellis of Profit Ratio Gauges?

Donna Coles3 Comments

It’s Community Month at #WOW2024 HQ, and for the first challenge, Jack Hineman (a long time participant of WOW) asked us to create gauge charts using map layers. Not only that, he wanted them displayed in a trellis format, with a specific requirement to ensure the number of columns was always >= number of rows displayed. Errr…..

This was tough! I tried to start the challenge one evening and after pouring through Ken Flerlages’s blog post that was referenced, reading the hints, and looking at Jessica Moon’s Tableau Public page referenced, I was none the wiser. The blog post did not mention map layers at all in building a gauge, and was purely mentioned as inspiration for the design of the gauge.

I happen to have some time off work, so reattempted the challenge the next day. After several hours, I got there, somehow! I did another google : “Gauge charts in Tableau” and hit upon this blog, which gave me a few pointers, although most of the time it really was a lot of trial and error based on Jack’s hints, and to be honest I surprised myself that I actually hit all the requirements, except one, without the need to look at the solution.

The one thing I had to look at was the calculations required to get the trellis to always have more columns than rows. My ‘go to’ formula didn’t work. More on that later.

As for my solution… well it’s a solution…. how elegant/efficient it is – who knows. It was something built very much in stages as I tried to get my head around what was being asked. As I rebuild as part of the process I go through when writing this blog, I may find ways of improving what I did to start with. I will do my best to explain what I think is going on, what my thought process was, but apologise in advance if you get to the end of all this, and still don’t have a ‘scooby’ 😦

Strap yourself in! This is going to be a long one!!!!

Modelling the data

Connect to the provided data source and create relationship calculations to relate Manufacturers to Gauge_Definition with 1=1 and Gauge_Definition to Gauge_Points with 1=1 as well.

Understanding the data

Let’s start by looking at the data provided. Jack provided 3 data sets:

Manufacturers

A simplified instance of Superstore just listing Manufacturers with Sales & Profit data.

Gauge Definition

A data set of 1 row essentially containing some ‘constants’ to be referenced within the challenge

  • Gauge Success Amount = 0.15
    • Profit Ratios >= to 0.15 (15%) are deemed successful. This is essentially the Goal indicator value.
  • Gauge Concern Amount = 0
    • Profit Ratios >=0 (but < 0.15) are deemed a concern
  • Gauge Start Profit Ratio = -0.3
    • The left hand point on the semi-circular gauge should indicate a -30% profit ratio
  • Gauge End Profit Ration = 0.3
    • The right hand point on the semi-circular gauge should indicate a 30% profit ratio
  • Gauge Success Pct of Gauge = 0.75
    • 15% Profit Ratio represents 75% of the gauge displayed (ie 0% of gauge = -30% Profit Ratio and 100% of gauge = 30% profit ratio)
  • Gauge Concern Pct of Gauge = 0.5
    • 0% Profit Ratio represents 50% of the gauge displayed.

Gauge Points

This is essentially a template/scaffold to help build the gauge and the various features on the gauge. It defines all the points that need to be plotted and in most cases then connected to create the various ‘shapes’ displayed eg the gauge semi circle for the actual profit ratio and the legend indicator; the small angled rectangle that represents the goal ‘reference line’; the positions for the 3 labels.

To start getting an understanding, let’s just focus on Point Type = Actual and Point Segment ID = Background

This is the data to build the complete grey semi circle of the main gauge. 50 rows represent points on the inner arc of the semi circle (Point Arc = In). They all have a Point Radius = 0.43 (ie the distance from the 0,0 centre position of a circle to the bottom edge of the gauge is 0.43). The other 50 rows represent points on the outer arc of the semi circle (Point Arc = Out). They all have a Point Radius = 0.58 (ie the distance from the 0,0 centre position of a circle to the top edge of the gauge is 0.58). Point Angle Rads defines the angle in radians (rather than degrees) from the circle centre to edge of the circle. The Point ID defines the order to ‘join the dots’ when the points are made into a polygon.

Here’s a diagram to help try and explain the maths we’re going to need to use based on the data we have

For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. We know the angle, and we know the radius, so we can use trigonometry to work that out, and then use the MAKEPOINT() function in Tableau to covert that into a spatial/geometric field to use on a map.

Let’s do this in Tableau.

Create field

X

[Point Radius] *SIN([Point Angle Rads])

Y

[Point Radius] * COS([Point Angle Rads])

Note – based on my diagram above, X = a and would be derived from the Cosine of the angle, while Y = o and be based on the Sine of the angle. However, Jack gave hints based on the above calcs which hold true if you adjust the diagram and assume the angle is positioned between the y-axis and the radius, rather than the x-axis and the radius.

Now create the point

Geo

MAKEPOINT([X],[Y])

On a sheet, add Point Type to Filter and set to Actual, and add Point Segment ID to Filter and set to Background.

Double click on Geo to automatically add Longitude and Latitude fields to the sheet.

We have the basics of a semi-circle… not in the right direction, but it’s something… Add Point ID to Detail, then click the Swap Axis button and hey presto…

It appears a bit more ‘ovel’ than circular as the axis aren’t aligned, so don’t worry about this – set the display to Entire View will help. Then change the mark type to Line, move Point ID to Path and then change mark type to Polygon.

We now have a filled semi circle. We’re not going to use this sheet, but hopefully, this has helped a bit with some fundamental understanding.

When building we’re going to be using map layers (and at this point, we can’t add a layer to this sheet). We’ll also be defining calculations based on which feature of the viz we’re focussed on, as we can’t apply filters to the sheet (if you remove the ones applied, it will look a little crazy!). But if you change the Point Segment ID filter to Goal, you’ll get the shape of the goal indicator ‘reference line’.

You might want to play around with the filters to examine the behaviour.

Are you still with me…? Take a break, grab a cuppa, we haven’t even started building yet, but I’ll still be here when you get back 🙂

Setting up the map layers

Using Jack’s hints, create a field to help ‘initialise’ the map layers

Zero

MAKEPOINT(0,0)

Double click this to create a basic ‘map’ with a single point.

Then drag another instance of Zero onto the canvas and drop on the Add a Marks Layer section that appears

You’ll now have 2 marks layers, which means whatever we now do, we can always add more.

Building the Gauge Background & Legend layer

Note – in building I ended up with more mark layers than Jack suggested. I’ve subsequently seen other versions but am sticking to what I managed for now.

The first layer I’m going to build is the ‘grey’ semi circle of the main gauge and the coloured legend ‘inner’ semi circle.

I want to identify those points only.

Geo: BG-Legend Layer

IF [Point Type] <> ‘Label’ AND [Point Segment ID] IN (‘Background’, ‘Concern’, ‘Failure’, ‘Success’) THEN [Geo] END

On the Zero marks card, drop this field directly on top of the COLLECT(Zero) to replace it. Add Point ID to Detail, then flip the axis using the switch axis button. Add Point Segment ID to Colour and adjust the colours of the Background, Failure, Concern and Success values to suit.

Change the mark type to polygon, and move Point ID to Path. Name this layer BG & Legend

Building the Actual Layer

Create a new field

Profit Ratio

{FIXED [Manufacturer]: SUM([Profit])/SUM([Sales])}

and format to % with 1 dp.

We need to understand where on the gauge, the Profit Ratio for each Manufacturer falls. We know that the gauge starts at -30% Profit Ratio (ie 0% of the gauge is equivalent to -30% Profit Ratio) and the gauge ends at +30% Profit Ratio (ie 100% of the gauge is equivalent to +30% Profit Ratio). Therefore if the Manufacturer’s Profit Ratio >= 30% it fills 100% of the gauge, anything less needs to be partially through. We can calculate this (using one of Jack’s hints) with

PR % of Gauge

([Profit Ratio] + [Gauge End Profit Ratio]) / ([Gauge End Profit Ratio]- [Gauge Start Profit Ratio])

To then determine the angle in degrees (and again using Jack’s hints), we want to find the proportion of 180 degrees that the PR % of Gauge represents, and then take off 90 degrees based on the gauge rotation.

PR Angle (Degrees)

([PR % of Gauge] * 180)-90

We can then convert this to radians

PR Angle Rads

RADIANS([PR Angle (Degrees)])

This gives us information to help determine a singular point on the gauge we need to ‘draw’ up to. But I want to ‘draw’ a polygon that goes from the left end (-30% mark) to this point, and for this, I need to know all the other points up to that point.

The way I came up with isn’t what Jack did. My result means I don’t get an exactly accurate marker, but it is so close to it’s position, and is ‘good enough’ for the viz type and how it’s being displayed.

To understand what I did, build out a tabular sheet that is filtered to Manufacturer = Hon and Point Type = Actual, and shows Manufacturer, Point Segment ID and Point ID on Rows with Point Angle Rads, Profit Ratio and PR Angle Rads as measures.

The Profit Ratio for the Manufacturer =Hon is 6.4% which is at an angle of 0.34 radians around the semi circle.

You can also see that while we have values for the Point Angle Radians field associated to the Point Segment ID = Background, we don’t have any for the Point Segment ID = Actual, as this is what we’re trying to find out.

Given that I know that all the Point Angle Radians values associated to the Point Segment ID = Background build a complete semi circle, I figured, to display up to my ‘actual’ Profit Ratio, I just want to get all the points associated to background which are less than the PR Angle Rads value.

PR Point Angle Radians

IF [Point Angle Rads] <= [PR Angle Rads] THEN [Point Angle Rads] END

Pop this into the table, and when you scroll down, you’ll see I’ve only got values in my new field up to where the PR Angle Rads is less

Using this field, I’ll create some new X & Y fields which I can make make into a spatial field

X (actual)

[Point Radius] *SIN([PR Point Angle Radians])

Y (actual)

[Point Radius] * COS([PR Point Angle Radians])

Geo – Actual

IF [Point Type] = ‘Actual’ AND [Point Segment ID] = ‘Background’ THEN
MAKEPOINT([X (actual)], [Y (actual)])
END

On the Zero(2) marks card, Replace the COLLECT(Zero) pill with the Geo – Actual pill by dragging the latter and dropping it directly on the former. Add Point ID to Detail.

It’s drawing the points for a complete semi-circle, as every Manufacturer is being included. To help get the rest of the display right, for the various permutations, add Manufacturer to filter and filter to Hon, Bush, Logitech and Xerox. Add Manufacturer to Columns too. You should now see the marks stop at various positions around the arc. The filters will be adjusted later when we tackle the trellis.

Change the mark type to polygon and move Point ID to Path. Rename the layer to Actual.

For the colouring, we need to determine the RAG status of each Profit Ratio – where does the PR % of Gauge sit in comparison the constants we know

Actual RAG

IF [PR % of Gauge] <= [Gauge Concern Pct of Gauge] THEN ‘Failure’
ELSEIF [PR % of Gauge] <= [Gauge Success Pct of Gauge] THEN ‘Concern’
ELSE ‘Success’ END

Add this to the Colour shelf and adjust accordingly.

Building the Goal Indicator Layer

Create a new spatial field

Geo: Goal Layer

IF [Point Type] = ‘Actual’ AND [Point Segment ID] = ‘Goal’ THEN [Geo] END

and then drag this onto the canvas and drop on the Add A Marks Layer option.

Change the mark Type to Polygon and add Point ID to path. Add Point Segment ID to Colour and adjust the colour of the Goal value to suit. Rename the layer to Goal.

Building the Label layer

Create a new spatial field

Geo: Labels

IF [Point Type] = ‘Label’ THEN [Geo] END

and then drag this onto the canvas and drop on the Add A Marks Layer option. Add Point Segment ID to Detail. 3 marks should now displayed in the positions we need them

This bit took a bit of time to get right, as I had 3 requirements I wanted to satisfy: 1 – display a text or a numeric field as a label depending on what label I wanted to display (I attempted to have a single ‘label’ field converting numbers to strings with the relevant formatting, but the Profit Ratio % just wouldn’t show how I wanted when converted to string); 2 – adjust the colour of (some) of the labels depending on the RAG status of the profit ratio value; 3 – adjust the size of the Profit Ratio label based on how many gauges were displayed.

I needed several label fields

Label: Goal

IF [Point Segment ID] = ‘Goal’ THEN [Gauge Success Amt] END

formatted to % with 0 dp.

Label: Manufacturer-Fail

IF [Point Segment ID] = ‘Manufacturer’ AND [PR % of Gauge]<=[Gauge Concern Pct of Gauge] THEN [Manufacturer] END

Label: Manufacturer-Concern

IF [Point Segment ID] = ‘Manufacturer’ AND ([PR % of Gauge]<=[Gauge Success Pct of Gauge] AND [PR % of Gauge]>[Gauge Concern Pct of Gauge]) THEN [Manufacturer] END

Label: Manufacturer-Success

IF [Point Segment ID] = ‘Manufacturer’ AND [PR % of Gauge]>[Gauge Success Pct of Gauge] THEN [Manufacturer] END

Label: PR-Fail

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]<=[Gauge Concern Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Label: PR-Concern

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]>[Gauge Concern Pct of Gauge] AND [PR % of Gauge]<= [Gauge Success Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Label: PR-Success

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]>[Gauge Success Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Add all these fields to the Label shelf, and adjust the label so that all are positioned on the same line, with no spaces, and add a carriage return beneath the text. Colour each field accordingly (DO NOT ADJUST THE FONT SIZE).

Change the mark type to text and align the label top centre. Rename the mark type to Labels and Disable Selection

To adjust the size of the labels, create a new parameter which we’ll need for the trellis.

pShowTop

integer parameter, defaulted to 7 which is a range from 5 to 81 with a step size of 1.

Show the parameter.

Create a new field

Label: Size

IF [Point Segment ID] = ‘BAN’ THEN [pShowTop]
ELSEIF [Point Segment ID] = ‘Manufacturer’ THEN 60
ELSE 80 END

We want the size of the BAN label to decrease as the number of manufacturers displayed increases.

Add this filed to the Size shelf as a continuous dimension (green pill, not aggregated).

Edit the Size legend so that sizes vary by range, the range is reversed, and the range starts from 1 to 81. Adjust the mark size range slider to a suitable start and spread.

As you change the value of the pShowTop parameter, the Profit Ratio BAN should adjust in size.

Format Sales and Profit to be $ with 0dp and to display as () when negative, then on all marks cards, add Manufacturer to Detail and Sales, Profit, Profit Ratio and Actual RAG to Tooltip, and adjust Tooltip on all the layers to suit.

Finally remove all gridlines/zero lines/axis ticks and hide the longitude & latitude axis. Hide the null indicator.

Building the Trellis

So now we’ve got the core viz nailed, we need to address the layout, which is to show a gauge for each of the top n manufacturers based on Sales. This means we need to have the gauges indexed/ranked from 1 to n based on total Sales, and then arrange in a grid so that top left is the manufacturer with the highest sales, and bottom right is the manufacturer with the lowest sales. For this we need to assign a row and column number against each manufacturer.

There are multiple blog posts about creating trellis charts. My go to post has always been this one by Chris Love, especially when the requirement is for the trellis to be dynamic (the number of rows/columns can vary) depending on the number of items to be displayed.

However in this instance, using the calculations referenced in the blog doesn’t meet the requirement of ensuring there are more columns than rows. This was an area that got me stumped. As a result, I finished the viz with a version that utilises my ‘go to’ calculations (published here), before then looking at Jack’s solution to get the calculations, which are

Cols Count

//If Rounded value = Value without Round then no remainder, use that number
if SIZE()/round(SQRT(SIZE()),0) = int(SIZE()/round(SQRT(SIZE()),0)) THEN SIZE()/round(SQRT(SIZE()),0)
//Otherwise add 1 to the number of columns
ELSE int(SIZE()/round(SQRT(SIZE()),0)) + 1
END

Size() is reflective of the number of items (in this case Manufacturers) to be displayed… as I write this down, for this specific instance, you could probably replace SIZE() with the parameter pShowTop.

Rowsv2

//For Each Manufacturer, what Row should it be in the Trellis?
//Rank the Manufacturer, Find the Integer portion when dividing by # of Columns
int((INDEX()-1)/ [Cols Count])

Colsv2

//For Each Manufacturer, what Column should it be in the Trellis?
//Rank the Manufacturer, Find the Remainder when dividing by # of Columns
int((INDEX()-1) % [Cols Count])

Make both Rowsv2 and Colsv2 discrete.

Add Rowsv2 to Rows and Colsv2 to Columns. Remove Manufacturer from Columns. It’ll look a bit odd, but be patient.

Edit the Manufacturer pill on the Filter shelf. On the General tab, select None, to remove everything from the filter, then on the Top tab adjust to be based on the top pShowTop by Sales

Now edit the table calculation associated to the Rowsv2 pill, so that it computes by specific dimensions and every field except Point ID is selected. Ensure Manufacturer and Point Segment ID are listed at the top in that order. Set the level to be Manufacturer and apply a custom sort based on Sum of Sales descending

As this is a nested table calculation, select the drop down arrow at the top and apply the same settings to the nested Cols Count field.

Then do exactly the same again for the Colsv2 table calculation settings. If all has been applied successfully, then you should get a grid

where you can then adjust the pShowTop parameter

Hide the Rowsv2 and ColsV2 fields from displaying (uncheck show header). Show the Longitude axis, and fix it to start at 0 but end ‘automatic’. Then hide the axis again (this ensures the arc lands on the row divider).

Then add the viz to a dashboard. My published version based on the trellis using Jack’s calculations is published here.

If you’ve made it to the end – well done! It was a bit of a marathon to do the challenge and another to write this blog. I’m sure it’s been quite an effort to read too, but hopefully you’ve learnt something, and I’ll certainly be referencing this again when a map layer and/or gauge based scenario occurs again.

Happy vizzin’!

Donna

Excel at Tableau Interactivity with Dynamic Zone Visibility

Donna ColesLeave a comment

Erica set the #WOW2024 challenge this week, asking us to recreate this ‘drillable’ functionality within a single dashboard.

To achieve this, I used 4 sheets

  • A large map
  • A small map
  • A table of data
  • A navigational control

along with parameter actions to drive the interactivity, and dynamic zone visibility to control what displayed when.

Building the large map

On a new sheet, double click on State/Province to automatically generate a map of the United States – if this doesn’t display by default, then make sure the location of your map settings are referencing the USA (Map menu > Edit Locations). Hide the ‘unknown’ indicator displayed bottom right. Change the mark type to map to create a filled map. Change the colour to a grey (I used #c0c0c0). Add State/Province to the Filter shelf and select Arkansas. Show the filter.

Click on the Tooltip button, and delete all the text in the dialog.

Now drag City onto the map and drop it on the section that appears labelled Add a Marks Layer. On the ‘City’ marks card that now appears, change the mark type to circle. Move City to the Label shelf and then also add State/Province to Detail. Change the colour of the circles to dark grey (I used #767f8b).

Update the Tooltip on the City marks card. Turn off all the map options to stop the pan/zoom map control options from appearing (map menu -> map options -> deselect all the options). Remove all row/column dividers.

Name the sheet Map-Large or similar.

Building the Small Map

Duplicate the Map-Large sheet, and name the new sheet Map-Small. Remove all background layers so just the state outline remains (Map menu -> background layers -> set washout to 100%).

On the Label shelf of the City marks card, uncheck show mark layers so the name of the city is not displayed and click on the Tooltip shelf and uncheck show tooltips, so they aren’t displayed either.

On this map, we need to highlight a selected City. For this we need a parameter to capture the City, and a calculated field to identify which City has been selected. We will also need a parameter to capture the State/Province to ensure the interactivity works as required.

pSelectedState

string parameters defaulted to ” (ie empty string)

pSelectedCity

string parameter defaulted to ” (ie empty string)

Is Selected City

[City] = [pSelectedCity]

Add Is Selected City to the Colour shelf of the City marks card.

Show the pSelectedCity parameter and type in ‘Fayetteville’. The colour legend should display values for True & False. Adjust colours to suit.

Also add Is Selected City to the Size shelf and adjust the sizes accordingly.

Building the table

On a new sheet, add

  • State/Province to Filter and filter to ‘Arkansas’
  • Add Is Selected City to Filter and filter to True
  • Add Customer Name, Order ID and Product Name to Rows
  • Right-click on the State/Province field in the Filter shelf and apply to worksheets > selected worksheets and select the Large Map & Small Map sheets. This makes the filter shared between all those sheets.

Create a new field

Unit Price

SUM([Sales])/SUM([Quantity])

and add to Text.

Drag Quantity onto the canvas and drop on the ‘unit price’ column when you see show me appear. This will automatically add Measure Values to Columns and Measure Names to Filter.

Do the same for Sales. Reorder the pills in the Measure Values box so they are arranged as required.

Add subtotals (Analysis menu > Totals > Add all subtotals), then display at the top (Analysis menu > Totals > Column Totals to Top).

Right-click on the Customer Name pill in Rows and uncheck Subtotals from the context menu.

Format the table: set the worksheet background colour to light grey and set the total (pane & header) background colour to a shade darker.

Adjust the width and height of the columns and rows to suit.

Uncheck show tooltip from the Tooltip shelf, and name the sheet Table or similar.

Building the Navigation sheet

The ‘back to main map’ navigational display is actually a worksheet rather than a navigational object on a dashboard, as we need to apply parameter actions to it in order to reset some parameters.

On a new sheet, double click into the space on the marks card below the shelf buttons, and type ‘X’

Change the mark type to Text and move the X field to the Text shelf. Update the text to include the additional words, adjust the font size and then align middle centre. Uncheck Show tooltip.

Create the following fields

City – Reset

”

State – Reset

”

and add both to the Detail shelf. Name the sheet Navigation or similar.

Building the dashboard

Using horizontal and vertical layout containers, arrange all the objects on a dashboard. You will need to adjust the background colour of some objects and the padding to get everything looking as expected. This can be quite tricky and is very hard to explain in a blog. The item hierarchy in the image below will help give you an idea

To draw horizontal or vertical lines, add a blank object to the layout container, change the background colour, reduce the padding to 0 and then adjust the height or width of the object so it is very narrow.

These are the properties associated with the red ‘tab’ in the heading – the background colour is red, inner and outer padding is 0, width is 4px and the object is contained within a horizontal container

For the City/State title, reference the pSelectedState and pSelectedCity parameters, along with a unicode symbol which I copy and past from here.

Adding the interactivity

Add the following dashboard actions

Set State

on select of the Map-Large sheet, set the pSelectedState parameter passing in the value of the State/Province field. When the selection is cleared, retain that value.

Set City

on select of the Map-Large sheet, set the pSelectedCity parameter passing in the value of the City field. When the selection is cleared, retain that value.

Reset State

On select of the Navigation sheet, set the pSelectedState parameter passing in the value of the State – Reset field. When the selection is cleared, retain that value.

Reset City

On select of the Navigation sheet, set the pSelectedCity parameter passing in the value of the City- Reset field. When the selection is cleared, retain that value.

Click around on the dashboard and sense check the parameters appear to be behaving – ie if you click on a city on the large map sheet, the table and small map should update, and if you then click the navigation sheet, the table shouldn’t show anything, and the small map shouldn’t show anything highlighted.

Hide and show the objects

Finally, we need to only show the required objects depending on the actions taken. For this we need some additional calculated boolean fields. Go back to any sheet, and create the following calculated fields

City is Selected

[pSelectedCity]<>”

City Not Selected

[pSelectedCity]=”

Note, these fields just have to exist, they don’t need adding to any sheet.

Navigate back to the dashboard.

Click on the Map-Large object, and on the Layout tab, check the control visibility using value option, and choose the City Not Selected value.

This means that when City Not Selected is true (ie the pSelectedCity parameter is empty), this object will display. When City Not Selected is false (ie the pSelectedCity parameter has a value), this object will be hidden.

Depending on what you’ve been clicking, this object might disappear immediately.

Click on the Table object, and do the same steps, but this time choose the City is Selected field. This means the table will only show then the pSelectedCity parameter has a value.

Apply the same settings so the small map, the table title, and the navigation sheet only show when City is Selected. Note – depending on how you have built the dashboard, you may find you can apply the setting once against a container which contains all the objects you want to hide rather than against each individual object.

I also chose to only display the Select a State filter control when the City Not Selected was true.

And hopefully, that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you make dynamic indexed bars & measures?

Donna ColesLeave a comment

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 Island Group 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

Selected Province Location

MAKEPOINT((IIF([Is Selected Province],[Latitude],NULL)),(IIF([Is Selected Province],[Longitude],NULL)))

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 .

Happy vizzin’!

Donna

Can you make a pie chart?

Donna ColesLeave a comment

In his final challenge for 2022, Luke set this challenge asking us to recreate this pie chart. Although not mentioned in the challenge text, there was a hint on the splash page that map layers would be required.

I’ve only really used map layers in other #WOW challenges, and they actually involve maps. This challenge was obviously a bit different – utilising a functionality built for one purpose in an entirely different way. I remembered when map layers were first released there was a big buzz about the potential possibilities, and had seen some examples, but I’d never gotten round to trying out for myself, so this was the perfect opportunity (and one of the many plus points as to why I love doing #WOW challenges).

So where to start… good question. If you read up on the official Tableau KLs relating to map layers, it’s all about geography, and while the data source does have geographic data (State, City etc), they aren’t relevant in this case. In my ‘googling’ I found the following resources of use

The first 2 blogs helped me understand the need for the use of the MAKEPOINT function, Sam Parson’s Pies & Doughnuts viz helped me understand the calculation I’d need for the MAKEPOINT function, and the final blog post really helped with putting in all together.

Feel free to ignore the rest of this blog and use the above to help you out 🙂

Building the first map layer

The first step is to create the geometry field we need to base this off of.

Zero

MAKEPOINT(0,0)

Double click this field, and it will automatically add the point centrally onto a map with Longitude and Latitude fields automatically generated too.

This is a key step in getting things started and enabling the use of map layers which we’re going to utilise.

Add Segment to Colour and adjust the colours. Change the mark type to pie chart and add Sales to angle. Increase the size to be as large as possible.

However the size isn’t as big as we need. To increase further use Ctrl-Shift-B (windows) or Cmd-Shift-B (mac) to increase the size further (Ctrl-B / Cmd-B) to reduce. This trick I found in the Interworks blog above. All Tableau key shortcuts are listed here.

Add Segment to the Label shelf. This completes our lowest map layer.

Building the second map layer

Drag Zero onto the map canvas, and drop it over the Add a Marks layer section that displays. This will add a second marks card called Zero(2).

On the marks card that is named Zero, rename it to Outer Pie.

On the marks card named Zero (2) rename to White Circle. Change the mark type to Circle, change the Colour to white and increase the size to leave a narrow border of the coloured pie underneath.

Building the third map layer

Drag another instance of Zero onto the canvas and add another marks layer. Rename Zero (3) to Inner Pie. Change the mark type to Pie chart and add Segment to Colour and Sales to angle. Increase the Size so it’s just smaller than the white circle. Change the opacity of the colour to 70% and add a white border (Colour shelf).

Adding the labels in the pie chart

The simplest way to do this is just to label the inner pie chart with the required fields and then manually move the labels from outside the pie to the desired location. However if your data changed in some way, eg the proportion of the slices changed, the labels may not be where you wanted without further tweaks.

So instead I’ve added a 4th map layer.

Add Zero once again to the sheet and add a marks layer. Rename this marks card to Labels -Inner Pie. Change mark type to Pie chart and add Segment to Detail, Sales to Angle and Sales to Label. Create a new calculated field

Pct

SUM([Sales]) / TOTAL(SUM([Sales]))

Format to % with 0 dp and and add to Label. Adjust the fonts of the labels so the Sales value is larger.

Increase the size of the pie chart so the labels are positioned ‘nicely’ within the segments of the Inner pie

Reduce the opacity of the ‘label’ pie chart to 0% and set the mark layer to be disabled

Finishing up

Adjust the tooltips to display as required (you’ll need to add Pct to the Tooltip shelf on both the Outer and Inner Pie mark cards).

Then remove the map background via the Map menu -> Background Maps -> None. Hide all axis and remove all gridlines/zero lines/row/columns dividers. You should now be left with a ‘clean’ pie chart which can be added to a dashboard.

My published viz is here.

Happy vizzin’!

Donna

Where were COVID-19 cases most active?

Donna Coles2 Comments

In this week’s #WOW2022 instalment, Luke set this map-based challenge to visualise COVID-19 cases across the continental US states on 30 December 2021.

Each ‘pyramid’ on the map represents a county within a state, with the height and colour of the pyramid indicating the volume of cases recorded.

Luke provided a workbook with some pre-modelled data as a starting point, so I used that.

To familiarise myself with the data I built out a basic table, focusing on the State of Idaho (ID) only.

Clat10 and Clon10 are the latitude and longitude positions of the County and metrics.caseDensity is the key measure used to indicate the volume of cases.

To draw the pyramid, we need to plot 3 marks for each County, 1 for each point in the triangle. This means we need to densify the data so we have multiple rows for each County. If we add Point as a discrete dimension (blue pill) to the table, we can see we can generate up to 20 rows per County.

This is obviously excessive – we only need Points 1-3, so for simplicity, we can add Point to Filter and limit to just 1, 2 and 3. I added the filter by duplicating the Point pill from Rows (hold ctrl and then click and drag the pill from Rows to Filter – this added the filter as a discrete dimension and I could just select options 1, 2, 3).

With only 3 points, we can now build the lat and long coordinates for each point. I am assuming point 1 is the bottom left point, 2 is the top point, 3 is the bottom right.

Triangle Long

CASE [Point]
WHEN 1 THEN [Clon10] – 0.1
WHEN 2 THEN [Clon10]
WHEN 3 THEN [Clon10] + 0.1
END

As the requirements stated the base of pyramid was 0.2 longitude points wide, then the bottom left and bottom right points need to be 0.1 points to the left or right of the County longitude, while the peak is aligned centrally.

Triangle Lat

CASE [Point]
WHEN 1 THEN [Clat10]
WHEN 2 THEN [Clat10] + ([metrics.caseDensity]/60)
WHEN 3 THEN [Clat10]
END

The latitude of the left and right bottom points are aligned with the County latitude, while the height is an additional 1/60 of the metrics.caseDensity value above the County longitude.

Change both the Triangle Lat and Triangle Long fields to be of the Geographic role type of Latitude or Longitude accordingly (right click field -> Geographic Role -> Latitude/Longitude).

Adding these into the table, we can see how the coordinates are forming

So with this, we can now build the map

Add State to Filter and exclude AK, HI, PR (Alaska, Hawaii, Puerto Rico).

Double click Triangle Lat then double click Triangle Long to add them to the sheet – a map should automatically be presented.

Add County to Detail, and a mark should appear for every County.

Add Point to Detail and change to a dimension – there should now be 3 marks per County forming triangles. Zoom in to really see this.

Change the mark type to line and the points all join up to form the pyramid.

Add metrics.caseDensity to Colour, then edit the colour. Choose the red-gold colour palette, then click the Advanced button, and first change the centre to 75, then set the start & end value to 0 and 200.

Modify the Tooltip and reduce the Size of the line. Zoom back out

Finally amend the map background ( Map menu -> Background Layers). Uncheck base and land cover, check terrain and coastline. Uncheck Country/Region Names and State/Province Names.

Hide the null indicator and then place on a dashboard. Done!

My published viz is here.

Happy vizzin’!

Donna

Can you make a hexbin map?

Donna ColesLeave a comment

Sean Miller was back this week to set this challenge to recreate a ‘rat sighting’ map using hexbins. I’ve only used hexbins in other #WOW challenges, so needed a bit of a refresher (the previous challenges pre-dated my own blog, so I couldn’t use myself as a reference). A quick google for ‘tableau hexbins’ and I found a variety of articles that provided the refresher needed.

Sean also used the opportunity to apply some other crucial skills – adding custom shapes and custom colour palettes, which I recommend is the first step you do in completing this challenge.

Adding custom shapes

Download the hexagon shape provided by Sean, and then save it into a folder in your …My Tableau Repository\Shapes directory. I have a folder called ‘Custom’ where I place random shapes I need. This post will help you out if you’re having difficulty with any of this.

Adding custom colour palette

Open a text editor such as Notepad, then open the preferences.tps file that is located in your .. My Tableau Repository directory. Copy & paste the block of code provided by Sean between the opening and closing <preferences> tag. Save the file and close the text editor. This post will help if you’re having trouble.

Building the map

The provided rat sighting data set contains a Longitude and Latitude value for every rat sighting since 2010.

Hexbins provide a way to group (bin) these Lat & Long values together. The size of the bin is typically determined by a parameter, so lets first set this up

pRatio

integer or float parameter which I set to a default value of 250 (Sean didn’t specify the value he’d used, but trial and error suggested to me this looked ‘about right’).

Now we can build the bins

HexbinX

(HEXBINX([Longitude]*[pRatio], [Latitude]*[pRatio])) / [pRatio]

Edit the geographic role of this field to be mapped to Longitude (right click on field -> Geographic Role).

HexbinY

(HEXBINY([Longitude]*[pRatio], [Latitude]*[pRatio])) / [pRatio]

Edit the geographic role of this field to be mapped to Latitude.

Add HexbinX to Columns and HexbinY to Rows. Modify each field so that it is a continuous dimension.

Change the mark type to Shape and select the hexagon shape you saved earlier.

Add the auto generated ‘count of dataset’ (Count of Rows) field to Colour, and adjust the colour to use the OrRd-5 palette you added earlier. Ensure to set the palette to Reversed.

Set the Tooltip so it doesn’t display anything on hover, and add Borough to the Detail shelf (this is needed for the interactivity later). Hide the nulls indicator that displays in the bottom left (right click -> hide).

On the Map menu, select Map Layers, and set the background style to dark.

Using the map controls, zoom in and pan to the left slightly, so the coloured area is mainly central and there’s less ‘sea’ at the bottom. It’s likely that you may need to adjust further once you’ve placed the map on the dashboard. But before publishing, we want to turn the map controls off, to prevent a user from shifting the display (Map -> Map Options -> uncheck all options).

Building the Bar Chart

On a new sheet, add Borough to Rows and Count of Rows to Columns. Add Borough to Filter and exclude Null and Unspecified. Sort the rows descending. Adjust the colour to suit.

Show mark labels, but only display the max & min values. Format the Count of Rows pill so the labels are displayed in K to 2 dp.

Hide the axes, right align the row label headings, adjust the tooltip. Hide the column heading. Remove zero lines and axis rulers. Set the background colour of the worksheet to ‘None’ (ie transparent).

Further formatting is required, but this is best done after the sheet is added to the dashboard, as you’ll lose visibility of the text at this point.

Creating the dashboard

Add the map to a dashboard, and remove all the additional containers/legends etc that are added, and the sheet title. Then add the bar as a floating object and position bottom left. Fit to entire view. Edit the title so it contains the ‘on hover’ instruction and format in light grey font.

Now you can format the row labels, the row headings and the gridlines to be appropriate colours – light grey rather than white.

Add a dashboard highlight action that on hover of the bar chart, highlights data in the Map

Then add floating text boxes and add the title and description. I ended up adding a text box just containing the OH, and then another position just below containing RATS! and then the description, as otherwise the carriage return between OH & RATS! made the spacing too wide. I used the controls on the layout tab to ensure both text boxes were positioned at the same x-coordinate.

Hopefully, you should now have a beautiful looking viz. My published version is here.

Side note – When I first started building this I tried from memory, and didn’t quite get things right. I then adjusted various fields as described above, but when I then tried to add the Count of Rows to Colour, I was only ever getting a value of 1 against each bin. I double & triple checked all the calcs and couldn’t see any issues. It was very weird. I simply ended up closing down my workbook and starting again from scratch and all was fine. I’m just letting you know this in case you too come across any oddities during your build, and things don’t behave as expected. It meant, what was ultimately quite a straight forward build (once I got the calcs right), ended up involving more time and head-scratching than really required 😦

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 build an app to visualise wildfires?

Donna ColesLeave a comment

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.

Now we need to deal with the zoom requirement.

I wasn’t entirely sure about this, so had a bit of a search and found Jeffrey Shaffer’s blog post How to create a map zoom with buffer calculation in Tableau – bingo!

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…

My published viz is here.

Happy vizzin’! Stay Safe!

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 structure the unstructured?

Donna Coles1 Comment

As soon as I saw that Candra’s challenge for this week was going to involve Regular Expressions (RegEx), I gave a little groan. RegEx just isn’t my thing 😦 I only ever seem to use them for these challenges, and not in my working life, so have minimal experience. I always think I should focus some time on learning them properly, but other things just end up taking priority. Ho Hum…

So most of my time was spent trying to wrangle the info I needed to identify ‘how many bedrooms’ each property had. I did a bit of googling to try to find the right expressions I think I needed, used the regex101 site to test my expression to find certain patterns of text against some of the data in the Description field, and then tried to plug that into a calculated field in Tableau to extract the data I needed.

But I couldn’t get it to work 😦 I could find matching text using the REGEXP_MATCH function, but when I then tried to use the REXP_EXTRACT functions I couldn’t get anything out…

So I ended up having to look at the solutions that had already been published by the time I started, Candra’s, Lorna Brown’s and Sam Epley’s. I just needed to get my head round what I was obviously doing wrong and give me some pointers. All 3 had slightly different approaches. I absorbed, then closed their workbooks and attempted again from memory. With a lot more trial and error I got somewhere… it isn’t perfect and has some mismatches from the others (but they don’t all match each other either…).

Once I’d got a grouping for each property, the actual Tableau stuff was quite straightforward…

  • Identifying the ‘Number of Bedrooms’
  • Building the Histogram
  • Adding the Average Price
  • Building the Map
  • Adding the Interactivity

Identifying the Number of Bedrooms

So the way I approached this, was to try to identify all the various permutations that represented the word ‘bedroom’ and replace it with the word ‘Bedroom’. But one of the options was BR or br, and the Description field contained html markup with the term <br />. I didn’t want all these to become ‘bedroom’, so I got rid of them all first,

Desc with Bedroom

REGEXP_REPLACE(LOWER(REPLACE([Description],'<br />’, ‘ ‘)),’bedroom|br |bdrm|bed|bd|br, |br/|rooms’,’ Bedroom’)

Firstly, replace any occurence of <br /> with a space, then replace any occurrence of the text bedroom or br<space> or bdrm or bed or bd or br<comma> or br<forward slash> or rooms with the word Bedroom.

I basically added more options to the or statement (identified by the | separator), as I went on examining the descriptions that were left. Using the LOWER function meant that bedroom or Bedroom or BedRoom etc would all be covered with one option.

Then I attempted to extract the number of bedrooms or identify as a studio

Studio | Beds

IF CONTAINS(LOWER([Desc with Bedroom]), ‘studio’) THEN ‘Studio’
ELSEIF REGEXP_MATCH(LOWER([Desc with Bedroom]),’\d bedroom’) THEN REGEXP_EXTRACT(LOWER([Desc with Bedroom]),'(\d+) bedroom’)
ELSEIF REGEXP_MATCH(LOWER([Desc with Bedroom]),’\d bedroom’) THEN REGEXP_EXTRACT(LOWER([Desc with Bedroom]),'(\d+) bedroom’)
ELSEIF CONTAINS(LOWER([Desc with Bedroom]), ‘six bedroom’) THEN ‘6’
END

If the revised description contains the word ‘studio’ then assume its a Studio.

Else if the revised description contains a number (\d) followed by 2 spaces then the word ‘bedroom’ then extract the numbers (\d+) that occur before the word bedroom. The brackets around the \d+ is what is used to identify what bit of the matching pattern to extract… this is the bit that I didn’t really know about and why I couldn’t get things to work.

Else if the revised description contains a number (\d) followed by 3 spaces then the word ‘bedroom’ then extract the numbers (\d+) that occur before the word bedroom. This just happened to be another pattern that occurred and meant some records didn’t get picked up by the prior statement. There’s probably a better way of doing this in one statement…

Finally, if the revised description contains the text ‘six bedroom’ then assume the property has 6 rooms.

This logic seemed to get a match against every record although it’s not 100% accurate, but it was close enough given my struggles.

I then wanted to get the rooms grouped

Room Grouping

CASE [Studio | Beds]
WHEN ‘Studio’ THEN ‘Studio’
WHEN ‘1’ THEN ‘1 Bedroom’
WHEN ‘2’ THEN ‘2 Bedrooms’
WHEN ‘3’ THEN ‘3 Bedrooms’
WHEN ‘4’ THEN ‘4 Bedrooms’
ELSE ‘5 or more Bedrooms’
END

I planned to use this field as my filter, but in doing so the value listed alphabetically, so Studio ended up at the bottom of the list.

To resolve this I created a parameter which meant I could define the order I wanted :

pBedroomSelector

And then I created a new field to use for the filter

Filter Room

[pBedroomSelector] = ‘All’ OR
[pBedroomSelector] = [Room Grouping]

I could then add this onto the filter shelf of the sheets I needed to build, setting the value to True.

Building the Histogram

For this chart, we need to ‘bin’ the Price of each property into groups of $100 ranges. However if we use the built in ‘bin’ function, the field created can’t be referenced in other calculations, and I needed to do this. So instead I determined the ‘lower’ value of the range by

Price per Night Min

FLOOR([Price]/100) *100

Divide the price by 100, round down to the nearest whole integer (so 1.9 will round down to 1), then multiply the result by 100.

And given that, I can then calculate

Price per Night Max

[Price per Night Min]+100

I also created a ‘friendlier’ field to store the number of properties

# of Listings

COUNT([listings copy_listings copy])

which is just a reference to the auto generated field created when you connect to the data source.

With these I can plot the histogram

  • Price per Night Min on Columns (set to discrete, continuous)
  • # of Listings on Rows
  • Mark type of Bar
  • Size set to be Fixed with a width of 100
  • Filter Room on the Filter shelf, set to True.
  • Adjust the colour via the Colour shelf and set a white border
  • Show the pBedroomSelector parameter
  • Add Price per Night Max to the Tooltip shelf and set to be an attribute.
  • Set the Tooltip accordingly and format gridlines, axes labels etc

Adding the Average Price

I wasn’t entirely sure what the average price on Candra’s solution represented, so I chose to go for the average price of the properties in the filtered selection; that is of all the 2-bedroom properties for example, find the average price per night, based on the total price per night of all the properties divided by the number of properties. ie I was looking for these values in the 3rd column.

But I couldn’t simply add the Price field aggregated to Avg to the bar chart. Doing so gave me different values per Price per Night Min grouping.

I just want the value on the grand total line spread across the all the data in the chart. So I created

Window Avg Price

WINDOW_SUM(SUM([Price])) / WINDOW_SUM([# of Listings])

This table calculation, set to compute by Price per Night Min gives the value I want across all rows of data

Add Window Avg Price to the Detail shelf of the histogram, set the calc to compute as above. Then you can add a reference line to the Price per Night Min axis.

Building the Map

To build maps you need fields that are geographic data types. For me, the Longitude field was already set, but I had to manually set the Latitude field (right click -> Geographic Role -> Latitude).

Once done, the map could be quickly built by double-clicking the Longitude field, then double clicking the Latitude field, then adding Name and Listing URL to the Detail shelf, and Price to the Tooltip shelf. Finally set Filter Room = True to the Filter shelf.

I then adjusted the colour of the circles, reduced the opacity to 50% and added a border (all via the Colour shelf).

I also added Area Code Boundaries via the Map -> Map Layers menu to get the map style Candra had used.

Adding the Interactivity

Add the 2 sheets to a dashboard. Each chart can be used to filter each other. This functionality can easily be added by clicking on the context menu of the dashboard object, and selecting Use as Filter. A filter dashboard action will automatically be added. Do this for both charts.

The final requirement, is for a link to the actual listing to be available from the map tooltip. This is a dashboard URL Action (Dashboard -> Actions -> Add Action -> Go to URL). Set as below

The words in the Name field will what is displayed on the tooltip.

The layout requires use of containers, background colours and a bit of padding. This is typically a bit of trial and error to get this right. You can check out my published version here.

Happy vizzin’! Stay Safe!

Donna