Tag: Week 10

Can you rank by multiple measures?

Donna ColesLeave a comment

This week’s challenge once again has 3 levels, so I will blog the first solution and then build on/adapt from there. The main requirement is focused on working out how to rank the football teams based on multiple measures, and this is relevant for all the levels. Once again, we’re looking at data related to football teams in the English Premier league, and where they finished each season.

Defining the calculation to rank by

A team’s finishing position is based on a combination of points scored, goal difference and goals scored. But on rare occasions, a team is deducted points in the season, so we need to account for that.

Adjusted Points

SUM([Points]) – ZN(SUM([Points Deducted]))

Putting the key measures (Adjusted Points, Goal Difference and Goals For) per Team into a tabular view for just a single Season (eg 1993), we can see multiple instances of teams ending on the same number of Adjusted Points

Just ranking on this field, won’t necessarily order the teams in the correct order as Goal Difference and Goals For also need to be taken into consideration. We need to create a field we can rank based on a combination of all three fields, and to do this we’ll ‘weight’ each field by multiplying each field by a suitable proportion based on precedence. In the example shared via the community forum post the leading indicator was multiplied by 1000. In my case I’m going to divide.

Adj Points | GD | GF

[Adjusted Points] + (SUM([Goal Difference])/100) + (SUM([Goals For])/10000)

Add this field into the table, and sort by it descending. You can see that for the 3 teams with 52 points, two of those teams have the same Goal Difference too, but are being correctly ordered based on the Goals For due to the result of the Adj Points | GD | GF calculation

Now we’ve got the core calculation nailed, we can start to build the viz.

Building the Beginner viz

On a new sheet, add Team to Rows and Season End Year to Columns.

We want to be more specific about the season, so create

Label Season

STR([Season End Year]-1) + ‘-‘ + RIGHT(STR([Season End Year]),2)

and add to Columns too.

To make the ‘squares’ rather than use the square mark type which can sometimes be a bit fiddly to get the size just right, I’m going to use a fake axis and a bar mark type.

Double click into Columns and type MIN(1.0) and change the Mark type to Bar. Adjust the Size to be as big as possible. Edit the axis to be fixed from 0-1.

Hide the Season End Year (right click the pill and uncheck Show Header). Rotate the Label Season (right click on the actual label and Rotate Label). Then make each column narrower and widen each row until you’re happy with the sizing.

We want to the Teams to remain listed in alphabetical order, but we want to know the position the team finished in the season, so we create

Rank Desc

RANK([Adj Points | GD | GF], ‘desc’)

Initially add this to Label, and then adjust the table calculation so it is explicitly computing by Team only.

You can verify the number associated to the teams in the 1993 season with the sorted table you made earlier.

But we’re not going to show the rank for every square, so move the Rank Desc from Label to Tooltip.

We do want to label just the team in first though so create

Is First

IF [Rank Desc] = 1 THEN ‘1st’ END

and add this to the Label shelf instead. Again adjust the table calculation as described above.

For the Tooltip we need to create

Tooltip Rank Suffix

IF [Rank Desc] = 1 OR [Rank Desc] = 21 THEN ‘st’
ELSEIF [Rank Desc] = 2 OR [Rank Desc] = 22 THEN ‘nd’
ELSEIF [Rank Desc] = 3 OR [Rank Desc] = 23 THEN ‘rd’
ELSE ‘th’
END

add this to the Tooltip shelf, adjust the table calculation as required. Also create

Tooltip – Note

IF ZN([Points Deducted])<>0 THEN ‘Additionally the team was deducted ‘ + STR([Points Deducted]) + ‘ points due to ‘ + [Notes] END

And then add Adjusted Points and Tooltip – Note to the Tooltip too. Adjust the Tooltip as required.

