Tag: Collapse

Profitability Spotlight – is your budget recovered?

Donna ColesLeave a comment

For week 22 of #WOW2020, Ivett Kovacs set her first challenge as a guest poster.

At first glance it looked like it would be tricky, and it was! There were many head-scratching moments as I made my way through this, and I couldn’t complete it all without having to look at Ivett’s solution – more on that later.

Understanding the data

First up, a word on the data provided, as the requirements weren’t as clear as I’d have liked.

Ivett provided a small custom data set to build this challenge on

It contains 2 rows for each Sub-Category. The value to be plotted for Review is evident on the challenge – it’s an average. But the aggregation for Revenue & Budget isn’t that obvious, as there is no direct indication on a label or in the tooltip. Should the values be summed or averaged, or a combination of both?

The Profit value in the tooltip is the only clue you have. The Profit value for Tables is -$60, the value for Chairs is $50. From examining the values in the data, the assumption is Profit = AVG(Revenue) – AVG(Budget), so the values to plot for Budget and Revenue need to be averaged.

The assumption is this data set is a combination of a two datasets; one containing the Revenue & Budget per Sub-Category

the other containing the customer reviews

So when it comes to the viz, we’re looking to plot based on the following data

Understanding what numbers I need to be aiming for when doing these challenges is crucial to me to ensure I’m heading down the right path 🙂

Duplicating the Data

The key thing to realise about this chart is that the right hand line is a fake axis; ie not an axis at all, just a vertical line located so that it looks like it is an axis.

What we actually need to do is build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate. This means we need 3 rows of data per Sub-Category, 1 row to represent each point being plotted.

So for this we need to Union the data twice, so there are 3 instances of the data – drag another instance of the table into the data pane and drop when the Union option appears. Do this twice.

The union duplicates the rows of data, and each set is identifiable by an automatically generated Table Name field containing values WW, WW1,WW2

Defining the points of the triangle

As I said above, we need to build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate.

At point 1, we’re going to plot our Review value. At point 2, we’re going to plot our Revenue value, and Budget will be at point 3.

From the diagram above, you can see our x coordinates are one of two numbers, either 0 or x1 (ie the x coordinate for the Revenue & Budget points is the same).

The y coordinates vary per measure, and need to be ‘normalised’ to a common scale, as otherwise, the Revenue & Budget figures would be plotted significantly higher than the Review values (you might find this blog by Zen master Jonathan Drummey useful at this point).

When normalising, you’re typically looking to convert your values to a scale from 0-1; this means the values aren’t plotted at their absolute values, but are still plotted in the same relative order to each other ie lowest value at the bottom, highest at the top.

When normalising a set of values from 0 to 1, your lowest value would typically be at the 0 position , with your highest value at the 1 position. To calculate where your value would sit on this scale, you need to know 3 numbers; the value to convert, the maximum value in the range of values to plot and the minimum value in in the range of values to plot. The normalised value is then :

(Current value – min value) / (max value – min value)

ie the difference between your value and the lowest as a proportion of the difference between the whole range.

However, in this instance, I chose to simplify the normalisation, and my method only works due to the values in the example data provided.

Side Note I consider Revenue & Budget to be values that are directly related to each other ie if Budget = Revenue I’d expect them to be plotted at the same point. I therefore chose to normalise these values across the combined range. This gave me different results from the solution, where Budget and Revenue were normalised independently of each other.

The maximum average review value is 5, the maximum value for budget and revenue combined is 100. As 100 is exactly 20 times bigger than 5, I simply chose to normalise the revenue/budget values to be on a scale of 0-5 instead, rather than normalising all values (Review, Budget & Revenue) to be on a 0-1 scale.

First up, we want to identify a position for each point

Path

IF [Table Name] = ‘WW’ THEN 1
ELSEIF [Table Name] = ‘WW1’ THEN 2
ELSE 3
END

Then we’ll create the x coordinate for each point

x

IF [Path] = 1 THEN 0
ELSE 20 END

