Luke posted the challenge this week to build a Sales funnel using bar charts and table calculations.
The data provided was structured as follows
Each row identified a record in the pipeline along with its value. The last_stage field indicated what stage in the process the record was currently at. The value and last_stage are the only two bits of data needed for this challenge, along with the knowledge that the process moves through the stages in the following order :
Prospect
Lead
Qualified
Opportunity
Negotiations
Closed
which you can tell from the diagram anyway.
The challenge is that the value of any record currently at stage 2 (lead) or above, also needs to be incorporated into the total value of the previous stages.
When tackling these types of challenges involving table calculations, I start by creating a table view of all the data I need. So let’s get cracking…
Building up the data required
First up, I need a field to help drive the order of the stages, so I created
last_stage Order
CASE [last_stage] WHEN ‘Prospect’ THEN 1 WHEN ‘Lead’ THEN 2 WHEN ‘Qualified’ THEN 3 WHEN ‘Opportunity’ THEN 4 WHEN ‘Negotiations’ THEN 5 ELSE 6 END
Add this to a view along with the value
This is essentially the data needed to build the Current Status column in the output.
Next we need to work out the Overall Funnel values. As Luke stated in the requirements, this is going to involve table calculations. What we want is a running sum but one that starts at the bottom at stage 6, and goes ‘up’ the table. But there is no Table Up option in Tableau, so we need to be creative.
We’ll still need a running sum though, so create this
Running Sum
RUNNING_SUM(SUM([value]))
Add this to the view, it will automatically be applied Table Down, which is what we need at this point – each row is the sum of the previous rows above it.
We also need the overall total captured against each row in the table.
Window Sum
WINDOW_SUM(SUM([value]))
Now we’re in a position to work out our ‘cumulative’ value or Overall Funnel, which is a calculation involving all 3 fields
Cumulative Value
[Window Sum]- ([Running Sum] – SUM([value]))
Note – for the values that will be displayed, I’ve applied formatting of $ to 0 dp.
Now onto the final Percent to Close calculation which is the value of records at stage closed, as a proportion of the cumulative value. So we need to get the closed value stored against every row
Closed Value
{FIXED : SUM(IF [last_stage order] = 6 THEN [value] END)}
so then we can determine
% To Close
SUM([Closed Value]) / [Cumulative Value]
which is formatted to percentage to 0 dp
So now we have all the data we need to build the viz. I like to save this sheet for future reference, and to double check as we start moving pills around to build the viz.
Building the viz
Start by duplicating the table sheet, and remove the calculations, so only the 3 necessary (Sales, Cumulative Value & % To Close) are listed.
Then move these 3 onto the Columns shelf, and in doing so, the viz should reformat as a bar chart automatically.
Set the colour of the bars to the dark green (#00646d) and tick the Show mark labels option on the Label shelf (you may need to expand the width of the rows to get the label to show.
The first 2 columns are displaying what we need, but the final one needs to show the bars ‘filling up’ to 100%. We need a dual axis for this.
Type into the Columns shelf Min(1) to create a fourth column. Uncheck show mark labels for this column only.
The set this field to be Dual axis, and synchronise the axis. It’s likely the marks will all change to circles, so reset the ‘All’ marks card back to bar. On the Min axis, right click and select Move marks to back, to ensure the % Closed values are sitting on top.
Now change the colour of the Min(1) field to light green (#7cadb2), untick Show Header against last_stage order & SUM([Value]) to hide the axis. Also right click on last_stage in the view, and select Hide field labels for rows.
Finally adjust the size of the labels displayed to 8pt. On the % To Close marks card, change the label alignment to left, and set the font colour to white. Uncheck Show Tooltips for all marks.
And you should now have the main display.
Oh, adjust the Size of the bars to suit.
When added to the dashboard, add a horizontal container above the viz, and use text boxes to display the column titles.
The requirement was to show some month to date / year to date metrics in comparison to the previous month to date, and also work out where the current month might finish.
I took up the challenge on Weds 25th March, and the challenge was dated ‘as at 24th March’. There was nothing in the requirements to indicate whether this was ‘hardcoded’ to this date, or whether it happened to be this date based on the time I viewed (ie it was yesterday, the last full day). In the interest of being ‘flexible’ I therefore chose to design my solution more dynamically. I based my challenge on viewing the data up to ‘yesterday’, where ‘yesterday’ is yesterday’s date in 2019. So if today is 27 March 2020, then the dashboard will be based up to 26 March 2019. Hope that’s clear.
As a result of this, there’s a fair few date calculations involved, so let’s crack on.
Setting up the calculations
I like to build up my calculations so they’re easier to read, rather than nest everything. First up we need
Current Year
{MAX(YEAR([Order Date]))}
The maximum year in the data set (which happens to be 2019 for the data source I’m connected to).
To get this, use the formatting to set to $ and 1 decimal place, then once set, change the format to ‘custom formatting’, which will display the ‘formatting code’. Then add the ▲▼ symbols. I use this site to get the characters I need.
The final calculation we need to work out is what’s been referred to as the ‘run rate’. This is basically trying to show what the final month sales/profit will be, based on the current rate. This means taking the current MTD Sales/Profit, dividing it by the number days this has been computed over to get an average sales/profit per day. Then this number is multiplied by the number of days in the month. Got it?
This is counting the number of days between the 1st of the month (based on yesterday), and the 1st of the next month.
Run Rate Sales
(SUM([Current MTD Sales])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])
Run Rate Profit
(SUM([Current MTD Profit])/SUM([Days in Current MTD])) * SUM([Days in Current Full Month])
Format these to $ and 1 decimal place.
This gives us all of the core calculations we need to build the KPIs.
Building the KPI card
This is done on one sheet, and is simply utilising the Text mark type.
By typing in, create a pill MIN(0) on the Rows shelf, and another one right next to it. Change the mark type to Text. This gives you 2 ‘cards’ you can now use. Add the all Sales related calculated fields to the Text shelf of the first card, and the Profit fields to the second card, and just format the font/layout accordingly. Then remove the axis headers, gridlines etc etc.
Building the Sales YoY Bar Chart
To ensure we only have the dates for the last 2 years, up to the current point in time, we need some additional fields
Previous Year
[Current Year]-1
Dates To Include
YEAR([Order Date])>= [Previous Year] AND [Order Date]<= [Yesterday]
Add Dates To Include to the Filter shelf and set to True.
Now build the viz by
Sales on Rows
Order Date on Columns, set to the Month level only (discrete blue pill).
Mark Type = bar
Order Date on Colour, set to Year level. Adjust colours to suit
Order Date on Size, set to Year level. Adjust size to suit so the latest year is narrower.
Set Stack Marks = Off (Analysis -> Stack Marks). This will stop the bars for each year from sitting on top of each other.
Format the Order Date axis, so the Month is displayed as 1st letter only.
Format the Sales axis so the value is displayed to K with no decimal places.
Due to the above, the format of the Sales value on the tooltip is likely to change too. If this happens, duplicate the Sales field, rename it to Tooltip – Sales or similar and format to $ with 0 dp. Add this to the Tooltip shelf.
You’ll need to do similar to get a month field for the tooltip. Create a calculated field Tooltip-Month = DATETRUNC(‘month’,[Order Date]) and custom format this to mmm yy. Add this to the Tooltip shelf.
Repeat the same steps to build the YoY Profit chart.
Date Sheet
The final dashboard indicates the date of the report. As my dashboard is dynamic and changes based on the current date, I couldn’t hardcode this. So I built the date to display on another sheet. This means I have 1 more sheet than stated in the challenge.
I simply added Yesterday to the Text shelf and referenced it
Building the Dashboard
Here we get into a bit of container fun! As I did last week, I’ll try to just step through the order you need to place the objects on the dashboard…
Add a Text object to store the title.
Beneath it, add the Date sheet.
To the right of both of these, add another Text object to store the sheet information text which will be displayed top right.
Add a Horizontal container beneath all of the above. Set the background of this container to light grey, and Inner Padding to 10 all round
Add the KPI sheet into the container and remove the title. Adjust the height of the objects to suit.
Now add a Vertical container to the right of the KPI chart. Adjust width to suit
Add the Sales YoY chart to the vertical container. Set the background of this object to white, so the title background isn’t grey.
Add the Profit YoY sheet below the Sales one. Again set the background of this to white.
Remove the container on the right hand side that contains the legends.
Add a floating blank object to the sheet. Set the background of this to light grey, and then adjust the positioning and height and width so it’s splitting your KPI card.
Finally if you haven’t already, edit the title and summary text appropriately.
The requirement was to create a bar chart showing number of orders per subcategory per day/week/month over a user defined number of years. The chart is coloured based on Profit. The bars also need to change size based on what date part was being displayed Lorna already hinted they’d be parameters and set actions involved.
Building the chart
First off let’s create the parameters we need to drive the dates we need to include in the viz.
Select Period
A string parameter listing Daily, Weekly, Monthly. The trick here is to store the datepart of day, week, month as the value, while displaying the required text for selection. Default to Weekly.
Doing this means later we can refer directly to the parameter when we need some date manipulation.
Number of Years
A range integer parameter starting from 1 to 4, defaulting to 2
With these, we can define the Date field that we need to plot on our axis
This has the effect of truncating every order date to the 1st day of the relevant month or week or just to the day, so for example if ‘month’ is selected all the orders placed in May 2019 will be grouped together under 01 May 2019 etc.
Dates to Include
YEAR([Order Date])>={MAX(Year([Order Date]))}-([Number of Years]-1)
{MAX(Year([Order Date]))} is a shortened notation for the level of detail calculation (LoD)
{FIXED : MAX(Year([Order Date]))}
which basically returns the highest year in the data set, which in this case is 2019. If the [Number of Years] parameter is set to 1 for example, we would expect all of 2019 to display, hence we need to subtract 1 in the formula so we get all orders in 2019.
Adding this to the Filter shelf and set to true will limit the orders to the dates in the years required.
Finally we need the measure
Number of Orders
COUNTD([Order ID])
Right, with those parameters set, we can now build the basic bar chart. For now we’ll just restrict the data to a single Sub-Category directly – we’ll look to adjust this later.
Add Dates to Include = True to Filter
Add Sub-Category = Tables to Filter
Add Date to Plot as a continuous exact date to Rows (green pill)
Add Number of Orders to Columns
Add Profit to Colour
Set Mark Type to Bar
Adjust the Tooltip to match
Show the Select Period & Number of Years parameters
Adjust the Sheet Title to reference the Select Period parameter
Sizing the bars
You’ll see the bars all look a bit overlapped. You might be tempted to adjust the bar size by moving the slider to reduce the overlap, which may well work, but as you change the Select Period parameter you’ll find that what you’re doing is setting the bar width to set width that is the same whether you have 24 marks displayed (monthly for 2 years) or 156 marks (daily for 2 years). The requirement is for the bar to adjust in width, so it’s wider when there’s less marks.
To do this, we need a calculated field
Size
CASE [Select Period] WHEN ‘day’ THEN 1 WHEN ‘week’ THEN 5 WHEN ‘month’ THEN 10 END
These are just arbitrary values I chose, and you can play around with the values to suit, but the key is you’re choosing a range of numbers with the smallest for ‘day’ and the largest for ‘month’.
Add this field to the Size shelf. and change it to be a Continuous Dimension ie a green pill of just Size rather than SUM([Size]).
Then click on the Size shelf and change from Manual to Fixed, and set the alignment to Centre.
Changing the Select Period parameter you’ll see the bars adjust their width from being very narrow for Daily, wider for Weekly, and wider again for Monthly.
Sub Category Selector
This is based on techniques that have cropped up in a few #WOWs this year.
We’re going to be using Set Actions for this bit, so to start we need a Set.
Right click on Sub-Category -> Create Set. Name the set Selected Sub-Category and just select a single value, Tables for now.
Now on a new sheet, add MIN(0.0) to Columns (type directly in) and Sub-Category to Rows.
Change the Mark Type to Shape
Add Sub-Category to Label
Add Selected Sub Category set to Shape, and adjust the shape and colour to suit
Create a calculated field called True and another called False, each containing the value True and False respectively – add these to the Detail shelf.
Edit the Axis to be Fixed to start at -0.07 to 1. This is to shift everything to the left.
Turn off Tooltips
Format to remove all column, row, zero & grid lines & axis rulers.
Uncheck Show Header on Sub-Category and MIN(0.0)
Building the dashboard
First up, now we’ve got a Set to store the Selected Sub-Category, remove, the Sub-Category field from the Filter shelf of the bar chart. Add the Selected Sub-Category set to the Filter shelf instead.
Now create a new dashboard sheet and add the bar chart to it.
By default, the Profit colour legend and parameters will be displayed in a vertical layout container to the right of the bar chart.
Remove the colour legend, and position the Select Period parameter above the Number of Years.
Then add the Selector sheet between the two parameters, and remove any additional legends that get added.
Add a dashboard action Deselect against the Selector sheet to stop the un-selected Sub-Categories from fading out.
Add a further dashboard action Select Sub Cat against the Selector sheet to set the value of the Selected Sub-Category set on selection. The dashboard action should be set flagged to Run on single select only, so multiple values can’t be chosen.
Change the vertical layout container to be floating, then adjust the height and set the background colour to white.
Use the Add Show/Hide Button option on the Layout container to enable the collapsible container functionality.
You’ll just need to move things around a bit, adjust the sizes to suit, but that should be pretty much it.
It was Ann’s turn this week to post the weekly #WOW challenge. There’s a fair bit going on here, so let’s get cracking.
Building the main chart
There’s essentially 3 instances of this chart. I’ll walk through the steps to create the Sales version. All the fields just need to be duplicated to build the Orders & Quantity versions.
First up we need a parameter to store the date the user selects. This needs to be a date parameter that allows all dates and is set to 8th May 2019 by default: Order Date Parameter
Based on this parameter value, we need to work out the day of the week of the parameter date, the date 12 weeks ago, and then filter all the dates to just include the dates that match the day of the week. So we need
Day of Week
UPPER(DATENAME(‘weekday’,[Order Date Parameter],’Monday’))
(the UPPER is necessary for the display Ann has stated).
Dates to Include
[Order Date]>=DATEADD(‘day’,-84,[Order Date Parameter]) AND [Order Date]<= [Order Date Parameter]
This identifies the dates in the 12 week period we’re concerned with.
I played around with ‘week’ and ‘day’, as I noticed when playing with Ann’s published solution that sometimes there were 12 dates displayed, other times there were 13, but this is just down to how the number of days in a month fall, and whether there’s actually orders on the days.
Weekdays to Include
[Day of Week] = UPPER(DATENAME(‘weekday’,[Order Date],’Monday’))
This identifies all the dates that are on the same day of the week as the Order Date Parameter.
Add both Dates to Include and Weekdays to Include to the Filters shelf and set both to True.
Add Order Date to Rows and set to be a discrete exact date. Add Sales to Text. Sort Order Date by Sales DESC
The colouring of the cells is based on 4 conditions
being the max value
being above the average value
being the min value
being below the average value
I used table calcs to work this out, giving each condition a numeric value
Colour:Sales
IF SUM([Sales]) = WINDOW_MAX(SUM([Sales])) THEN 1 ELSEIF SUM([Sales]) = WINDOW_MIN(SUM([Sales])) THEN 4 ELSEIF SUM([Sales]) >= WINDOW_AVG(SUM([Sales])) THEN 2 ELSE 3 END
Add this to the Colour shelf and change it to be a continuous (green) pill, which will enable you to select a ‘range’ colour palette rather than a discrete one. Temperature Diverging won’t be available for selection unless the pill is green; on selection, the colours will automatically be set as per the requirement. Change the mark type to Square.
We also need to identify an above & below average split so create
Sales Header
UPPER(IF [COLOUR:Sales]<=2 THEN ‘Above Average’ ELSE ‘Below Average’ END)
Note the carriage return/line break, which is necessary to force the text across 2 lines.
Add this to the Rows shelf in front of Order Date, and format to rotate label
Finally we need to show a triangle indicator against the selected date.
Selected Date
IF [Order Date]=[Order Date Parameter] THEN ‘►’ ELSE ” END
Add this to Rows between Sales Header and Order Date
Format to remove all column & row lines, then add row banding set to the appropriate level, and a mid grey colour
Finally Hide Field Labels for Rows, format the font of the date and set the tooltip.
Now we need to set the title to include the rank of the selected date.
Selected Date Sales Rank
IF ATTR([Order Date])=[Order Date Parameter] THEN RANK_UNIQUE(SUM([Sales]))END
Add this to the Detail shelf, and the field will then be available to reference when you edit the title of the sheet
Name this sheet Sales Rank or similar.
You can now repeat the steps to build versions for Orders (COUNTD(Order ID)) and Quantities (SUM(Quantity)).
Dynamic Title
To build the title that will be displayed on the dashboard, create a new sheet, and add Order Date Parameter and Day of Week to the Text shelf. Then format the text to suit
Building the Dashboard
The ‘extra’ requirement Ann added to this challenge, was to display a ‘grey shadow’ beneath each of the rank tables. This is done using containers, setting background colours and applying padding. When building this took a bit of trial & error. Hopefully in documenting I’ll get the steps in the right order…. fingers crossed…
On a new dashboard, set the background colour to a pale grey.
Add a vertical container.
Add the Title sheet into the container, and remove the sheet title
Add a blank object into the container, beneath the Title sheet.
Add another blank object into the container, between the Title and the blank, set the background of this object to dark grey, reduce the padding to 0 and the edit the height to 2.
This will give the impression of a ‘line’ on the dashboard
Now add a horizontal container beneath the ‘line’ and the blank object at the bottom. You may need to adjust the heights of the objects
Set the outer padding of this object to 5.
Add a blank object into this horizontal container. Blank objects help when organising objects when working with containers, and will be removed later.
Add another horizontal container into this container next to the blank object. Set the background to a dark gray and set the outer padding to left 10, top 5, right 5, bottom 0.
Into this dark grey layout container add the Sales Rank sheet. Set the backgroud of this object to white, and the outer padding as left 0, top 0, right 0, bottom 4. Make sure the sales rank sheet is set to Fit Entire View.
Add another horizontal container to the right of the Sales Rank sheet, between that and the blank object. Set the background to the dark grey, and outer padding to left 5, top 5, right 5, bottom 0.
Add the Orders Rank sheet into this container, again set to Fit Entire View, set the background to white and outer padding to left 0, top 0, right 0, bottom 4.
Add another horizontal container, this time between the Order Rank sheet and the blank object. Set the background to dark grey, and outer padding to left 5, top 5, right 10, bottom 0.
Add the Qty Rank sheet into this container, again set to Fit Entire View, set the background to white and outer padding to left 0, top 0, right 0, bottom 4.
Now delete the blank object to the right, and delete the blank object at the bottom. Also delete the container in the right hand panel that has been automatically added and contains all the legends etc.
Set the dashboard to the required 700 x 450 size.
Select the ‘outer’ horizontal container that has all the charts in it, and Distribute Contents Evenly
You may need to adjust the widths of the columns within the ranking charts to get everything displayed in the right way.
But fingers crossed, you should have the desired display.
Calendar icon date selector
The final requirement, is to show the date selected on click of a calendar icon. This is managed using a floating container to store the Order Date Parameter, and using the Add Show/Hide Button option of the container menu.
Select Edit Button and under Item Hidden choose the calendar icon you can get off the site Ann provided a link for.
You’ll just then have to adjust the position of the container with the parameter and the button to suit.
Note – I did find after publishing on Tableau Public, I had some erroneous horizontal white lines displaying across my ranking charts. I’m putting this down to an issue with rendering on Public, as I can’t see anything causing this, and it’s not visible on Desktop.
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
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
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
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 TextButton
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!
Luke set the #WOW challenge this week, stating it had a difficulty rating of 10/10, so it was with some trepidation when I sat down to tackle it. Was it going to be as difficult as Luke’s radial bar challenge from 2018 week 10….?
Looking at the viz, and reading through the requirements, it didn’t seem as bad to me – it’s a table calculations challenge, and I’m OK with those. I started using Tableau before LoDs were invented, so table calculations don’t scare too much. I’m no expert though, and it still often takes a bit of trial & error to get the settings right.
Building out the Calcs
With a lot of challenges, I often start by just trying to build a tabular view of the data to sense check I’m getting the right numbers, which I then publish onto Tableau Public along with my viz. With a table calculation challenge, building the table of data is crucial.
The 3 key pieces of existing data needed for this challenge are Customer Name, Order Date & Order ID.
To start we want to put these on the rows, but given this will initially generate a lot of rows of data, I chose to arbitrarily filter to a random set of 20 or so customers (include Noel Staavos, as he’s a slight exception, which may catch you out later).
You can see from the above, Noel has multiple orders on the same day, which we need to handle.
The first requirement states we need to order the data by total number of orders per customer, so for this we need the 1st of many calculated fields (this one isn’t a table calculation, though it could be….):
Total Orders
{FIXED [Customer Name]: COUNTD([Order ID])}
Add this as a discrete pill on the Rows (since the final viz needs to show it this way too). I’ve placed it after the Customer Name but it doesn’t have to be there at this point.
We need to sort the customers by this field, so click on Customer Name and select Sort to bring up the Sort dialog. Choose to Sort By Field, Sort Order Descending, Field Name Total Orders. Aggregation Sum
We’ve now got our data ordered in the way we need. The next step is to work out the 90-day reorder rate per customer, which we will tackle in several steps. In much of what follows, some of the calculations could well be done in one calculation, but I like to see the ‘building blocks’ to help verify the calcs are correct.
First up we need to work out how many days between each order, and to figure this out we need to compare the Order Date on each line to the Order Date of the previous line.
I want to display the date of the previous order on the same line as the current order, so create
Previous Order Date
LOOKUP(ATTR([Order Date]),-1)
This ‘looks up’ the Order Date on the previous row. If I wanted to look at the next row, the second attribute would be 1 rather than -1. Or if I wanted to look at the data in the row 2 rows before the current one, I’d use -2.
Add Previous Order Date to the Rows as a discrete field. By default it’ll probably show as ‘Null’, but this is because the table calculation is computing across the table (so is looking for a previous column containing Order Date which doesn’t exist), whereas we want to look down it (ie by Row).
Click on the triangle against the Previous Order Date pill (the triangle indicates its a table calculation), and select Edit Table Calculation
We need to change the settings so they calculate for each Customer Name. Set to Specific Dimensions, and uncheck Customer Name
You can see from the above, that for Noel Staavos’ multiple order on the same day, one of the orders is comparing against an order on a different date, while the other is comparing to the other order made on the same date.
Ok, so now we have the current date & previous date on the same row, we now need to work out the number of days between the dates.
Days Since Previous Order
DATEDIFF(‘day’, [Previous Order Date],ATTR([Order Date]))
Add this to the Text shelf. Hopefully it should automatically use the same table calculation settings defined for Previous Order Date, but if the numbers look off, double check the settings. They should match.
Now we can work out if the order is within 90 days of the previous order
Reorder Within 90 Days
IF ISNULL([Previous Order Date]) THEN NULL ELSEIF [Days Since Previous Order]<=90 THEN 1 ELSE 0 END
Note, I am purposely choosing to output 1 or 0 (ie a number) rather than true or false (a boolean), as it will make the next calculation easier.
Again add Reorder Within 90 Days to the table, and again sense check the table calculation settings if things don’t look right.
Now we have the information we need to work out the reorder rate per customer, which is the number of records where Reorder Within 90 Days is 1 as a proportion of the number of records with a Reorder Within 90 Days value of either 0 or 1, since the requirements state the first order for each customer shouldn’t be included in the metric.
90-Day Reorder Rate
ZN(WINDOW_SUM([Reorder Within 90 Days])/WINDOW_COUNT([Reorder Within 90 Days]))
Set this to be a percentage with 0 dp.
By choosing to set Reorder Within 90 Days to an integer, the WINDOW_SUM() is simply summing up the 1s & 0s in the column. WINDOW_COUNT() is just counting the number of 1s & 0s there are in the column. Wrapping with a ZN means any Customers without any reorders will report as 0% rather than NULL.
Let’s add that to the table now too. You’ll see it reports the same value down the whole table across all customers, whereas we need it to show a different value per customer (although still show the same value for all the rows of the same customer).
We need to once again adjust the table calculation settings for this field.
What you’ll notice this time though, is there are Nested Calculations within this field, so the settings need to be checked for both the Previous Order Date calculation and the 90-Day Reorder Rate calculation
Both need to be identical with Order Date and Order ID being checked.
At this point, we have enough that we could start building the main viz, but I’m going to continue building out the ‘check sheet’ with the data I need for the KPIs too.
For the Overall Reorder Rate < 90 days we need to get a count of all the reorders within 90 days across all customers, as a proportion of all reorders (ie not the 1st order for each customer).
Count Reorders < 90 days per customer
IF FIRST()=0 THEN WINDOW_SUM([Reorder Within 90 Days]) END
I’m choosing to output the value only against the 1st row for each customer. This is because I’m going to be adding up this column shortly, and if I outputted the value against each row, I’d be double-counting.
Add this field to the table, and once again verify the table calculation settings are applied to each nested calculation. If done right, you should get the correct number against the first row for each customer
Now I want a sum of this column
Total Reorders < 90 Days
IF FIRST() =0 THEN WINDOW_SUM([Count Reorders <90 days per Customer]) END
Again, I’m choosing just to show this value against the 1st row, but this time it will be the first row in the whole table, as this time the table calculation needs to considering all the customers.
Add this field to the view. It’ll probably automatically give you the right number, but it’s worth having a look at the table calculation settings.
It contains 3 nested calculations this time : Total Reorders < 90 days, Count Reorders < 90 days per Customer, Previous Order Date.
The settings for Count Reorders < 90 days per Customer and Previous Order Date should be as before, with Order Date & Order ID both checked.
The setting for Total Reorders < 90 days is different though. By default it like shows Table(down), which gives the right result, but to ensure things don’t go awry if the pill gets moved around for some reason, I like to explicitly set the computation, by changing to Specific Dimensions and selecting all 3 fields
So this gives me one of the values I need to help me work out the overall rate; I now need the other
Count All Reorders
IF FIRST()=0 THEN WINDOW_COUNT([Reorder Within 90 Days]) END
This again is a nested calculation. Previous Order Date should be set with Order Date & Order ID as usual, and Count All Reorders should be set across all 3 fields.
Now we have the numbers to work out
Overall Reorder Rate
[Total Reorders <90 days]/[Count All Reorders]
which is set to a Percentage to 0 dp.
Add this onto the table and check the calculation settings again. This time there are 4 nested calculations
Total Reorders < 90 days : set to all 3 fields
Count Reorders < 90 days per Customer : set to Order Date & Order ID
Previous Order Date : set to Order Date & Order ID
Count All Reorders : set to all 3 fields
Remember, you may have filtered the customers in your view, so you may get a different value from the solution at this point!
Right onto the final KPI – average number of reorders per customer. For this we need the total number of reorders, which we’ve already got- Count All Reorders, and number of customers.
There’s probably a simpler way of doing this, but I’m continuing down the same route I’ve been working on.
First I want a count of the customer for each customer (which is 1), then I want to sum that up.
Count Customers
IF FIRST()=0 THEN COUNTD([Customer Name]) END
then
Total Customers
IF FIRST()=0 THEN WINDOW_SUM([Count Customers]) END
When added to the table, this is again a nested calculation with Count Customers set to Order Date & Order ID, and Total Customers set to all 3.
Now we can work out
Average Reorders Per Customer
[Count All Reorders]/[Total Customers]
which is set to be a number to 1 decimal place.
Adding this to the table, and verify the nested calculations are all set properly
Count All Reorders : set to all 3 fields
Previous Order Date : set to Order Date & Order ID
Total Customer : set to all 3 fields
Count Customers : set to Order Date & Order ID
Yay! We’ve now got all the values we need to build the KPI table and the main Viz.
I would recommend naming this sheet as Check Data or similar.
Building the KPI Viz
First step, duplicate the Check Data sheet.
For the KPIs, we only need 2 of the measures we’ve created, so remove all the pills from the Measure Values section except Overall Reorder Rate and Avg Reorders per Customer.
Then remove Total Orders and Previous Order Date from the Rows.
Due to the way table calculations work, we need to keep Customer Name, Order Date & Order ID in the view, but we only need the 1st row in the whole table, as that is where the data we want has been stored.
Create a new field
First Row
FIRST()=0
Add this to the Filter shelf and select True
Hide Customer Name, Order Date & Order ID by unchecking Show Header from each pill
We need to create a fake axis to get the display we need. In the Columns type in Min(0) twice, then remove Measure Names
Expand one of the Agg(Min(0)) cards on the left, and then drag the Overall Reorder Rate pill from the Measure Values section at the bottom, so it sits directly on top of the Measure Values pill
The Avg Reorders per Customer will now be on the Label shelf of the other AGG(Min(0)) card.
Change the mark type of both cards to Text.
Uncheck Show Header against the MIN(0) pills, and format to remove all columns/row/grid & zero lines.
Now you can format the text for each mark to be the appropriate size and with the relevant wording.
Then finally turn all tooltips off, and remove the Customer Name filter if it’s still there.
Hopefully you should get the numbers that now match the solution! Rename the sheet KPIs.
Building the main Viz
Once again start by duplicating the Check Data sheet we built.
Move Order ID and Previous Order Date onto the Detail shelf.
Move 90-Day Reorder Rate onto the Rows, and set to discrete
Move Order Date to Columns and change to continuous. Remove Measure Names from Columns.
Change the mark type to shape and move the Reorder Within 90 Days pill from the Measure Values section onto the Shape shelf. Change the shapes so NULL is the triangle and 0 and 1 are both diamonds.
Add Reorder Within 90 Days to the Colour shelf too, by clicking on the one on the Shape shelf, holding down Ctrl and dragging onto Colour. This will have the affect of duplicating the pill and will retain the table calculation settings. If you can’t get this to work, then drag from the Measures pane on the right, but make sure you set the table calc settings to match.
Change the colours so Null & 0 are grey (#CAC4BE) and 1 is purple (#5557EB).
The Tooltip for each shape also needs to be coloured accordingly, based on whether the mark represents the First order, a reorder over 90 days, or a reorder under 90 days (I chose to add the logic for first). I created 3 calculated fields for this:
Tooltip: First
IF FIRST()=0 THEN ‘First Order’ END
Tooltip:Over 90
IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END
Tooltip:Witin 90
IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END
I placed all 3 of these fields on the Tooltip shelf which was then formatted as:
while all 3 fields are in the tooltip, only 1 will ever actually display a value,
Make sure the table calculation settings for all 3 tooltip fields are set with Order Date & Order ID checked.
Now we need to create the line to join the marks up.
Duplicate the Order Date pill on Columns to create 2 instances of the pill sitting side by side. Set to Dual axis, synchronise and s’move marks to the back’ so the lines are behind the shapes.
Change the mark type on the Order Date (2) card to line. The requirements state
“Make sure the “line” color matches with the following mark. It must be a single color“.
Hmmmm… I don’t think that last sentence was there when I tackled the challenge… I just read the line colour ‘matches’ with the following mark, so to do this I added Previous Order Date to the Path shelf. The line graduates in colour, but from the colour of preceding mark to the colour of the following mark.
Looking at Luke’s solution now that I’ve seen this statement, he chose to use a Gantt mark type to represent the line, which meant each gantt block was coloured fully. Oh well… I didn’t quite get this then 🙂
Stop the tooltips from showing on the line, by deleting the text in the tooltip, then its just a case of re-sizing the marks, and formatting to suit. Set the background colour of the worksheet to grey.
Adding the red header on the dashboard
To get the red header displayed above the table, add, a container onto the dashboard and add the viz to sit inside it. With the container selected (identified by the blue border), set the background of the container object to red (#D81159).
Then select the viz itself (identified by the grey border), and adjust the outer padding settings, to have 0 left, bottom & right, and 4 at the top.
Hopefully this should now be pretty much everything needed to get this challenge sorted. If I’ve missed anything, please comment. My published viz is here.
It’s a pretty lengthy read, so if you’ve got this far – well done & thank you!
The challenge for this week’s #WOW was set by Sean Miller, with a focus on creating a more ‘traditional’ list style report. The challenge was related to the structure of the data, and using Tableau to take multiple rows of data for a patient, and present as a single row, with some of the information concatenated into a string of comma separated values.
Whilst there are other tools & methods that could be used to shape the data before it hits Tableau, which may be more appropriate/performant for a business use, this is after all a Tableau challenge.
I figured out what I was going to need pretty quickly – a table calculation to compare rows to build up the string. However I did struggle with the volume of data being used. It caused Tableau to keep crashing initially, so I had to ping Sean to sense check I was on the right track, and there wasn’t something I was missing. Sean confirmed I was doing the right thing, so I decided to start building against a much smaller set of data. I restricted the data by adding a data source filter, and using a wildcard filter against Member Name starts with ‘a’.
So onto the build.
Building up the concatenated string
Add Member Name and Health Check Name to Rows to get the set of rows we need to work with.
What we’re aiming for is to get a concatenated string of each Health Check Name for each Member Name, on a row.
We’re going to achieve this by building up a string that combines the Health Check Name for the current row, with the combined list from the previous row.
This requires the use of a couple of Table Calculations. The first one is Index(). This can be used to number the rows in a table. I typically create a calculated field to store this.
Index
INDEX()
Set it to be discrete rather than continuous, and add the field to the Rows.
By default, the index is basically displaying a number per row, starting from 1 to however many rows are being displayed. This is because the table calculation has been set to compute Table Down.
We actually want the calculation to restart at 1 when the Member Name changes, ie for each Member Name.
Change this by clicking the ‘carrot’ / arrow on the Index pill and set to Compute Using -> Health Check Name
Next we need to create new calculated field that will build up the string
Health Check Name List
IF [Index]=1 THEN ATTR([Health Check Name]) ELSE PREVIOUS_VALUE(ATTR([Health Check Name])) + ‘, ‘ + ATTR([Health Check Name]) END
If we’re the first row, then store the name of the current row, else get the value from the previous row and concatenate to the current value, separating with a ‘,’.
Add this field to the Text shelf, and verify the table calculation is set to Compute by Health Check Name
You can see how the list is building up for each row, so by the time you get to the last row for the Member Name you’ve got the complete list.
Restrict to 1 row per Member Name
The last row for each Member Name is ultimately what we want. To identify this, I need another table calculation
Size
SIZE()
This does exactly what it ‘says on the tin’; returns the number of rows being displayed, and like INDEX() it can be computed over different partitions in the table.
Change it to be discrete and add to Rows, once again setting it to Compute By -> Health Check Name
Size is displaying the total number of rows associated to each Member Name.
To reduce the data to 1 row per Member Name, we just need the following:
Index = Size?
Index = Size
This returns true if the values match, so add this to the Filter shelf and set to True, and hey presto! 1 row per Member Name.
You can now start adding all the other fields to the output and remove Index & Size from the rows.
You will need to ensure Health Check Name remains on the canvas though, as otherwise the table calculations will break. You can either hide it from displaying as a column, by unchecking Show Header, or move it to the Detail shelf.
To ‘label’ the concatenated string field, so it ‘looks’ like just another regular column, I type the text directly into the Columns shelf
This gives us a ‘double’ column heading, and we can simply remove one
Min # Healthchecks filter
This is achieved by adding Size to the Filter shelf, but to get a range slider, the field needs to be a green continuous pill rather than blue.
List Must Contain filter
Right click on the Health Check Name field and Create -> Parameter
The Edit Parameter dialog will open with the list of distinct values pre-populated. Add the value ‘All’ to the bottom.
This parameter will be presented on the dashboard to allow user selection. But as it stands it won’t do anything. We need another field to read in the value selected in the parameter and filter the rows.
FILTER: Health Check Names
CONTAINS([Health Check Name List],[Health Check Name Parameter]) OR [Health Check Name Parameter]=’All’
This returns True if ‘All’ is selected or the concatenated list we built, contains the value from the parameter.
Add this to the Filter shelf, and set to True.
Note throughout this challenge, all the table calculations must be set to compute by Health Check Name. This includes Nested Calculations
It’s worth double checking, and if you do alter them, recheck the filter value is selected to True and hasn’t reset itself.
And that’s the core of the tricky stuff all sorted. The additional filters need adding for Physician & Age Bracket, formatting applied and then all displayed nicely on a dashboard.
Once I’d got all this I saved my work, and then took off my data source filter, keeping my fingers crossed it didn’t all grind to a halt… it didn’t, but it wasn’t the quickest either.