Finally adjust the look and feel by

  • Set the background colour of the worksheet to pale lilac (#f1eff6)
  • Set the mark colour to purple (#938bc1) and add a pale grey border
  • Set row and column dividers to a thin solid pale grey line (#e6e6e6)
  • Adjust the font colour of the row and column labels to black
  • Hide the Team row heading label (right click > hide field labels for rows).
  • Hide the Label Season column heading label ((right click > hide field labels for columns).
  • Hide the MIN(1.0) axis (right click pill – uncheck show header)
  • Make the mark label bold and centred
  • Set the sheet to fit width
  • Add a title
  • Name the sheet Beginner or similar

Building the Intermediate Viz

The requirement here, is we now need to highlight the teams that won and those that were relegated each season.

For this, we’re going to create another ranking field, but this time ranking in the other direction

Rand Asc

RANK([Adj Points | GD | GF], ‘asc’)

and then determine the team’s position in the season, giving consideration to the fact that in the 1994-95 season, 4 teams were relegated instead of the usual 3.

Position

IF [Rank Desc] = 1 THEN ‘C’
ELSEIF (MIN([Season End Year]) =1995 AND [Rank Asc] <=4) OR ([Rank Asc] <=3) THEN ‘R’
END

Duplicate the Beginner viz. Remove Is First from Label and add Position instead. Add Position to Colour and adjust accordingly.

Building the Advanced Viz

Now we want to extend the colouring, so the teams who were not the champions and who weren’t relegated, are coloured based on their league position.

For this duplicate the Intermediate sheet. Add Rank Desc to the Detail shelf, verifying the table calc is computing by Team only. Change it to be a discrete (blue) pill, and the change the pill so it is also on the Colour shelf, by updating the ‘detail’ icon to the left of the pill.

This will result in 2 pills on the Colour shelf and the Colour Legend listing all the permutations.

Adjust the colour legend, so all the R’s are red, the C is yellow, and the Nulls are set from automatically assigning the BuPu 3 Color Seq Palette. Use ctrl (windows) to select multiple entries in the colour legend, rather than assigning one by one.

Note if you don’t already have the BuPu 3 Color Seq Palette in your preferences file, you will need to add and then reload Tableau. For further information see here.

And that should complete all levels of the challenge. My published viz is here.

Happy vizzin’!

Donna

Let’s practice using the Device Designer

Donna Coles1 Comment

Inspired by a discussion on Twitter that resulted in this blog post from Kevin Flerlage, Sean challenged us this week to build a viz that should adapt for different devices.

This makes use of the dashboard layout feature in Tableau (see here for further information). The focus on this week is really on the layouts, the charts are relatively straightforward, so I’m just going to summarise each one fairly quickly.

Note on the data

In the requirements, Sean talked about filtering the date to 2023 and assuming a YTD value where ‘today’ was 8th March 2023. The data set linked in the requirements didn’t contain that information. I chose instead just to use the 2022.4 version of Superstore I had. As this challenge wasn’t going to contain any complicated table calcs/LODs I wasn’t worried that my numbers might not match.

Filtering the data

To restrict the information based on Sean’s requirements, I created a parameter to represent ‘today’

pToday

date parameter defaulted to 8 March 2022

I then created a field to use to ensure I only counted data up to that date.

Dates to Include

[Order Date] <= [pToday]

I added this to the Filter shelf and set to True. In addition I added Order Date to the Filter shelf, and selected Years > 2022. Both these fields I set to apply to all worksheets using this datasource.

Building the KPIs

Format Sales and Profit to be $ with 0 dp. If it doesn’t exist, create

Profit Ratio

SUM([Profit])/SUM([Sales])

and format this to % to 1 dp.

Add Measure Names to Filter and filter to the three measures (Sales, Profit and Profit Ratio). Add Measure Names to Columns and Measure Values to Text. Re-order as required. Add Measure Names to Text too and format the Text as required, Remove row dividers and hide the column headers.

Building the bar chart

Add Sub-Category to Rows and Profit Ratio to Columns and sort descending, Add Sales to Colour and add Profit to Tooltip. Adjust tooltip. Hide the Sub-Category column heading from displaying (right click > Hide field labels for Rows). Change the title of the viz.

Building the Map

Double click State/Province to automatically generate a map of the USA (if it doesn’t display, check the location is set to USA – Map menu > Edit Locations).

Add Profit Ratio to Colour and add Sales and Profit to Tooltip. Adjust tooltip.

Remove all the background map layers by selecting Background Layers from the Map menu, and unchecking all the options listed on the menu that displays on the left hand side.

Hide the ‘unknown’ indicator if its displaying (right click > hide indicator).

Stop the map options (that allow zoom & pan etc) from displaying by selecting Map Options from the Map menu and unchecking all the options that are presented.

Remove row and column dividers and change the title of the viz.

Building the Scatter Plot

Add Sales to Columns and Profit to Rows. Add Order ID to Detail and Profit Ratio to Colour. Make the zero lines slightly more prominent and title the viz.

On a new sheet, add Customer Name and Product Name to Rows. Add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and restrict to Quantity, Sales and Profit. Reorder the measures as required.

Add subtotals -from the Analysis menu select Totals > Add all Subtotals.

Set the row banding to none. Centre align the Customer Name column. Name the sheet Order Details or similar

Back on the scatter plot sheet, adjust the Tooltip and add a reference to the Order Details sheet by using the Insert > Sheets > select sheet

How well the viz in tooltip displays can often be a bit of trial and error. You may need to adjust the width and height properties on the referenced sheet in the tooltip. Also, sometimes setting the Fit property of the Order Details sheet may help too.

Building the default dashboard

Create a dashboard sized Generic Desktop. Set the background of the dashboard to grey (Format menu > Dashboard) Arrange the four vizzes on the dashboard. You may need to use horizontal and vertical containers to help you align everything where you want. Add padding around all the viz objects (I set mine to 10). Hide the title of the KPI viz. For the other 3 vizzes, set the background to white, so the title is also in white.

Set filter actions on both the bar chart and map, by selecting the object and then choosing Use as Filter from the context menu.

Click on the bar and check the other vizzes all filter. Do the same with the map. Now we’re ready to apply the different device layouts.

Creating the Tablet layout

The dashboard initially created is the Default dashboard, and we can tell this by looking at the left hand pane.

To add a new layout for a generic tablet, click the Device Preview button.

A Device Preview bar will appear at the top of the dashboard, Scroll through the Device type options until Tablet is displayed. By default the model should already be Generic Tablet. A border will display over the dashboard which indicates the boundaries of the dashboard based on the dimensions of the device. Click the Add Tablet Layout button.

The dashboard will immediately be resized to fit the boundary. Some of the vizzes are squashed up. Manually readjust so everything is displayed as required. A Tablet option will have appeared on the left hand side, and you can now toggle between Default and Tablet (click on the words in the left hand pane) and see the dashboard adjusting.

Creating the phone layout

Click on the Phone option (top left under Tablet), or scroll through and select Device Type = Phone.

By default, everything on the default dashboard is displayed on the Phone layout, which is obviously a portrait layout, optimised for scrolling down.

We don’t want the title, or the Scatter plot on this layout. To remove them, click the locked padlock icon and it will change to an unlocked icon, and the dashboard will be editable..

Remove the title object and the scatter plot object. In my case I also removed my footer information. Adjust the bar chart so its a bit longer, so the bars and labels are readable. It doesn’t matter that it will extend outside the bounds.

And that’s it – you now have 3 layouts for a single dashboard. Publish the viz to Tableau Public, then test out by accessing the viz from a tablet and/or your phone. Tableau will detect the type of device you’re accessing from, and show the most suitable display. Below are images of my viz accessed from my laptop, and on my mobile.

My published viz is accessible here.

Happy vizzin’!

Donna

Sales Budget “Burndown” Chart

Donna ColesLeave a comment

Erica Hughes had a table-calc-tastic challenge for us this week! I was using Tableau before LoDs were invented, so became very familiar with the ‘dark art’ of table calculations, and subconsciously often turn to these first when solving problems. Since the advent of LoDs, this functionality can get forgotten about, so this is a great challenge to help flex those table calc muscles and become a good reference point.

With any table calc challenge, I work out what I need in a tabular form before even attempting any visual, so that’s where we’ll start today.

I also tend to ‘build up’ the calculated fields I need, as this helps me validate the data I’m working with. It means I end up with more calculated fields than absolutely necessary, but it’s a good practice to get in as it eases troubleshooting in the long run.

In this example, we need to treat Sales as if it’s a budget that is then being ‘spent’ over the course of the following months. The first thing we need to define is the total budget

Total Sales

TOTAL(SUM([Sales]))

We then need to understand the cumulative (running sum) of Sales per month.

Running Sum Sales

RUNNING_SUM(SUM([Sales]))

Note – this calculation can be achieved by applying a Quick Table Calculation to the Sales pill once added to a view, but I will need to reference the values in other calculated fields, so created the field directly.

Let’s start building out our table of data to see what’s going on with these calculations.

Add Order Date to Rows and set so its displaying the date in the Month Year format

Then add Total Sales, Sales and Running Sum Sales

The Total Sales column will be the same for every row and match the same value in the last row of the Running Sum Sales column. The value in each Running Sum Sales row is the sum of the Sales value in the same and all preceding rows.

By default the table calculations are working ‘down’ the table which gives the desired result, but I tend to ‘fix’ the field the calculation is computing over, as when we build the viz we won’t be going ‘down’, but ‘across’, so fixing helps ensure we get all our settings just right.

So edit the table calculation of both the Total Sales and the Running Sum Sales fields to compute using Month of Order Date.

With these fields, we can calculate the ‘sales budget’ value being displayed as

Total Less Running Sum Sales

[Total Sales]- [Running Sum Sales]

Pop this on the view and verify the table calculation settings are set as above (this field contains nested calcs, so check each setting). You should be able to verify the value of this column is the result of the 1st column – 3rd column values

But there’s a twist.

The requirements state that “for dates in the future, the sales budget should remain constant”.

For this we need to work out what month ‘today’ is in. For this task, I have hard coded ‘today’ into a parameter called Today and set to 8th March 2022. If this was a ‘real’ production business dashboard, I’d just refer to the function TODAY() directly.

I can then work out

Current Month

DATETRUNC(‘month’, [Today])

which will return 01 March 2022 in this instance.

I then want to identify the record that matches the current month

Is Current Month?

DATETRUNC(‘month’, [Order Date]) = DATE([Current Month])

which just returns a boolean True/False.

And with this, I can then determine the value from the Total Less Running Sum Sales column that is associated to March 2022, and ‘spread’ that value across every row in the view.

Curr Month: Total Less Running Sum Sales

WINDOW_MAX(IF ATTR([Is Current Month]) THEN [Total Less Running Sum Sales] END)

If the month in the row is the current month, get the required value and ‘spread’ over every other row using WINDOW_MAX. Add this to the view, checking your table calc settings again.

Now we can work out the values needed for the Sales Budget line

Sales Budget

IF MIN(DATETRUNC(‘month’, [Order Date])) > MIN(DATE([Current Month])) THEN [Curr Month: Total Sales Less Running Sum Sales] ELSE [Total Less Running Sum Sales] END

Format this to $, Millions (M), 1dp.

If the month is later than the current month, use the value associated to the current month, otherwise use the Total Less Running Sum Sales value. Note here, the date functions are wrapped within a MIN function as the other fields are table calculations which means they’ve been aggregated, so all other fields referenced need to be aggregated to. The function MAX will have worked just as well.

Phew! we’ve finally got the data we need for the first line :-). As mentioned earlier, this can be achieved by combining some of the logic in the calcs, but I like to be methodical and verify my numbers give me what I expect at each stage.

In order to display the Estimated Budget line, we need to first work out how much of the total sales would be spent each month, if the same amount was spent each month – ie Total Sales divided by number of months.

Size (Count of Months)

SIZE()

I just chose to use the SIZE() table calculation to essentially count the number of rows in my view.

Estimated Budget Constant

//average the sales over the total months to get a constant budget
[Total Sales] / [Size (Count of Months)]

Add these into the view and adjust the table calc settings as before

essentially Total Sales (2,297,201) / Count of Months (48) = Estimated Budget Constant (47,858).

Like before, we need to compute running sum of this estimated budget

Running Sum Est Budget

RUNNING_SUM([Estimated Budget Constant])

and then we can calculate the Estimated Budget

Total Less Running Sum Est Budget

[Total Sales] – [Running Sum Est Budget]

This now gives us the data to plot the 2nd line.

The final calculation we need for the tooltip is the difference between the sales budget and estimated budget

Difference to Estimated

[Sales Budget]- [Total Less Estimated Running Sum]

format this using a custom format of +”$”#,##0,K;-“$”#,##0,K

Now we can build the viz! I tend to keep sheets like above in any workbook as a ‘check sheet’ if I need to do any troubleshooting later on.

So on a new sheet, add Order Date to Rows and set to be a continuous (green) month/year format this time. Add Sales Budget to Columns. Adjust the table calculation so all nested calculations are computing by Order Date.

Add Total Less Estimated Running Sum to Columns (set the table calc settings), then change to dual axis and synchronise axis.

Remove Measure Names from the All Marks card to remove the colours that have been set. Change the Colour of the Sales Budget line to purple, and set the markers to have circles.

From the Analytics tab, drag a Trend Line to the canvas and drop it as a linear trend on the Total Less Estimated Running Sum measure

This will add the ‘dotted’ line.

On the Total Less Estimated Running Sum marks card, set the Opacity of the Colour to 0%, so only the grey dotted trend line is visible.

Edit the trendline and uncheck Show recalculated line for highlighted or selected data points

Add Current Month to the Detail shelf of the All Marks card, and set to the month/year format. Then right click on the Order Date axis and Add Reference Line, setting the values as below

Right click on the reference line, and Format; adjust the alignment and font size & colour, so ‘Future’ is listed at the top.

Add Difference to Estimated onto the Tooltip of the Sales Budget card (adjust those table calc settings). The format the tooltip accordingly.

Add Curr Month: Total Sales Less Running Sum Sales to the Detail shelf of the All Marks Card (adjust those table calc settings). Edit the title of the viz

Finally tidy up the display by removing axis, row and column banding etc, and adjust the Sales Budget axis so it displays every 500,000.

And that should be it… my published viz is here.

Happy vizzin’!

Donna

Can you build a “must include” filter?

Donna ColesLeave a comment

Ann Jackson set this week’s challenge inspired by a business requirement she’d encountered, which was to be able to filter the data based on a user selection, and then additionally apply another filter to narrow down the required data.

The task ‘splash’ page had already indicated the challenge would involve sets, and the requirements additionally stated you’d need version 2020.2 or higher, which added an additional hint that set controls would be involved. Examining Ann’s solution, I could also see that the tell-tale set icon displayed when hovering near the input control, which confirmed the ‘filters’ were indeed using the set control functionality.

  • Building the bar chart
  • Viz in Tooltip
  • BANs

Building the bar chart

Let’s start by focusing on the bar chart. The first thing to do is to rename several fields. Ann likes capital letters, so the following fields just need to be renamed so they’re all in uppercase: Sales, Quantity, Product, Order ID and Customer (Name). Sales can also be formatted to $ with 0dp.

We create the first set we need by simply right-clicking on the PRODUCT field -> Create -> Set. I named this 1st Products and set to the Use All condition.

Lets build out a basic table and then we can play with how we need to use this set. Put

  • ORDER ID on Rows
  • CUSTOMER on Rows
  • Add Sales to Text
  • Drag Quantity over the Sales column, so Measure Names gets added to Columns and Measure Values is on Text
  • Sort by Sales descending

If we now add the 1st Products set to the Filter shelf and choose the Show Set option, the list of products in the set will be displayed for selection.

Choose the 1st item in the list, and the data will be filtered to just those customers who have ordered that product

But, while this list matches the names if you do the same on the solution, the Sales and Quantity values differ. This is because, we have actually filtered the data just to the lines containing the selected product. If you bring PRODUCT onto Rows, you’ll see you have 1 line per customer.

Now, while you can use LOD calculations to compute the total sales/quantity at an order id level, to resolve this, it won’t solve the next step of the puzzle, to filter the data further by other products on the same orders, as we’ve already filtered out all the other data. So we need to do something else.

To demonstrate, we just want to keep a handle on these specific ORDER IDs so ctrl-click to select them all, and then Include to add them to the Filter shelf. Now remove the 1st Products set from the Filter shelf, then re-add back to the Rows.

You’ll see we now have all the product lines on each order, with an In or Out displayed against each row. The rows which match the PRODUCT selected in the set, is marked as In. This is essentially what the filter is doing if 1st Products is on the Filter shelf – it is filtering the rows to those which return as In (the set).

We basically want a way to be able to identify all the rows on an order which have at least 1 In row, so we can filter on that instead. For this we can create a new field,

Order has 1st Products

{FIXED [ORDER ID]: MAX([1st Products])}

What this is doing is saying for each ORDER ID, get the maximum 1st Products value, which is then ‘stored’ against each row for the same ORDER ID. This may look odd, as 1st Products from the display is showing an ‘In’ or an ‘Out’, so how does the MAX work? Well, whilst the display is In or Out, under the bonnet this is actually a boolean field of 1 = True = In or 0 = False = Out, so the MAX is actually returning either a 1 (true) or a 0 (false).

If you add Order has 1st Products to Rows, you can now see every row in this restricted data set is listed as True

Remove the ORDER ID from the Filter shelf, and you should observe all the other orders listed have False listed against every row.

So we can now add Order Has 1st Product = True to the Filter shelf, and we can remove 1st Products from Rows too.

So we’ve mastered the 1st product filter, time for the 2nd. Once again right-click on PRODUCT and create another set with the Use All condition. This time I called the set 2nd Product.

Just as before, we want to identify all orders that contain this 2nd product selected, so we’ll create another calculated field

Order has 2nd Product

{FIXED [ORDER ID]: MAX([2nd Product])}

Add this field to the Filter shelf and select True. Now click on the 2nd Product set in the Dimesions pane, and selected Show Set, to display the list of products

You’ll notice though that all products are listed, and not just the ones that are associated to the set of orders already filtered. To fix this, we need to add the Order Has 1st Products field to context (click on the Order Has 1st Products pill on the Filter shelf and Add to Context). This will change the pill to a grey colour, and you can now restrict the values in the 2nd Products set to only show All Values in Context.

The add to context function allows us to dictate the order in which the filtering is applied, so the data is primarily filtered based on our 1st Products set and then filtered based on the 2nd Product.

You should now be able to play around with the filtering and see how the rows for complete orders are retained.

Removing PRODUCT from the Rows, and we get back to the total order value / quantity values we’re expecting.

From this, you should now be able to build out the viz. Firstly, duplicate the sheet so we retain the original table, then move SALES & QUANTITY to the Columns, add Measure Names to Colour, add Labels to match mark colour, and format accordingly.

Viz in Tooltip

As an ‘added extra’ Ann added the ability to hover on a bar and see the details of the order in a simple table. We can use the original table we built above to demonstrate the functionality. Add PRODUCT to Row, and replace the QUANTITY field, with a new field you need to create called QTY, which is just based on QUANTITY ie

QTY

[QUANTITY]

Then hide the ORDER ID & CUSTOMER fields (uncheck Show Header) on the pill. You could remove completely, but I like to retain just in case we need to debug if things don’t work.

We will need to show some totals, but this isn’t the Grand Total. Grand Totals will work, but the background of the row will always be a fixed colour. Either white, by default, or set to grey if you choose. However I noticed that depending on the order id I hovered on, the Grand Total row may display grey or white depending on the existing banding. For this we need to use subtotals instead (Analysis menu -> Totals -> Add All Subtotals).

Because we have multiple (hidden) dimensions on the rows, multiple Total rows will display. We can remove by unchecking the Subtotals option against the ORDER ID pill.

And finally, we need to relabel the row. Right-click on the Total row and select Format. Change the label of the Totals section from ‘Total’ to ‘Grand Total’.

With additional formatting to change the font size etc, this sheet can now be added as a ‘Viz in Tooltip’ to the bar chart. On the Tooltip shelf associated to the bar chart, you can Insert -> Sheets -> <Select Sheet>

Hovering over a bar should now automatically filter the tabular display to the appropriate CUSTOMER & ORDER ID.

BANs

For the BANs, we need several additional calculated fields

# ORDERS

COUNTD([ORDER ID])

simply counts the number of distinct Order IDs in the filtered view.

Total Orders

{FIXED:COUNTD([ORDER ID])}

an LOD calculation that counts the overall number of distinct Order IDs, regardless of any filters applied.

% OF TOTAL ORDERS

[# ORDERS]/SUM([Total Orders])

formatted as a % to 1 dp.

AVG ORDER AMOUNT

SUM([SALES]) / [# ORDERS]

formatted to $ 0 dp.

AVG ORDER QUANTITY

SUM([QUANTITY])/[# ORDERS]

formatted to a number with 0 dp.

Add Measure Names to the Filter shelf, and filter to the relevant 4 measures. Add Measure Values and Measure Names to the Text shelf, and Measure Names on Columns. Add the Order Has 1st Products = true and Order has 2nd Product = true to the Filter shelf, but this time don’t add anything to context. If you do, it will affect the Total Orders measure, and the % of orders calc will be wrong. Hide the Measure Names on the columns (uncheck show header), and format the text appropriately.

To get the thick lines, format and set the Row Divider on the sheet to be a thick coloured line.

And you should now have all the core building blocks needed to add onto a dashboard.

My published viz is here.

Happy vizzin’! Stay safe!

Donna

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

Donna Coles1 Comment

Week 10 of #WOW2020 was set by guest challenger Sean Miller, who chose to demonstrate a ‘hot off the press’ feature released in v2020.1 (so having this version is a prerequisite to completing this challenge).

I was excited to see this as I don’t use maps often in my day job, and I love being able to have the opportunity to try the new stuff.

Sean provided references to two blog posts, which are a must read as they will definitely help guide you through the challenge, and explain in more detail what’s going on ‘under the bonnet’. I’m not therefore going to repeat any of this.

Sean provided 2 versions for the challenge with supporting datasets.

Intermediate challenge – Can you isolate pubs within 500m of a hotel?

For this we are provided with a set of hotels in London and a set of pubs. The requirement is to only include on the display the pubs which are within a 500m radius (ie buffer) of each hotel.

Join the data

The provided data consisted of a sheet of Pubs with a Lat & Lon field, and a sheet of Hotels with a LAT & LON field

These 2 data sets need to be Inner Joined together as

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

INTERSECTS

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

In the join clause window, you have the option to Edit Join Calculation which lets you type the calculation you need

Mapping the Hotels

Whilst the join has been made, we will need the ‘buffer’ calculation to display on the viz, so create

Buffer Hotel

BUFFER(MAKEPOINT([LAT],[LON]),500,”m”)

Then double click the Latitude (generated) and Longitude (generated) fields which will automatically display a map on screen.

Add Buffer Hotel to the Detail shelf and you’ll get the following (and the mark type will change to Map)

The circles look to be representing each hotel, but if you hover over one circle, all get selected. Add Hotel Name to Detail to allow individual selection.

Add Number of Records to the Label shelf, and format to suit.

Change the Colour of the mark to be pale orange and adjust the Opacity to suit.

Set the map background by choosing Map -> Map Layers from the menu and selecting Streets from the background style section

Mapping the Pubs

As with the hotel, we’re going to need the Pub Location spatial point to display on the viz, so create

Pub Location

MAKEPOINT([Lat],[Lon])

Duplicate/drag another instance of Latitude (generated) onto the Rows shelf.

On the second marks card, remove all the fields, and change the mark type to circle, then add Pub Location onto the Detail shelf, along with Pub Name.

You might be struggling to see the marks, but they are there – change the colour to grey, add a white border and adjust the size… found them?

The Tooltip on the pub marks, displays the distance from the hotel to the pub, so create

Distance

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

which is the distance in metres from the Pub Location to the Hotel Location (I could have used my Pub Location field and created a Hotel Location field to put into this calculated field.

Add Distance to the Tooltip field for the pub marks, and adjust to match.

Now make dual axis

Hotel List – Viz in Tooltip

On hover over the hotel buffer circle, a full list of the pubs in range is displayed. This a managed using another sheet and the Viz in Tooltip functionality.

Create a basic table with Hotel Name, Pub Name on Rows and Distance on Text. Type in the word ‘Distance’ into the Columns to make a ‘fake’ column label.

Hide Hotel Name from displaying by unchecking Show Header on the field, then Hide Field Labels for Rows and Hide Field Labels for Columns. Format to remove the column divider

Name the sheet Pubs or similar

On the Tooltip of the hotels buffer marks, adjust the initial text required, then insert the sheet by Insert -> Sheets -> <select sheet>

This will insert text as below

At the point it says ‘<All Fields>’, delete the text, then Insert -> Hotel Name

Now, if you hover over the buffer circle on the map, the list of pubs associated to just that hotel should display.

Note – when adding the sheets into the viz in tooltip, or changing the fields to filter by, always use the insert & select options rather than just typing in, as I find it doesn’t always work otherwise….may be just me though….

Phew! That’s the intermediate challenge completed (well once you’ve tidied and added to a dashboard of course.

onto the next….

Jedi Challenge – Can you find the pubs closest to a chosen hotel?

Sean provided a separate pre-combined dataset for this, as the display needs to show all the pubs, regardless of which hotel is selected, whereas in the intermediate challenge, the spatial join meant all the pubs outside of the buffer zones were excluded.

The map itself follows very similar principles. We need a dual axis, where one axis is plotting a selected hotel with it’s buffer, and the other axis, the pub locations.

The selected hotel is ultimately going to be derived from a parameter action, but we’ll set that later. For now, let’s just create the string parameter, Selected Hotel, to store the name of the hotel, which is just set to a ‘default’ value of “The Hoxton – Shoreditch”

Additionally, the buffer radius can be changed in this challenge, so we have another parameter, Buffer Radius, this time an integer with a max value of 500, and defaulted to 500 as well.

To draw the selected hotel with buffer on the map, we first need to isolate the selected hotel’s latitude & longitude, to determine the location, and store it against every row in the dataset via a LoD calculation

Is Selected Hotel?

[Name]=[Selected Hotel]

Selected Hotel Lat

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

Selected Hotel Long

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

Selected Hotel Location

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

Now we know the location, we can create the buffer around it

Hotel Buffer

BUFFER([Selected Hotel Location],[Buffer Radius],’m’)

The Hotel Buffer and the Selected Hotel parameter are needed to display the hotel on the map.

We then need to create the fields used to display the pubs.

Pub Name

IF [Location Type]=’Pub’ THEN [Name] END

Pub Location

IF [Location Type]=’Pub’ THEN MAKEPOINT([LAT],[LON]) END

You should now be able to create the map following the steps outlined above in the intermediate challenge. One axis will show the buffer around the selected hotel, the other will show all the pubs.

The pubs need to be sized & coloured based on the distance from the selected hotel, so we need

Distance Selected Hotel-Pub

DISTANCE([Selected Hotel Location],[Pub Location],’m’)

Add this to the Size & Colour shelf of the pubs marks card, and adjust to suit (you’ll need to reverse the colour range). Also note, there are 2 pubs named Alchemist, so add Neighbourhood to the Detail shelf too to make sure the distance calcs returns the correct values. Update the tooltip on the pubs mark too.

Finally

  • update the tooltip on the pubs mark
  • add the Selected Hotel parameter to the Label of the hotel mark and adjust font to suit
  • remove the tooltip from the hotel mark

At this point the main map is built, but Sean has added a bit extra to this challenge, a bar chart to drive the hotel selection with a sort selector to drive the ranking of the hotels; all of this is wrapped up in a collapsible container – phew!

Let’s break this down and start with the bar chart.

Hotel Selector Bar Chart

Build a bar chart as follows :

  • Name, Yelp Rating (as discrete field), Price Rating on Rows
  • Yelp # of Ratings on Columns
  • Location Type = Hotel on Filter
  • Is Selected Hotel on Colour
  • Show mark labels so Yelp # of Ratings is displayed at the end of the bars

Adjust formatting to match (remove column/row lines, set the row banding, hide headers etc)

Set the Alias of the Price Rating field, so Null displays as <blank>

Name the sheet Hotel List or similar.

On a dashboard, add the Hotel List and the Map, so we can create the parameter action (Dashboard -> Actions -> Add Action -> Set Parameter) to interact between the list and map.

Clicking a hotel in the bar chart should now change which hotel is selected in the map.

Bar Chart Sort Selector

The bar chart can be sorted based on the 3 measures displayed; Price Rating, Number of Ratings, YELP Rating. We need to build the selector to allow a choice, and then change the bar chart based on the selection. This again is parameter actions, and builds on techniques used in previous WoW challenges blogged about here and here and here.

As a result, I’ll be relatively brief about how the selector is built, as the blogs should help with this.

I used 3 instances of MIN(0.0) on the Columns, and aliased the Measure Name of these to ‘ Yelp Rating ‘, ‘ Price Rating ‘, ‘ Number of Ratings ‘ (Note the spaces either side). I also adjusted the axis of each measure to make them all appear left aligned,(this was a bit trial & error).

I also needed a parameter Selected Sort Measure defaulted to ‘ Price Rating ‘

Three calculated fields are used to set the Shape of the displayed mark for each measure

Sort – Price Rating

[Selected Sort Measure] = ‘ Price Rating ‘

Sort – Number of Ratings

[Selected Sort Measure] = ‘ Number of Ratings ‘

Sort – Yelp Rating

[Selected Sort Measure] = ‘ Yelp Rating ‘

I also added the True = False url action trick to ensure the marks all appeared ‘selected’ when only one was selected.

To invoke the sort on the bar chart itself, create a calculated field

Chart Sort

CASE [Selected Sort Measure]
WHEN ‘ Yelp Rating ‘ THEN SUM([Yelp Rating])
WHEN ‘ Price Rating ‘ THEN SUM([Price Rating Sort]) * -1
WHEN ‘ Number of Ratings ‘ THEN SUM([Yelp # of Ratings])
END

Note the Price Rating Sort field is multiple by -1 to ensure it displays from lowest to highest on the sort, whilst the other fields will display highest to lowest.

Alter the Hotel Name field on the Hotel list bar chart to sort descending by Chart Sort

Add the Sort Selector sheet to the dashboard, and add a parameter action

You should now be able to play around, selecting a sort option to change the order of the hotel list, then selecting a hotel to change the map.

Hiding the hotel list / sort selector

On the dashboard add a vertical container, then place the Sort Selector sheet and the Hotel List bar chart inside.

Remove the chart titles, set the background of the container to white, then set the container to be floating and select the container option to Add Show/Hide Button.

A Cross image will appear, select to Edit Button and change the button style to Text Button

In the Title section enter the required text for when the section is displayed (Item Shown) and then for when the section is collapsed (Item Hidden). Adjust the font too.

After hitting apply, the button section, will need resizing to get the text to display

The show/hide functionality needs to be manually selected on Desktop. When on server the interactivity will work. So to close the container, on the button menu, select Hide

and the container with the selector and the bar chart will disappear

Now it’s all just about finalising the dashboard to display all the objects in the appropriate locations. The colour/size legend and Buffer parameter are also within a container, which is floated and positioned bottom left.

Hopefully I’ve covered everything. There’s a fair bit going on in this Jedi version!

My published versions are here.

Happy vizzin’!

Donna