I’ve just chosen an arbitrary value to plot the 2nd & 3rd points at – could easily have been 1.

Then we’ll create the y coordinate for each point, which is where the normalisation comes in

y

IF MIN([Path]) = 1 THEN AVG([Review])
ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20
ELSEIF MIN([Path]) = 3 THEN AVG([Budget])/20
END

Dividing by 20, normalises the values to a scale of 0-5 as discussed above.

Let’s put these values all out in a table, so we can see what’s going on

Building the Polygon Viz

You can see we have 3 points per Sub-Category, so we can plot the x & y measures on a sheet as follows :

  • Add Min(x) to Rows
  • Add y to Columns
  • Add Sub-Category to Detail
  • Add Table Name (or Path) to Detail
  • Change Mark Type to Polygon
  • Change Colour to #666666 with 30% opacity

Tooltips

We need 2 new calculated fields for the Tooltip

Profit

AVG([Revenue]) – AVG([Budget])

formatted to $ with 0 dp

Margin

[Profit]/AVG([Budget])

formatted to % with 1 dp.

Note this will differ from the solution, where I think Ivett inadvertently used SUM(Budget) rather than AVG.

Add these to Tooltip field and adjust text accordinly.

Colouring the Line

To make the line, I created a second instance of y

y2

IF MIN([Path]) = 1 THEN AVG([Review])
ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20
END

which just plots 2 points rather than 3.

Add this to Columns next to y, make dual axis and synchronise axis. Things might have disappeared – you need to remove Measure Names from both marks cards. Change the mark type of the y2 card to Line.

You won’t see much difference at this point (or you shouldn’t). We need a field to define the colour

Colour : Line

IF [Profit] < 0 THEN ‘Non-Profitable’ ELSE ‘Profitable’ END

Add this to the Colour shelf of the y2 marks card and adjust to suit.

Labelling the line

On the y2 marks card, add Review (set to AVG) to Label shelf, and move Sub-Category to Label shelf. Set Label to only label Start of Line. Adjust format/layout of Label to suit.

Set the format of Review to Number Standard – this is a format little used, but will display a whole number or a decimal. I discovered this through an Andy Kriebel WoW from a long time ago and is a real gem!

Finalising the viz

To tidy up and get the viz looking like the solution

  • Format to remove all gridlines
  • Hide the y2 axis
  • Hide the ‘4 nulls’ indicator if you have it
  • Edit the y axis
    • Fix the axis from 0.5 to 5.25
    • Add a title
    • Set axis ticks to none
  • On the y2 marks card, edit the Colour shelf and change the markers to show to All (this gives the circles on each end of the line)
  • Change the Min(x) pill to be FLOAT(Min(x)) using the ‘type in pill’ function
  • Edit the x axis to be Fixed from -0.3 to 19.9
    • cutting off the end of the axis makes the end circle disappear. This is very sneaky, and I had to see the solution for this!
  • Hide the x axis
  • Remove the row divider lines

So we’ve now got the core viz – hooray! But we’re not quite done – boo!

Expand /Collapse

Ivett set the viz to expand to show a column by Sub-Category on click.

This is another requirement I couldn’t get however hard I tried. I duplicated my viz and created a version with Sub-Category on Columns and tried to use Parameter Actions to set a parameter that would control which viz would display using a sheet swap techinque. However when I added the necessary field to the Detail shelf, all the polygons disappeared and I don’t understand why…. I therefore published my first instance of this challenge with a parameter the user controlled to decide which view to show. This is here.

So I had to look at Ivett’s solution to see how she had achieved this, and it involved sets.

Right click on Sub-Category and Create – > Set

Selected Sub-Cats

Don’t select any values at this point.

Create a field

Expand

IF [Selected Sub-Cats] THEN [Sub-Category]
ELSE ‘Click to Expand’
END

If there are values in the set, then the set values will be returned, otherwise the text ‘Click to Expand’ will display.

Add this to the Columns shelf, and ‘Hide field labels for columns‘ so the text ‘Expand’ doesn’t show.

