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.
So after Ann’s gentle workout for week 6, newly crowed Tableau Zen Master Lorna, hit us with this challenge, and I confess, I struggled. The thought of then having to write this blog about it even brought a little tear to my eye 😦
But here I am, and I will do my best, but I can’t promise I understood everything that went on in this. I truly am amazed at times how some people manage to be so creative and bend Tableau to their will. It really is like #TableauBlackMagic at times!
So I read the challenge through multiple times, played around with Lorna’s published viz, stared at the screen blankly for some time…. I found the University Planning Dashboard viz by Ryan Lowers that Lorna had referenced in the challenge as her inspiration (she’d linked to it from her published viz). I played around with that a bit, although that took a while for me to get my head round too.
I also did a google search and came across Jonathan Drummey‘s blog post : Parameter Actions: Using a parameter as a data source. This provided a workbook and some step by step instructions, so I used this as my starting point. I downloaded the workbook, copied across the fields he suggested and tried to apply his instructions to Lorna’s challenge. But after a couple of hours, it felt as if I was making little progress. I couldn’t figure out whether I needed 2 or 4 parameters to store the ‘list’ data source variables (one each to store the list of selected categories for forecast, the list of selected categories for target, the list of selected forecast values, and the list of selected target values, or one each to store the list of selected categories and forecast values combined, and selected categories and target values combined). Suffice to say I tried all combos, using a dashboard to show me what was being populated on click into all the various fields/parameters I’d built. But it just wasn’t giving me exactly what I needed.
I downloaded the University Planning Dashboard and tried to understand what that was doing. And finally I shrugged my shoulders, and admitted defeat and cracked open Lorna’s solution. When I finally get to this point in a challenge, I try just to ‘have a peak’, and not simply follow verbatim what’s in the solution. I gleaned that I did need only 2 parameters, and that what I had been doing with my attempts with Jonathan’s example was pretty close. It made me feel a bit better with myself.
How things then transpired after that I can’t really recall – it was still a lot of trial and error but I finally got something that gave me the Sales Forecast data and associated select & reset functionality (by this time I’d probably spent 4 hours or so on this over a couple of evenings). Once I’d cracked that, the target was relatively straight forward, so by the time I’d finished on the 2nd day, I had a dashboard that allowed the selections/resets and simply presented the data in a table on screen. I chose to keep that version as part of my published solution, just for future reference (see here). I then finished off the next day, building the main viz.
What follows now, is just an account of the fields etc I used to build my solution. So let’s get going….
Building the Sales Forecast Selector
I’m going to start by focusing on building the left hand side of the viz, setting and resetting the Sales Forecast values for each Category.
We need 2 main parameters to start with:
Forecast Param
An integer parameter defaulted to 70,000. This is the parameter that stores the value of the forecast to set.
Forecast List
A string parameter defaulted to empty. This is the parameter which will ‘build up’ on selection of a category, to store a delimited list of category + forecast values – ie the data source parameter.
Oh, and I also used a 3rd parameter, Delimiter, which is just a string parameter storing a :
The delimiter needs to be a distinct character that mustn’t exist in the fields being used. The Category field nor the Forecast Param field will contain a ‘:’, so that’s fine. But any other unused character would work just as well. Having this field as a parameter isn’t ultimately necessary, but it makes it easy to change the delimiter to use, if the chosen value doesn’t end up being suitable. It was also a field used in Jonathan Drummey’s solution I’d based my initial attempts on.
Now we need to build the viz to work as the category selector.
I simply put Category on the Rows shelf, sorting the pill by SUM(Sales) descending and set the Mark Type to circle. Oh – and I set a Data Source Filter to set the Order Date just to the year 2019.
I also needed the following
something to colour the circles based on whether the Category was selected or not
something to use to help ‘build up’ the List parameter ‘data source’
something to return the forecast value that had been selected against the specific Category
Category Exists in Forecast List
CONTAINS([Forecast List], [Category])
If the Category exists within the Forecast List string of text, this field will return true, and indicates the Category has been ‘selected’. This field is added to the Colour shelf, and the colour needs to be adjusted once parameter action has been applied to distinguish between true & false.
Add to Forecast List
if [Forecast Param]<>0 THEN [Forecast List] + [Category] + ‘_’ + STR([Forecast Param]) + [Delimiter] ELSE ” END
If the entered Forecast value isn’t 0, then append <Category>_<Forecast Value>: to the Forecast List parameter. Eg if the Sales Forecast value is $50,000 and Technology is selected, then Technology_50000: is added to the existing Forecast List parameter, which has started as blank.
If the Sales Forecast value is then changed to $10,000 say, and Office Supplies is selected, then the Forecast List parameter will become
Technology_50000:Office Supplies_10000:
This Append To Forecast List calculated field is used in conjunction with the Forecast List parameter within a Parameter Action on the dashboard to make all the ‘magic’ happen. The Append To Forecast List field must be in the view to be available to the parameter action, so it is added to the Detail shelf.
When a circle is selected the Append To Forecast List field is used to ‘set’ the Forecast List parameter, subsequently building up a string of Category_Value pairs.
Finally, on hover, the Category and the value of the selected sales forecast at the time must be visible on the Tooltip. To get the value at the point of selection, which isn’t necessarily the latest value visible in the Sales Forecast parameter displayed on screen, the following field is required:
Current FC Value
INT(if contains([Forecast List],[Category]) then REGEXP_EXTRACT([Forecast List],[Category]+”_(-?\d+)”) end)
This manages to pull out the number associated with the Category, so in the above example, would return 50000 for Technology and 10000 for Office Supplies.
This field has custom formatting applied : ▲”$”#,##0;▼”$”#,##0 and is added to the Tooltip shelf.
RegEx is a concept I have yet to really crack, so there is no way I’d have come up with the above on my own.I think it’s looking for the named Category followed by Underscore (_) followed by either 1 or no negative sign (-) followed by some numbers, and returns just the numeric part.
Finally, the circles shouldn’t be ‘highlighted’ when selected on the dashboard. To stop this from happening a calculated field of True containing the value True, and a field False containing the value False are required. These are both added to the Detail shelf, and a Filter Action is then required on the dashboard setting True = False. This is a technique that is now becoming a familiar one to use, having been used in earlier #WOW2020 challenges.
So my ‘selection’ sheet looks like
and when added to the dashboard, the parameter action looks like :
with the filter action looking like :
At this point, I’d suggest using a ‘test’ dashboard which contains the selection sheet, displays the Forecast List and Forecast Param, and has the dashboard actions described above, applied to get an idea of what’s going on when a circle is selected, and the values of the Forecast Param changed.
The final part to this set up, is the ‘reset’ button, which when clicked on, empties the Forecast List parameter.
Create a new sheet, change the Mark Type to Text, and on the Text shelf add the string ‘↺’. I simply typed this ‘into’ a pill, but you could create a calculated field to store the ‘image’, which isn’t actually an image, but a special string character, that I got off my favourite ‘go to’ unicode characters website.
You then need a calculated field
Forecast List Reset
”
that just contains an empty string. This is added to the Detail shelf.
Put this sheet on the ‘test’ dashboard, and create another parameter action
This takes the value out of the Forecast List Reset field and sets the Forecast List parameter, subsequently resetting the list to an empty string on click.
Verify this is all working as expected.
Building the Sales Target Selector
Subject to Sales Forecast selector working as expected, then apply exactly the same principles to create the Target selection sheet and associated parameters.
The only slight difference with the fields used in the Target selection is:
Add to Target List
if [Target Param]>0 THEN [Target List] + [Category] + ‘_’ + STR([Target Param]) + [Delimiter] ELSE ” END
This just applies the addition to the list if the entered target is a +ve number (ie > 0), rather than not 0 as in the forecast selection.
The Target also needs to be displayed on the Tooltip, and this time there is a default target value that should be displayed, even when no selection has been made. For this I created
Target
IF ZN(MAX([Current Target Value])) = 0 THEN MIN(IF [Category]= ‘Furniture’ THEN 270000 ELSEIF [Category]= ‘Office Supplies’ THEN 260000 ELSEIF [Category]= ‘Technology’ THEN 250000 END) ELSE MAX([Current Target Value]) END
which was formatted to a currency of 0 decimal places, prefixed by $. This was added to the Tooltip shelf.
At this point, you should now have both the ‘selection sheets’ working on the dashboard, so we can now focus on building the main viz.
Building the Bar Chart
Rather than building the bar chart, I first decided to build a tabular view that simply presented on screen all the bits of data I needed for the bar chart, this being
Sales value per Category (simply SUM(Sales))
Sales Forecast value per Category (ie Sales + selected Forecast value)
Selected Sales Target value per Category (this is the Target field described above)
% Difference between Sales & Target
% Difference between Sales Forecast & Target
So I created the following additional calculated fields:
Forecast
SUM([Sales]) + MAX([Current FC Value])
formatted to currency prefixed with $ set to 0 dp.
Forecast vs Target Diff
([Forecast]-[Target])/[Target]
custom formatted to ▲0%; ▼0%
Sales vs Target Diff
(SUM([Sales])-[Target])/[Target]
also custom formatted to ▲0%; ▼0%
Adding the table to the ‘test’ dashboard allows you to sense check everything is behaving as expected
Now its just a case of shifting the various pills around to get the desired view. Ensure at least one Sales Forecast Category has been selected, to make it easier to ‘see’ what you’re building.
Lorna stated the target should be displayed as a Gantt mark type, with the sales and the forecast displayed as bars. This means a dual axis chart is required, with sales & forecast on one axis and target on the other.
To get Sales and Forecast onto the same axis, we need to add Category to the Rows (sorted by Sales desc) and Measure Values to the Columns, filtering to only the two measures we need.
Set the Mark Type to bar, and add Measure Names to both the Colour and the Size shelf.
Adjust colours and sizes to suit.
You might have something like
where the measures are ‘stacked’, so the bar is the length of the Sales then the length of the Forecast. We don’t want this, so need to set Stack Marks to Off (Analysis menu -> Stack Marks -> Off).
Add all the necessary fields to the Label shelf and format accordingly (you may need to widen the rows to make the labels show against each row).
Note – in my solution I created some fields to make the opening & closing bracket around the Forecast v Target Diff value only show when a Forecast had been selected, however in writing this blog, I realise it was simpler just to change the formatting of the Forecast v Target Diff to add the brackets around the number. The custom formatting was changed to : (▲0%); (▼0%)
Adjust the Tooltip to suit too.
Now add Target to Columns alongside Measure Values. Set to Dual Axis and Synchronise the axis. Reset the Measure Values mark type back to bar if needed, and set the Target mark type to Gantt.
Remove Measure Names from the Colour and Size shelf of the Target marks card. Untick Show Mark Labels too. Adjust the colour of the mark to suit, and you should pretty much be there now…
Tidy up the final bits of formatting, removing/hiding the various axis, labels, gridlines etc etc.
When this is all put together on the dashboard, you might need to fiddle about a bit with layout containers to get the bar chart lined up with the Selector views.
And with that I’m done! My published version is here, along with the ‘check’ dashboard I used to sense check what was going on, as I’m sure if I ever looked at my solution again, I’d struggle to understand immediately 🙂
Once again, I just want to acknowledge those that manage to create this magic with Tableau. I applaud you!
Ann set the challenge this week, which for seasonsed #WoW participants like myself, was relatively straightforward. However it encapsulates a couple of concepts that I’ve only learnt from doing #WoW challenges over the years, so if you’re a relatively newbie, this is a great challenge for giving you a firm foundation for the future.
Determining the dates to compare
To start off, Ann stated that we needed to compare Oct 2019 with Nov 2019 but not to hard code the dates. I therefore used a parameter to drive this, which I created by first creating a new calculated field
Order Date MY
DATE(DATETRUNC(‘month’,[Order Date]))
Against every row, this sets the date to be 1st of the month.
I then created a parameter Order Date MY Parameter by right-clicking on the Order Date MY field and selecting Create -> Parameter. This has the effect of automatically populating the parameter with all the month/year options. I also set the format of the date to be March 2001 (ie month year), and the default value to be November 2019
I then created the following fields :
Current Month
[Order Date MY Parameter]
Previous Month
DATE(DATEADD(‘month’,-1,[Current Month]))
These two fields would ultimately drive the rest of the calculations I needed.
Sales by Sub-Category KPI
Ann is a fan of capital letters, so first up, you’ll need to create a field to store the Sub-Category in uppercase :
Sub-Cat UPPER
UPPER([Sub-Category])
Now for each KPI column, we need to determine
Sales for Current Month
Sales for Previous Month
% difference between the two
An indicator whether the % difference is less than 100% or not
Sales Current Month
IF [Order Date MY] = [Current Month] THEN [Sales] END
Sales Previous Month
IF [Order Date MY] = [Previous Month] THEN [Sales] END
% Sales
SUM([Sales Current Month])/SUM([Sales Previous Month])
formatted to a percentage with 0 dp
Sales Indicator
IF [% Sales] < 1 THEN 0 ELSE 1 END
You might initially think to set this to a string of ‘under’ and ‘over’ or similar, but setting to 1 and 0 will be useful later on when we need to calculate the Overall KPI.
Build the Viz
To create the ‘cell block column’ layout, we need to make use of an old friend Min(1) to create an axis, which allows us a bit more flexibility. This is the first of the techniques I was first introduced to via #WoW, and is a valuable concept to have in your arsenal.
You can already see the makings of the column just with these 2 pills. We then need to apply the following
Set mark type to be Bar.
Add Sales Indicator to Colour shelf, and adjust colours to match
Add Current Month, Previous Month, Sales Current Month, Sales Previous Month and % Sales to Tooltip and format tooltip to match the requirement.
Edit Axis and fix axis to start at 0 and end at 1, remove the title, and set tick marks to None
Duplicate the MIN(1) pill (click the pill, hold down Ctrl and drag to sit next to it). Make this pill dual axis and sync
Edit the top axis and change the title to SALES, and again set tick marks to None.
Orders by Sub Category KPI
As with the Sales KPI, we again need to know 4 things for this KPI
Number of orders in current month
Number of orders in previous month
% difference between the two
an indicator whether the difference is less than 100% or not
Orders Current Month
COUNTD(IF [Order Date MY] = [Current Month] THEN [Order ID] END)
Orders Previous Month
COUNTD(IF [Order Date MY] = [Previous Month] THEN [Order ID] END)
% Orders
[Orders Current Month]/[Orders Previous Month]
formatted to percentage to 1 dp
Orders Indicator
IF [% Orders] < 1 THEN 0 ELSE 1 END
The viz is then built exactly as above, adding further MIN(1) pills to the Columns shelf.
Units by Sub-Category KPI
Once again 4 fields are required, which are pretty identical to the ones defined for Sales above, but where any reference to Sales is replaced by Quantity.
Then build the column using further MIN(1) pills again.
Overall KPI
The overall KPI provides a % score based on the % of the 3 measures that are on target (ie 100% or more), along with an indicator (red circle), if the overall KPI < 100%.
This is where using 1 & 0 as the indicator value rather than a string value becomes handy. We can simply sum the three outputs together, giving an output of 0, 1,2 or 3.
% Overall Score
[Overall Score]/3
formatting to percentage set to 0 dp.
Overall Indicator
IF [% Overall Score] < 1 THEN ‘●’ END
This is where the second technique that was introduced to me originally by #WoW comes in : using geometric shapes in Text fields.
I use this concept A LOT in my work dashboards. I use this website to copy the shapes from.
Building the Viz
This time, we need to use Min(0.5), and set the Mark type to Text, adding Overall Indicator and % Overall Score to the Text shelf. The text is the formatted so the circle indicator is coloured red.
Using the font colour as a way of ‘colouring shapes’ is another concept I first discovered during #WoW, coupled with the fact that the field only displays a circle based on meeting some conditional logic. So while the Text field looks like a red piece of text (ie the circle) is visible, it won’t in fact always be there.
The axis is again fixed from 0 to 1, so this makes the text centred, and the Toolip is formatted to match requirements.
A dual axis is again required, but this time, the mark of the 2nd axis should be set to be a circle, with the size set to be the smallest possible. The colour should also be adjusted to 0 transparency (basically this mark needs to disappear). There should be no pills on the marks card of the secondary Min(0.5) axis.
And at this point, the viz should be pretty much complete (subject to any further formatting of fonts and row/column/grid lines – you might want to format the column lines to be white to break up the display, and adjust the size of the bars).
A beautiful looking bump chart for week 5 of #WOW2020; a challenge set by Luke inspired by a conversation with Zach Bowders.
Immediately looking at the chart, I knew it was highly likely to involve dual axis (at least for the ‘standard’ challenge).
One axis to represent the line; the other to represent the blocks. The table calculation of Rank was also going to be involved, since the positioning was all based on the Sales rank (which was assumed rather than explicitly stated).
Basic Data Structure
First up I just wanted to sense check I had made the correct assumption in respect of ranking the sales, so using the 2019.4 version of Superstore data, I built a very basic view; Region on Rows, MONTH(Order Date) on Columns and SUM(Sales) on Text. Applying the Quick Table Calculation of Rank to the SUM(Sales) pill, and ensuring it was computing for each Month, changed the displayed output to the rank expected.
With all that clarified, I could focus on building the viz. Let’s start with the ‘standard’ view.
Standard View
Having built my ‘basic data’ tabular structure, I started by duplicating this view, and then applying the following changes :
Moved the SUM(Sales) rank pill from Text to Rows
Moved Region to Colour
Editing the Rank of Sales axis to reverse it, and adjusting the colours associated to each Region and we have the Line chart completed. At this point I chose to rename the Region field to COLOUR:Region (Line), as I figured I’d need another instance of Region later to colour the blocks (which were different colours).
Dual Axis
Duplicate the SUM(Sales) Rank pill (by clicking on the pill, and holding down Ctrl) and place alongside the existing pill on the Rows shelf.
Change the mark type of the second instance to be Square.
Duplicate the Colour:Region (Line) field and rename to Colour:Region(Block), and add this to the Colour shelf of the Square mark type.
At this point you’ll end up with a single row of blocks. This is because the Rank table calculation of the 2nd mark type was based on the Colour:Region (Line) which no longer exists on this 2nd instance, and it needs to be changed to Colour:Region (Block).
Adjust the colours to suit
Make the chart Dual Axis, Synchronise the 2nd axis, and Move Marks to Back of that 2nd axis (right click on the axis to find this option).
Displaying the Labels
The challenge requires the rank position to be displayed on the ‘January’ blocks and the Region to be displayed on the ‘December’ blocks, BUT to only use one calculation on the Label shelf. I achieved this by creating the following calculated fields:
Label:Rank
CASE RANK(SUM([Sales])) WHEN 1 THEN ‘1st’ WHEN 2 THEN ‘2nd’ WHEN 3 THEN ‘3rd’ WHEN 4 THEN ‘4th’ END
which simply translates the rank number into its string form, and then
Label:To Display
CASE MIN(MONTH([Order Date])) WHEN 1 THEN [LABEL:Rank] WHEN 12 THEN ATTR([Region]) END
When it’s January, then use the Rank Label we created, but when its 12, use the Region field (or equivalent if its been renamed).
On face value, this may look confusing – how can the MIN return multiple values? But as this is Table Calculation field (as it references the Label:Rank field which is based on the Sales rank), and it’s computing per month, the month is changing. You’d get the same result if you change MIN to MAX. If the calculation was considering the whole table, this logic wouldn’t work.
Add this to the Label shelf of either mark, set the table calculation to be applied for each month (as above), and format the text to be middle centre.
Hide both the axis headers, remove the column/row lines and any gridlines, change the format of the date (via format -> axis) to be abbreviated, and ‘hide field labels for columns’. Remove tooltips from both marks.
At this point, you may think the gapping between the squares is too big in some places, too small in others. Don’t yet try to adjust the sizing, until you’ve got the chart on the dashboard, as how the display looks on the sheet, doesn’t always reflect the dashboard.
I created the dashboard to the 700×350 size specified, added the viz, and then increased the Size of the Square mark type. This is what the sheet looked like
But this is the dashboard with the legends and sheet title removed :
With a few adjustments, this is the core of the ‘standard’ version.
Advanced Version
The advanced requirement was to not use the Square or Shape mark type.
I started by duplicating the version I’d already built, then just tried a few mark types I thought might help to see if I got any inspiration – bar and gantt, but neither clicked.
1st Attempt
NOTE – detailing this for information to understand my thought process, but this doesn’t ultimately give the desired outcome, so just read only 🙂
Then I had a moment of inspiration (or thought I did) and decided to try using Text mark type. I used my ‘go to’ geometic shapes website, and copied the ‘square’ into a calculated field, which I added to the Text shelf, and increased the Size as large as possible
This gave me something very close
But I needed one of the axis to be shifted slightly, so used an offset parameter to move one of the axis around. Again it took a bit of shuffling to get it looking ok on the dashboard, but once satisfied I published to Tableau Public, only to find that rendered everything differently 😦
So back to the drawing board…
2nd Attempt (that worked)
In working through the above, I’d expanded on some concepts I hadn’t done when flicking through the standard mark types, and Gantt type was the mark I needed. Ultimately this wasn’t something that I managed without a bit of trial and error. I’m just going to document what I ultimately ended up with.
Take the ‘standard’ view of the chart you’ve hopefully already built. Change the mark type of the 2nd axis to Gantt rather than Square.
Create a new calculated field
Size
0.9
and add this to the Size shelf of the Gantt mark, changing the aggregation to MIN rather than SUM. Setting to 1 will make the marks butt up to each other.
As both axis were identical with the results of 1-4., the line points are positioned at the base of the Gantt. I needed to adjust the line axis to be at say 1.5, 2.5 etc, although exactly what the offset needed to be I wasn’t sure, so I created a Parameter called Offset
I then changed the first SUM(Sales) rank pill, to add Offset, simply by ‘typing into’ the pill
I found a value of 0.4 worked for me, but added the Offset parameter to the sheet so I could adjust up or down as needed
Removing the axis headers as before and adding to the dashboard may have required more minor adjustments, but on publication, this version didn’t go all wonky.
Donna + DataViz 