Kyle set this challenge while I was on my holidays. And because I was away and short on time, I’m cheating a bit this week. I’m not going to provide a full solution guide, but am writing this blog to maintain consistency 🙂
The challenge was very specific to just the new dynamic spatial parameters feature, which is well documented. I used these two resources to familiarise myself with the feature and to build the challenge
It was Kyle’s turn to set the challenge this week. Like him, I don’t have a need to use map / spatial data much, so whenever there’s a WOW challenge involving them it always makes me think a bit harder (and usually refer to some documentation).
Connecting to & modelling the data
I followed the links in the challenge requirements and downloaded the Shapefile option from each page
This downloaded zip files (one did take some time to download). I then extracted the zip files which generated several files.
In Desktop, I then chose to connect to the Spatial file option and when I navigated to the file location where I had unzipped the data, only the .shp file was available for selection.
I connected to the School District Characteristics data source first, then clicked the ‘carrot’ to access the context menu of the data source, and selected open to access the physical layer of the data canvas
I then clicked Add against the connections section to add another spatial file data source, selecting the School Neighbourhood Poverty file this time and changed the join type between the two data source fields to use the intersects option.
Building the bar chart
On a new sheet add Statename to the Filter shelf, and select Washington. Add Lea Name to the Rows. Create a new field
and add this to Columns and sort descending. Widen each row slightly, and increase the width of the Lea Name column a bit. Remove all gridlines, and remove the axis title, and hide the Lea Name column heading. Update the Tooltip as required and update the sheet title.
Building the map
Create a new sheet. Add the Geometry field from the School District Characteristicsset of data to the Detail shelf.
Go back to the bar chart sheet, and update the Satename filter so that it also applies to the sheet you’re building the map on. The map should now be filtered to Washington too. Add Lea Name to the Detail shelf and # Schools to the Tooltip and adjust accordingly.
From the Map > Background Layers menu option, uncheck the options on the Background Map Layers section, so just the Cities and Streets, Highways/Motorways.. options remain selected. Adjust the Colour of the map (via the colour shelf)
Then drag the Geometry field from the School Neighbourhood Poverty data source section onto the canvas and drop it when the Add a Marks Layer section appears
This will add a second marks card. Name this marks card Schools and the other one Districts.
On the Schools marks card, add Name to the Detail shelf and then update the tooltip as required. Remove the row & column dividers.
Adding the interactivity
Add the 2 sheets onto a dashboard side by side and show the Statename filter. Add a dashboard filter action
Filter District
On Select of the bar chart, target the Map passing all fields. Show all values when selection is cleared.
Clicking on a bar should now filter the map and ‘zoom in’ just to that district with the relevant school marks visible.
Lorna’s challenge this week involved the use of nested LoDs (level of detail calculations) and ViT (viz in tooltip). The requirements were very brief (just 4 bullet points), but that didn’t mean this would be simple!
Each bar in the bar chart represents the number of states where the labelled sub-category had the most sales. ie there were 13 states where Phones was the sub-category with the most sales.
Let’s just look at the data : on a sheet add State/Province and Sub-Category to Rows and Sales to Text. Sort the list descending. From the screen shot below, we can see that for Alabama, Chairs has the most sales, in Alberta it’s Fasteners and for Arizona it’s Phones.
We need to get to a point where we can display a single row for each State/Province and the most popular Sub-Category.
We’ll start by creating a field that stores the sales for the State/Province & Sub-Category combination
Sales for State & Sub Cat
{FIXED [State/Province], [Sub-Category]: SUM ([Sales])}
On the table view above, add this field to Text instead of the Sales pill. The data displayed should be the same.
What we then need to do is to identify the maximum sales value for each State/Province. We do this with another Fixed LoD which references the above Fixed LoD (ie a nested LoD expression)
Max Sales for State & Sub Cat
//This returns the value of sales for the subcategory with the largest sales per state {FIXED [State/Province] : MAX([Sales for State & Sub Cat])}
Pop this into the table and you should see that the value for every row in this field is the same for each State/Province and matches the value of the first row in each pane
We can then use this to identify the Sub-Category where the values in the two columns match
Sub Cat with Max Sales for State
IF ([Sales for State & Sub Cat]) = ([Max Sales for State & Sub Cat]) THEN ([Sub-Category]) END
Add this onto Rows and the Sub-Category with the largest sales per State/Province is only listed once per pane.
Now remove Sub-Category from the view and you get 2 rows per State/Province – one Null, and one with the Sub-Category name we want.
Filter out the Nulls, by adding Sub Cat with Max Sales for State to the Filter shelf and excluding NULL. We’ve now got 1 row per State/Province with the appropriate Sub-Category
Let’s shift the data around – remove the fields from the Text shelf, and swap the order of the fields in the Rows so Sub Cat with Max Sales for State is listed first.
You can now see that for each Sub-Category we have the list of State/Provinces which we can just count using a new field.
Count States
COUNTD([State/Province])
Add this to Text and remove State/Province from Rows and sort descending and we have the data we need to build the bar chart.
Building the bar chart
Add Sub Cat with Max Sales for State to Rows
Add Count States to Columns and Label
Add Sub Cat with Max Sales for State to Filter and exclude Null
Sort Descending
Adjust colour of bars
Hide axis, Remove all gridlines, zero line etc
Adjust format of heading labels
Hide field labels for Rows to remove the Sub-Category label
Update the title
Adjust width of each row and size of bars as desired
Building the Map
To create the map, we need to be able to identify which Sub-Category we’re hovering on in the bar. For this we’re going to capture the value into a parameter
pSelecteSub-Cat
string parameter defaulted to ” / empty string
and the we need to identify the State/Provinces where the selected Sub-Category has the maximum sales
Selected States
[Sub Cat with Max Sales for State] = [pSelectedSub-Cat]
this returns a boolean true/false value based on whether the fields match or not.
On a new sheet, double click on State/Province to which should automatically generate a map. Make sure the location(Map menu > Edit Locations) is set to reference the Country/Region field, so both the US and Canadian provinces are all picked up
Change the mark type to be a filled map. Update the washout of the background layer to be 100% (Map menu > background layers) and then set the border property on the Colour shelf to be white.
Add Selected States to the Colour shelf and exclude the Nulls (right click Null in the colour legend and exclude). Add the pSelectedSub-Cat to the sheet and manually enter a value, eg Phones. Adjust the colours associated to the True & False values.
Adding the Viz in Tooltip
Back onto the bar chart sheet and edit the Tooltip. Include some introductory text that references the Sub-Category and the number of States (or leave with the default values if you wish). Then add the map sheet via Insert > Sheets > <select relevant sheet>
Edit this text adjusting the height and width as appropriate and removing the <All values>text (we don’t want to filter the map to just show the states with the associated Sub-Category, we need all states to be visible, we’re just colouring based on the parameter).
My tooltip dialog looks like
Setting the parameter
Add the bar chart to a dashboard then add a dashboard parameter action (Dashboard menu > Actions > Add Action >Change Parameter)
Set Sub Cat
on hover of the bar chart on the dashboard, update the pSelectedSub-Cat parameter with the value from the Sub-Cat with Max Sales for State field.
Now if you hover over a bar, you should get a map with the associated States highlighted.
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,
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.
For this week’s #WOW2020 challenge, Sean Miller stripped things right back and went ‘back to basics’.
This blog should be brief as I’m only going to touch on the bits that I think some people might find a little tricky.
The Map Colours
Use the Red-Black Diverging colour palette, centred at 0 to ensure the colours match exactly (this is most noticeable on the Viz in Tooltip table if it’s not centred at 0).
Map Background
On the Map -> May Layers menu, ensure all the items under the Map Layers section are unchecked
Seaboard States
I used the MIN(1) on the Columns shelf and fixed the axis from 0-1 to fill it up.
Top 10 Products
Orders Count
I dragged Order ID into the ‘measures’ section (below the line on the left hand pane if you’re using later versions of Tableau), and chose the COUNTD aggregation. When I added this to the table, I then changed the alias of the field and called it ‘Orders’
Top 10
Add Product Name to the Filter shelf and select the Top tab.
Colouring the columns
This uses the Legend Per Measure functionality. Add Measure Values to the Colour shelf and select the Use Separate Legends option
This will add 3 colour legends onto the canvas. Set the colours of the Profit measure to the Red – Black diverging as with the map.
For the other 2 legends select any diverging colour palette, then click on the coloured square at each end, and select white from the palette displayed. Change the stepped colour to 2, and you’ll find that the measures now don’t look like they actually have a background colour.
Viz in Tooltip
When adding the sheet as a tooltip, I adjusted the size to 500×350
The size of the Top 10 Products sheet should be set to Entire View to ensure you don’t get a ‘View is too large to display’ message on the tooltip
Getting the Top 10 filtered properly
Once the viz has been added as a ‘viz in tooltip’ a State related filter pill will automatically be added to the Filter shelf of the the Top 10 Products sheet. To ensure the top 10 products gets filtered by the state BEFORE the top 10 products by sales are identified, the filter needs to be Added to Context
Arranging on the Dashboard
I managed to tile all the items, except for the ‘Eastern Seaboard States’ title which I floated.
It’s getting near #data19, Tableau’s annual customer conference, which once again is being held in Las Vegas. So for #WorkoutWednesday this week, Lorna decided to incorporate this into her challenge, which was also intended to showcase some of the new features introduced into v2019.3 (so you’ll need this version at least to complete the challenge).
For the challenge, Lorna provided a dataset in a .hyper format that simply consisted of the (casino) name, address and it’s associated latitude and longitude values.
The core requirement was to show all the casinos within a user defied radius from another casino, which was selected by user by clicking on the map.
So we need to
identify the selected casino
determine the lat and long of the selected casino (the start)
determine the distance (in miles) between the selected casino and all the other casinos
identify the radius
restrict the display to just those within the specified radius
Identify the selected casino
This is simply a parameter Selected Casino which is initially hardcoded to have the default of “Mandalay Bay Resort & Casino” (it’s important you get this spelt right using the exact case too).
I then have a boolean calculated field
Is Selected Casino?
name = [Selected Casino]
which is true if the name of the casino in the dataset matches the value stored in the parameter
Lat & Long of selected casino
My aim is to get the latitude and longitude of the selected casino to be stored against each row in the dataset, so I need to use an LoD to do this
The first MAKEPOINT function above determines the start point using the information about the selected casino, and the second MAKEPOINT determines the end point, using the casino location information stored on each row.
Identify the radius
The viz requires the data to be restricted just to those within a defined radius, so we need another parameter, How many miles? to capture this value.
In Lorna’s viz, the parameter has a fixed range, which is specified as part of the parameter properties
In order to then restrict the casinos shown to just those within range, we need
Within specified miles?
[Distance (miles)]<= [How many Miles?]
The final thing we also need at this point, is a Distance (metres) field as this is used to colour the marks on the viz, and is also referenced on the tooltip.
Building the map
So now we’re at the point we can start building the map.
My initial observations upon hovering over Lorna’s published solution, was this would need to be a dual axis map. The text on the tooltip differed dependent on whether the mark was the selected one or not. The colour of the marks also differed; for the selected mark, the colour is black, while for the non selected marks the colour varied based on the distance.
So with this in mind, I decided to build another calculated field
IsSelected Lat
IF [Is Selected Casino?] THEN [latitude] END
which would just store a latitude value against the row of the selected casino only, as demonstrated below (I set it to have a geographic role of latitude)
I figured I’d then plot this as a dual axis on the map, but as soon as I added, second latitude pill, my display changed from having a map background, to a standard scatter plot chart 😦
So I started to go down the route of just making it a single axis chart, but the tooltips started to get too complicated, and the colouring just wasn’t working….
So I reverted back to the original plan, but this time, used the same latitude pill for my dual axis, which retained the map background
I now needed to differentiate on each map between the selected casino, and the others, and did this initially by Size
Size (selected)
IF [Is Selected Casino?] THEN 2 ELSE 1 END
Size (non selected)
IF [Is Selected Casino?] THEN 2 ELSE 1 END
These are independent fields which can be adjusted differently, and one is placed on the Size shelf of each marks card and adjusted so on one chart the selected mark is the biggest, and all the other marks are as small as they can be. On the other chart, the selected mark is made as small as possible.
I then started adding the various other features
added Distance (metres) to the colour shelf on the non- selected marks card, changing the colour palette to purple, and reversing the range so the lower the distance, the darker the colour
changed the mark type to Shape on the selected marks card and added Is Selected Casino? to the shape shelf, changing the shapes accordingly
The location marker image I found on flaticon.com (remember to add an attribution on your viz if need be to the icon creator)
See this blog for how to get the shape into Tableau
Set the tooltip appropriately for each marks card
Filtered the view to restrict to the casinos in range
The map background needs to be changed to street view, which is set via the Map -> Map Layers setting in the menu
We can now make the map properly dual axis, and can put on a dashboard.
Changing the selected casino
Now it’s on the dashboard, we can use a parameter action to change the value of the selected casino ‘on click’
Displaying count of casinos within radius
The dashboard shows a summary on selection, which I added as the title of the map sheet
I created a calculated field
Count Casinos
TOTAL(SUM([Number of Records])) – 1
which needs to exclude the selected casino, hence the subtraction. I added this to the Detail shelf, so I could reference it in the sheet title.
Ensuring all the casinos remain ‘visible’ on selection
By default, on selection of a different casino, all the other casinos within range are faded out, which we don’t want to happen
So I used the same ‘dummy’ trick with highlight action that I documented in a previous blog to prevent this from happening
The dashboard just needed a few more tweaks – adding the title (which references the parameters), adding the colour legend and the distance parameter, and I was done. I published my viz.
Except I wasn’t completely happy…
When I increased the number of miles, the location shape became ‘hidden’ beneath the other marks, but if I changed the order of the axis, tiny marks were visible on each casino, which I couldn’t get the colour to match
This stumped me, so I had to check out Lorna’s solution, but I stared for some time and just couldn’t figure out what she’d done. It was Rosario Gauna’s solution where I immediately saw what I had to do, as Rosario had made a comment in the worksheet caption as a reminder.
Frustratingly, this is exactly what I do to remind me of this sneaky trick… the small marks on the ‘selected casino’ mark shelf, were ‘hidden’ by simply right clicking on the Is Selected Casino? shape legend and ‘hiding’ the false values
Doh! I wish there was a way you could see more easily on the sheet in Desktop where someone has done this.
Donna + DataViz 