A dashboard action will ultimately drive the behaviour, but we can test the display by editing the set and selecting all values.

The view should expand as expected, showing a column per Sub-Category

But the name of the Sub-Category is still displayed on the label, and in the example this isn’t the case. To fix this I created another calculated field

Label: SubCat

IF [Selected Sub-Cats] THEN ” ELSE [Sub-Category] END

which I added to the Label shelf, and adjusted the display to suit.

‘Reset’ the view to show the collapsed version by re-editing the set and removing all values.

How to Read this chart legend

I simply duplicated the main viz, and filtered by Sub-Category = Chairs.

I removed the label, and then used the Annotate Point functionality to add the relevant labels against the points.

Profitable Legend

The Profitable / Non-Profitable legend makes use of our trusted friend MIN(0), with pills arranged as below.

Applying the Set Action

Create a dashboard to the size specified, and just use floating objects to position the text and various sheets.

To drive the expand / collapse function, create a Set Action on the main viz as below, that targets the Selected Sub-Cats set.

Revenue / Budget Label

This is just managed using Text objects on the dashboard, carefully positioned in the right locations. You might need to add additional objects to get the layout required.

The background of the whole dashboard is set to light grey and the sheets and objects need to be set to the same grey or white to get the same presentation.

So fingers crossed, you should have a complete solution now.

My final version (after I peeked at Ivett’s) is here.

Happy Vizzin’! Stay safe!

Donna

Which products are most profitable?

Donna Coles2 Comments

I’m starting to write this blog with a bit of uncertainty today as I’m not ultimately sure where I’m going to end up….

I wasn’t even sure I was going to pen an entry this week…. I found Luke’s challenge tough, and the path I took full of multiple wrong turns, that meant trying to write out a comprehensible ‘how I did it’ quite tricky, as reviewing now I’m questioning ‘what did I do that for…’.

Whilst with perseverance and a bit of inspiration from Rob Saunders, I did manage to post a working solution, I knew I wasn’t overly happy with it, particularly because my expand and collapse functions didn’t behave as I saw others do… I had to click twice to collapse.

Before starting to write, I decided to check out Luke’s workbook which he’d finally published to see if I could understand where I was going wrong.

I’d used both a set action and parameter action in my attempt; Luke had just a parameter action. In trying to understand why I ended up with a set action and see if I could do without it, my solution gradually started unravelling, as various calculated fields needed changing.

So, I think the best way to approach this blog is to rebuild my solution from scratch, using only my existing workbook as a reference, and I will attempt to write and screen shot as I build. This could take some time, and I have no idea how successful I’ll be…. I may well get to a point where I’ve taken a wrong turn again, and everything I’ve written needs to be scrapped… at which point I may just have to say ‘sorry, I tried!’……

Ok, let’s get cracking.

Top N Sub-Categories

At the ‘first’ level of the table, we need to display the top n sub-categories ordered by Profit. Those not in the top n should be displayed under an ‘All Others’ grouping, and always displayed at the bottom. The top n can vary based on the user input.

We need a parameter to define the value of the top n.

SUB-CATEGORIES TO SHOW

This is an integer parameter ranging from 1-10, defaulted to 3. I’ve titled it exactly as displayed on the output, so it’s one less change to make later (hence the capitals).

The easiest way to group the sub-categories into those in the top n, is to use a set. Right click Sub-Category and Create -> Set.

Top N SubCats by Profit

Use the Top tab to define the rules for which Sub-Category to include, referencing the SUB-CATEGORIES TO SHOW parameter and the Profit field as shown below.

SubCat Group

IF [Top N SubCats by Profit] THEN [Sub-Category] ELSE ‘All Others’ END

If the Sub-Category is in the set then the name will display, otherwise it will be grouped under the ‘All Others’.

Add SubCat Group to Rows and change the sort on the pill to sort by Field = Profit desc

This will result in All Others being listed at the top, but to resolve that, drag the Top N SubCats by Profit set onto the Rows an place in front of the SubCat Group.

Let’s get some of the measures we need out onto the canvas.

Margin

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

format to percentage with 0dp.

Add Margin, Profit & Sales onto the sheet.

Top N Products

The next level in the table displays the Top N products per SubCat Group based on their margin. For those not in the Top N, the products should be grouped under ‘All Others’, and listed at the bottom. The Top N is once again defined by a user input via a parameter.

PRODUCTS TO SHOW

Once again an integer parameter ranging from 1-10 but defaulted at 5 this time.

Unfortunately, this time we can’t use a set to define our Top N grouping. This is because the set will only consider the Top N across all Products and will not consider the fact the Products are nested per Sub-Category grouping. So we need to come up with an alternative. I did do a bit of research to find ideas, and found this article by Emma Whyte at The Information Lab to get me started : Showing a Nested Top N with Other in Tableau.

The Top N needs to be based on the order of the Margin, or the rank.

Margin Rank

RANK_UNIQUE([Margin])

Change this to be a Discrete field.

This will give us a unique ‘number’ per row displayed based on the value of Margin. If records have the same Margin value, using RANK_UNIQUE will mean they get a different rank number (as opposed to how other ranking functions work). The table calculation of INDEX() could work just as well. NOTE – there is potential though that when the Margin values are the same for different products, what makes the Top N and what doesn’t may differ, so you might find you get a slightly different list from some of the solutions you see.

Add Margin Rank and Product Name onto the sheet, and edit the table calculation on Margin Rank, to compute by Product Name only

We now want to group the Product Name based on the Top N products.

Product Name Group

IF [Margin Rank] <= MIN([PRODUCTS TO SHOW])
THEN ATTR([Product Name])
ELSE ‘All Others’ END

We also only want to show (PRODUCTS TO SHOW + 1) rows per SubCat Group ie if PRODUCTS TO SHOW = 5, we want to display 6 rows per SubCat Group, where the 6th row displays ‘All Others’. The 6th row also needs to show the Profit, Sales and Margin values associated to all the Products in the ‘All Others’ Product Name Group.

So we need to calculate some new fields that will store a revised value for Proft, Sales and Margin, depending what row we’re working with.

Sales For Others

WINDOW_SUM(IF [Margin Rank] > [PRODUCTS TO SHOW] THEN SUM([Sales]) END)

If the Product Name isn’t in the Top N, then get it’s Sales value, and then sum all of those rows that meet the same condition.

If you put this onto the sheet, and set the table calculation for each of the nested calculations (Sales for Others & Margin Rank) to be by Product Name only, you’ll see that the value displayed in every row for each SubCat Group is the sum of the values associated to the rows in the ‘All Others’ group.

So now we need a field that’s either going to display the Sales for the 1 product or the sales for the group of products, depending on what row we’re on.

Grouped Sales

IF [Margin Rank]<= MIN([PRODUCTS TO SHOW]) THEN SUM([Sales]) ELSE [Sales For Others] END

Again the table calculation settings need to be set to compute by Product Name only. This is the field we ultimately want to display, so it needs to be formatted accordingly. The Sales and Sales For Others fields can be removed.

Along similar lines, we need

Profit For Others

WINDOW_SUM(IF [Margin Rank] > [PRODUCTS TO SHOW] THEN SUM([Profit]) END)

Grouped Profit

IF [Margin Rank]<= MIN([PRODUCTS TO SHOW]) THEN SUM([Profit]) ELSE [Profit For Others] END

Margin For Others

IF [Margin Rank] > [PRODUCTS TO SHOW] THEN [Profit For Others]/[Sales For Others] END

Grouped Margin

IF [Margin Rank] <= MIN([PRODUCTS TO SHOW]) THEN [Margin] ELSE [Margin For Others] END

When added to the sheet, once again make sure all the table calculation properties for all the nested calculations are set to compute by Product Name only.

As mentioned above, we only want to show PRODUCTS TO SHOW + 1 rows, so let’s create a field we can filter by :

Show?

[Margin Rank] <= MIN([PRODUCTS TO SHOW]) + 1

Add this to the Filter shelf and set to True. Once again the table calculation needs to be set to compute by Product Name only.

Add Totals

We want a grand total and subtotals only at the level of SubCat Group. Add the totals by Analysis -> Totals -> Show Column Grand Totals to get the overall total. Then on the SubCat Group pill, click and add SubTotals

We’ve now got the main components of the table. We now need to get the interactivity working to allow the expand / contract on arrow selection.

Expand / Contract All Others Product Group

First up, we’ll just tidy up our table display

  • Hide the In/Out Top N SubCats By Profit field
  • Hide the Margin Rank field
  • Hide the Product Name field.
  • Remove the Margin field
  • Alias the Grouped Margin, Grouped Profit, Grouped Sales fields
    • Right click and Edit Alias. Name the field ‘ Margin ‘ (note the trailing and leading spaces. You can’t alias just as Margin as a field already exists with that name, but the spaces make it think it’s a new name.

We need an additional field that will store our ‘arrow’ icon. We’re going to revisit this field. For starters

Product Group Header

IF [Margin Rank] <= [PRODUCTS TO SHOW] THEN ”
ELSE ‘â–º’
END

I use this site to get my icon characters from. Add this field to the sheet after the SubCate Group pill, once again setting the table calculation to compute by Product Name.

In the dashboard, the aim is to click on an arrow associated to a single SubCat Group, which will expand the Product Group Name field to display the actual Product Name (rather than ‘All Others’) with their associated Margin, Sales & Profit values, and also show a â–¼ icon.

This will be achieved using Parameter Actions, for which we need a parameter :

Selected Sub Category Group

String parameter defaulted to ”

Display this parameter on the sheet, as we can start to test the interactivity ‘manually’ without the need for the dashboard. What the dashboard action will do is on ‘click’, it will be set to populate the value of this parameter with the associated SubCat Group value. We can then do some checks based off of this and set various fields accordingly. It means we need to revisit some of the fields.

First up let’s set the arrow….

Edit Product Group Header to be

IF [Selected SubCat Group] = MIN([SubCat Group]) THEN
IF [Margin Rank] > [PRODUCTS TO SHOW] THEN ‘â–¼’ ELSE ” END
ELSEIF [Margin Rank] <= [PRODUCTS TO SHOW] THEN ”
ELSE ‘â–º’
END

Test this by entering the value of ‘Copiers’ into the Selected SubCat Group parameter. The arrow against ‘All Others’ should change.

We also need to change the value of the Product Name Group to show the actual Product Name on selection, so

edit the Product Name Group

IF [Margin Rank] <= MIN([PRODUCTS TO SHOW])
THEN ATTR([Product Name])
ELSE
IF [Selected SubCat Group] = MIN([SubCat Group])
THEN ATTR([Product Name])
ELSE ‘All Others’ END
END

Again test this out by changing the value in the parameter.

But we need to make more rows show too, so

edit Show?

([Margin Rank] <= MIN([PRODUCTS TO SHOW]) + 1) OR (MIN([SubCat Group]) =[Selected SubCat Group])

The values of our measures are still the totals though, so we need to edit these fields to

Grouped Sales

IF ([Margin Rank]<= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group] = MIN([SubCat Group])) ) THEN SUM([Sales]) ELSE [Sales For Others] END

Grouped Profit

IF ([Margin Rank]<= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group] = MIN([SubCat Group])) ) THEN SUM([Profit]) ELSE [Profit For Others] END

Grouped Margin

IF ([Margin Rank] <= MIN([PRODUCTS TO SHOW]) OR ([Selected SubCat Group]=MIN([SubCat Group]))) THEN [Margin] ELSE [Margin For Others] END

However while this works if you play with setting and clearing the parameter on the sheet, it won’t quite fully work if added as a dashboard action, as while the action can set the parameter we can’t ‘clear it’.

We need to ‘tie’ the parameter action to another field

SubCat Group for Reset

IF [Selected SubCat Group] = MIN([SubCat Group]) THEN ”
ELSE MIN([SubCat Group])
END

Add this to the Detail shelf (if you want to see how it changes based on the parameter value, add it to the Rows and test changing the parameter).

It needs to be on the sheet so it can be referenced from the dashboard action.

Adding the Action

Add the sheet to a dashboard, then add Parameter Action as below where the Target Parameter is Selected SubCat Group and the field it references is SubCat Group for Reset

And after all that, you should have a working solution. Phew!

Pretty pleased I got there without taking a detour 🙂 The table just now needs various formatting applied, which I’m going to leave to you to do 🙂 Just tweet me if you’re having problems!

The parameter action may be confusing you a bit – it took a while to really get my head round it, so I’ve tried to explain this a bit more below…

How the parameter action works

On initial load of the sheet, the Selected SubCat Group parameter is blank. So for the SubCat Group = Copiers, the SubCat Group for Reset will also be Copiers as SubCat Group is not the same as Selected SubCat Group parameter. Other fields are also set based on the fact these two fields aren’t the same (like the arrow pointing to the right etc).

When the right arrow is clicked on the dashboard, the value of SubCat Group for Reset is used to populate the Selected SubCat Group parameter. So in this example, Selected SubCat Group will now contain the value Copiers. As the Selected SubCat Group parameter is now the same as the SubCat Group, various fields change their behaviour (like the arrow now points down, and more rows are displayed). But also, the value of the SubCat Group for Reset is also changed; as the SubCat Group is the same as the Selected Sub Cat Group parameter, SubCat Group for Reset now contains a blank string.

So at the point the down arrow is now clicked again on the dashboard, the value of the SubCat Group for Reset is again used to populate the Selected SubCat Group parameter. As SubCat Group for Reset is blank, then the parameter will now be populated with a blank value, and so all the login in the fields will be based on the fact that SubCat Group is not the same as the Selected SubCat Group parameter, and the table will display just as it did on first load.

Hope that helps to demystify what’s going on… it’s certainly helped me!

Thanks for sticking with me if you got this far 🙂

The version of the challenge I built while writing this (my 3rd published version) is here.

Happy vizzin’! Stay Safe!

Donna

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

Donna Coles1 Comment

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

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

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

Sean provided 2 versions for the challenge with supporting datasets.

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

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

Join the data

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

These 2 data sets need to be Inner Joined together as

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

INTERSECTS

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

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

Mapping the Hotels

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

Buffer Hotel

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

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

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

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

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

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

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

Mapping the Pubs

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

Pub Location

MAKEPOINT([Lat],[Lon])

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

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

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

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

Distance

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

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

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

Now make dual axis

Hotel List – Viz in Tooltip

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

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

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

Name the sheet Pubs or similar

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

This will insert text as below

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

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

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

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

onto the next….

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

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

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

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

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

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

Is Selected Hotel?

[Name]=[Selected Hotel]

Selected Hotel Lat

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

Selected Hotel Long

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

Selected Hotel Location

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

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

Hotel Buffer

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

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

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

Pub Name

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

Pub Location

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

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

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

Distance Selected Hotel-Pub

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

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

Finally

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

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

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

Hotel Selector Bar Chart

Build a bar chart as follows :

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

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

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

Name the sheet Hotel List or similar.

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

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

Bar Chart Sort Selector

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

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

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

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

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

Sort – Price Rating

[Selected Sort Measure] = ‘ Price Rating ‘

Sort – Number of Ratings

[Selected Sort Measure] = ‘ Number of Ratings ‘

Sort – Yelp Rating

[Selected Sort Measure] = ‘ Yelp Rating ‘

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

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

Chart Sort

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

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

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

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

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

Hiding the hotel list / sort selector

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

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

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

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

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

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

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

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

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

My published versions are here.

Happy vizzin’!

Donna