Instructions were provided to get the data from this site , select ‘Share & Export’ then ‘Export as CSV’. I couldn’t find an actual ‘export as CSV’ option. In stead I selected the ‘Get table as CSV (for Excel)’ option under the ‘Share & Export’ menu, and then copied the text displayed into notepad. I then save this as a csv file.
Building the Calculated Fields
There aren’t that many fields needed for this viz.
Firstly, we need to organise the years into rows on the viz, based on the decade
Decade
STR(FLOOR([Year]/10) * 10) + ‘s’
This takes the Year eg 1968, divides by 10 to get 196.8, applies the FLOOR function which rounds down to the nearest whole number ie 196, then multiples that by 10 to get 1960.
We also need to a counter for each year in the decade to organise the data into the columns. I just used
Index
INDEX()
Let’s see what we’ve got so far
Add Year (as a blue discrete field)and Decade to Rows. Then add Index to Rows as a blue discrete field. Adjust the table calculation settings, so the Index is computing by Year only.
We also need to convert the Time/9I field into a duration in minutes
take the hour part of the date field and multiply by 60 to get the total number of minutes for the hours the game took, then add on the minute part of the date field.
Add this field to the Text shelf, and additionally add Year to the Filter shelf, and set so that it is filtered to be at least 1960.
This gives us the measure we’ll be plotting on the bar chart, but we also need to plot a reference line based on the time for the year 2023.
2023 is the last value in our data, so we can get the value against that year by the following
Latest Duration
WINDOW_MAX(IF LAST()=0 THEN SUM([Duration (mins)]) END)
As 2023 is the last value, then the Duration(mins) value is returned for this point only – the value is null/empty for all other years. The Window_Max function then ‘spreads’ that value across every other row in the data set.
Add this onto the sheet, and verify the table calculation is set to compute by both Year and Decade
The final piece of information we need is to determine whether the duration for each year is bigger or smaller than the latest years’ value
Duration > Latest
SUM([Duration (mins)]) >= [Latest Duration]
Add this to the Rows and the values will be True or False depending on how the data compares.
Building the Viz
On a new sheet, add Decade to Rows, Index to Columns and Year to Detail (discrete blue pill). Adjust the table calculation setting of Index to compute by Year only. Add Year to filter and set to at least 1960.
Add Duration(mins) to Rows and Duration > Latest to Colour. Modify the table calculation setting so it is computing by both Year and Decade then adjust the colour accordingly.
Add Time/9I and Year to the Label shelf. Modify the Time/9I field so that it is using Exact Date and set to be a discrete Attribute rather than a measure.
Also, apply a custom date format to the Time/9I field so that is displays as h:nn
Adjust the text on the Label shelf, so that the Year is above the Time/9I, the font colour is black and the label is aligned bottom centre
Add Latest Duration to the Detail shelf, and adjust the table calculation setting so that it is computing by both the Year and the Decade.
Add a Reference Line to the Duration(mins) axis that works by pane and references the Latest Duration average value. Manually set the Label to the text to 2:38 in 2023. Don’t show a tooltip.
The format the reference line, so the text is aligned top right.
To add a bit of ‘breathing space’ between each row, add another reference line. This time set it to use a Distribution that is 150% of the average Latest Duration. Don’t show any labels, tooltips or lines or fill.
Finalise the viz by hiding the Duration(mins) axis and the Index headings (uncheck show header). Remove all gridlines, and column dividers. Hide the Decade column label (right click label and Hide Field labels for Rows). Click the Tooltip shelf and uncheck Show Tooltips.
Add the viz to a dashboard, add title and publish. My published viz is here.
Lorna used this week’s challenge to showcase a new feature in Tableau v2023.1 – dynamic axis titles. As you can expect, you’ll therefore need this version of Desktop (or later if you’re reading this in the future ;-)) to complete the challenge.
Modelling the data
Download the file Lorna provided and connect to the 2023 sheet. Lorna hinted that a pivot would help, so in the data source canvas, multi-select all the measures (ctrl-click each column – there are several) then right-click and Pivot.
Rename Pivot Field Names to Measure and rename Pivot Field Values to Value.
Building the Basic Scatter Plot
We need 2 parameters to control the selection of the measures we want to display in the scatter plot. Right click on Measure > Create > Parameter
pMeasure1
string parameter defaulted to Metres, and all the possible other values should be listed
Repeat the steps again to create pMeasure2 which is defaulted to Tackles
To determine the value to plot on the axes based on the selections from the parameters we need
X-Axis Value
IF [pMeasure1] = [Measure] THEN IF [Value] <> 0 THEN [Value] END END
Note – the additional nested IF was added as I discovered while playing with the Lorna’s solution that marks didn’t display when the value was zero.
Similarly we need
Y-Axis Value
IF [pMeasure2] = [Measure] THEN IF [Value] <> 0 THEN [Value]END END
Add X-Axis to Columns, Y-Axis to Rows, Team Name to Rows and Name to Detail. This will give a basic scatter plot.
Change the mark type to circle, adjust the colour, and reduce the opacity to around 70%. Add a grey border to the circles.
The size is based on the number of games the player has played, so we need
Count Games
COUNTD([Game ID])
Add this to the Size shelf.
Adjust the Tooltip so it references the parameters and the other relevant fields
To change the axis titles, edit the x-axis (right click axis > edit axis) and from the menu arrow next to the word ‘custom’, select the pMeasure1 option.
Repeat the same for the y-axis, but select pMeasure2 instead.
Making the small multiples
For this we need to define which row and column each Team Name should sit in. As we’ve only got 12 teams to work with, and that number is static, and we know we’re working with a 3×4 grid, I’m going to ‘hardcode’ this a bit rather than use more dynamic calculations.
To see what’s going on, on a new sheet add Team Name to Rows. Then create a new field
INDEX
INDEX()
and add this to Rows and convert to discrete. It should provider a counter from 1 to 12 for each Team Name.
Based off this INDEX value we’ll work out which row and colum each team will sit.
Column
(INDEX()-1) % 3
Row
IF [INDEX]<=3 THEN 1 ELSEIF [INDEX]<=6 THEN 2 ELSEIF [INDEX]<=9 THEN 3 ELSE 4 END
Add these to the table as blue discrete pills
Back onto the scatter plot sheet, add Row to Rows as a blue discrete pill, add Column to Columns as a discrete pill, and move Team Name to Detail. Modify the table calculation settings of both the Row and the Column pill so that the calculation is computed using Team Name and Name (in that order) and at the level of Team Name
Hide the Column and Row pills (uncheck Show Header)
Adding the Team Name title
Create a new field
Ref Line
WINDOW_MAX(SUM([Y-Axis Value])) * 1.1
and add this to the Rows. This will create a second marks card. Set the table calcuation of the Ref Line pill to compute using Name and Team Name (in that order).
On the Ref Line marks card, remove the pill from the Size shelf, change the mark type to gantt bar, and reduce the size to the smallest possible, set the opacity to 0%, and border to none.
Add Team Name to the Label shelf, then set to label min/max values, by the X-Axis Value field and Label Minimum value only.
Each team name should only be displayed once. Edit the text of the label and add a few spaces to shift the label across and align it better.
Edit the Tooltip of this marks card, and delete all the text. Now make the chart dual axis and synchronise the axis. Then remove Measure Names from the All marks card, and hide the right hand axis.
Finally remove the 0 lines from displaying, and hide the nulls indicator (right click). Add the chart to a dashboard, and position the parameters as floating objects within the text of the title. I just used spaces within the text to leave room for where I wanted to place the parameterss.
For this week’s challenge, Kyle got us to look at dashboard layout, specially using containers to arrange the charts and KPIs. He added in a sprinkling of interactivity to make the challenge more complete.
The charts aren’t overly complex, so I won’t go into too much detail on building them all out. I used the latest version of Superstore, v2023.1.
Building the KPIs
When I first did this, I built the KPIs on a single sheet, then realised that wouldn’t work to get the layout required, so I ended up with 3 sheets.
Rename Sales to SALES and format to $ with 0 dp.
Add Measure Names to Filter and select SALES only.
Add Measure Names and Measure Values to the Text shelf.
Align centrally and format the text (I used font size 12pt and 20pt).
Add Category to the Filter shelf, and select all. Set the filter to apply to worksheets > all using this data source
Add Order Date to Filter and select Range of Dates. The Order Date pill will be ‘green’. Click on the context menu of the pill and select the Month (May 2015) option, so the range of dates will change at monthly intervals rather than daily. Set the filter to apply to worksheets > all using this data source
Create a field called True containing the value TRUE and False containing the value FALSE and add both these fields to the Detail shelf. These will be needed later to stop the sheet from remaining highlighted on selection.
Stop the Tooltip from displaying.
Name the sheet Sales
Repeat the steps for the Profit Ratio measure – if this doesn’t exist, create the field
PROFIT RATIO
SUM([Profit])/SUM([Sales])
and format this to % with 1 dp.
For the Orders measure create
ORDERS
COUNTD([Order ID])
If need be, you can simply duplicate the Sales sheet and just change the Measure Names filter to the appropriate measure.
Creating the Line Chart
Create a parameter to store the name of the selected measure.
pSelectedKPI
string parameter defaulted to the word ‘ORDERS’
Create a field to store the measure to display based on the value of the parameter
Measure to Display
CASE [pSelectedKPI] WHEN ‘ORDERS’ THEN [ORDERS] WHEN ‘SALES’ THEN SUM([SALES]) WHEN ‘PROFIT RATIO’ THEN [PROFIT RATIO] END
On a new sheet, add Order Date to Columns and set to be a green (continuous) month level and Measure to Display to Rows.
I wanted to make my tooltips and labels reflect the measure selected (this isn’t actually part of the challenge). I created
Label – Orders
IF [pSelectedKPI] = ‘ORDERS’ THEN [ORDERS] END
format this to a number with 0 dp.
Label – Profit Ratio
IF [pSelectedKPI] = ‘PROFIT RATIO’ THEN [PROFIT RATIO] END
format this to % with 1 dp
Label – Sales
IF [pSelectedKPI] = ‘SALES’ THEN SUM([SALES]) END
format this to $ with 0 dp.
Add all 3 fields to the Tooltip shelf.
Also create
Chart Label
PROPER([pSelectedKPI])
this is a new function introduced in v2023.1 and will convert ‘ORDERS’ to ‘Orders’ and ‘PROFIT RATIO’ to ‘Profit Ratio’ etc. Add this to Tooltip too.
Modify the Tooltip as below – the 3 Label fields should be directly side by side with no spacing. Only one field will have a value at any time.
Remove the titles from the axes, and remove all gridlines. Name the sheet Line.
Building the Bar Chart
Add Category to Rows and Measure to Display to Columns. Sort descending. Add the 3 Label fields to the Label shelf, and arrange side by side.
Add Chart Label to Tooltip and adjust the tooltip.
Hide the axes, remove all gridlines/axis rulers etc and hide the Category label. Adjust the formatting of the fonts as required. Name the sheet Bar.
Building the dashboard
Describing using layout containers can be quite tricky – objects move around as you place things in. I’m going to do my best to describe the set up/structure I have and hope that it gets you what you need.
My preference is to always start with a floating container sized as per the dashboard. I then add tiled objects into that. I also have a habit of trying to rename my containers in the navigation layout pane to help me find the right section.
So let’s start. Create a new dashboard and set it to to 1200 x 900px.
Click the Floating button at the bottom of the left hand pane, and add a Horizontal container. Set the x & y position to be 0,0 and the width to 1200px and height to 900px. Rename the container to Base.
From the Objects list on the Dashboard pane, click Tiled and add a Text object. Enter the text for the dashboard title. Add a blank object beneath the text object.
When working with containers, it’s always good to add blank objects as a ‘starting point’ These all then get removed.
Now add a vertical container between the Title and Blank object. Name this container Main Body. Add a blank object into that container.
Add a vertical container to the left of the blank object in the Main Body container. Name this container Left Nav. Add a Text object into the Left Nav container and enter the instructional text. Add a blank object above the instructional text.
Add another vertical container to the right of the Left Nav container within the Main Body container. Add the Bar and the Line chart into this container, one above the other. Call this container Charts.
At this point a Tiled container object will have been automatically added containing the parameter.
Leave this for now – we’ll address this shortly.
We can remove some of the blanks now too. Remove the blank within the Main Body container, that is to the right of the Charts container. You can select the object on the item hierarchy layout, right click and remove from Dashboard.
You can also remove the blank object at the bottom of the Base container, below the Main Body container. Your item hierarchy should look something like
Now add another Horizontal container to the charts container, above the bar chart. Add the 3 KPI sheets into this container, position side by side. Name the container KPIs. Select the KPIs container, and use the context menu to Distribute Contents Evenly.
If they haven’t already appeared, the select one of the charts/KPIs and from the context menu select Filters > Category and then Filters > Month of Order Date to get the filter controls visible on the dashboard. They should appear on the right hand side, within that Tiled container (underneath the viz).
From the item hierarchy section, expand the Tiled container until you find the controls listed (see how many containers that got automatically added!).
Select the Category filter (easiest to do this by clicking on the object in the item hierarchy), and then move that object and position it above the blank object in the Left Nav container. Then delete the blank object. Select the Month of Order Date filer object and do the same.
Now we’ve got everything we want , we can remove the Tiled container and all of the objects it contains from the dashboard. Just right click on the first Tiled container in the item hierarchy and Remove From Dashboard (say yes/ok to any prompt that appears). You should have something that looks a bit like this – not the item hierarchy layout.
Now you’ve got everything needed on the dashboard in the right containers, we need to tidy it all up.
Fix the width of the Left Nav container to around 205 px.
Change the Category filter to single value dropdown, defaulted to All
Fix the height of the KPIs container to around 175 px
Remove the titles from all the KPI objects, and ensure all set to fit entire view
Amend the titles of the bar and line charts to reference the Chart Label field.
Fix the height of the bar chart to around 340px and set to fit entire view.
Set the background colour of the whole dashboard to grey (Format menu -> Dashboard – Dashboard Shading)
Set the background colour of the bar and line chart to White and add a grey border (slightly darker than the background colour)
Add borders round the 3 KPI charts too.
Set padding around all the objects – I tend to use both inner and outer padding. The key is consistency to ensure the spaces between the objects are the same. I typically start with 10 px outer padding all round, and then adjust as required. Sometimes you may add padding to the container and not to the objects themselves, other times you may set the container padding to 0 and apply to the objects, or a combination of both.
Adding the Interactivity
To set the category bar chart to work as a filter, simply select the object and from the context menu select use as filter. Then go to the Actions list (Dashboard > Actions) and edit the ‘Filter 1 (generated)’ action and rename it to something more useful eg Filter bu Category.
For the bar & line chart to update ‘on click’ of a KPI, add a parameter action
Select KPI
On selection of the Orders, Profit Ratio or Sales sheet, set the pSelected KPI parameter, passing the value from the Measure Names field. Keep current value when selection is cleared.
Finally to prevent the KPIs from remaining highlighted in blue on selection, add 3 filter actions (1 per KPI) set up as follows
Deselect Sales KPI
On selection of the Sales sheet on the dashboard, target the Sales sheet directly, and set the fields as Source = True to Target = False.
Finally, to collapse the left hand nav section, select the Left Nav container and from the context menu, select add Show/hide button. A X button will appear which is floating by default. Move this to where you choose (you might need to add some additional left padding to the title to make space.
Load the dashboard in presentation mode to easily test the hide/show functionality.
Once you’ve grasped the concept of containers, they really are the best way of controlling the behaviour and layout of objects on your dashboard. When I’m building something formal, I personally never want to have a Tiled container on my dashboard – this is an object that gets automatically added, and you can see from above, how many nested containers it ended up adding through a single action I took. If you’re not careful, you can end up with such a nest of containers, that it can get really hard to unpick.
This week’s #WOW challenge was set by me and born out of a client requirement to compare store locations via selections from a map. I adapted it to use our favourite Superstore dataset (v2022.4).
The core requirement is to be able to make a selection on the map and see how sales compare to the average of all the other states. There is more to this challenge, but it was too much for one week, so I’ve broken it into 2 parts. This is part 1. In a few weeks time, I’ll be building on this solution for a part 2.
Building the basic hex map
The requirements provide a link to here to get the relevant files needed to complete this challenge and build the hex map – this includes the 2022.4 version of Superstore, the Hex map template, the hexagon shape file and a transparent shape file (more on that one later).
Using the hex map template sheet provided, relate the Orders Superstore data to the hex map sheet, relating State/Province to State.
Then on a new sheet add Column to Column, Row to Row and State to Detail. Edit the Row axis, and reverse the scale.
Change the shape of the mark to be a hexagon (use the provided shape if need be and add to a custom shape palette), and increase the size of the marks. Add Sales to Colour and change to use the Grey sequential colour palette, and adjust the opacity to 80%. Add Country/Region to the Filter shelf and select United States. This will remove Alaska and Hawaii that don’t have any sales and aren’t in the Superstore data set.
Add Abbreviation to the Label shelf and align centrally, Adjust the font size if need be.
Identifying the selected state
We need to be able to capture the state that has been selected ‘on click’. This will be driven by a dashboard action.
When I first built this concept for a client, the natural first step was to utilise sets and set actions; that is capture the selected state in a set, and then colour the map, build the other charts and logic based on the existence in the set. However this method does caused some issues when I tried to prevent the highlighting later, so I chose to use a parameter and parameter actions instead.
Firstly we need a parameter that will be used to capture the state selected ‘on click’
pSelectedState
string parameter defaulted to <empty string>
We can then create
Is Selected State
[State] = [pSelectedState]
As we want to retain the colour by sales on the existing map, we need to make a dual axis. Show the pSelectedState parameter and type in ‘Florida’.
Add another instance of Column to Columns. Remove the Abbreviation label from the second marks card, and replace the Sales field on the Colour shelf with the Is Selected State field. Increase the opacity on this mark to 100% Set the colours as follows :
True- teal : #66b3c2
False- pale grey : #d3d3d3
Additionally, add Is Selected State to the Shape shelf of the second marks card. Set the True value to use the same hexagon shape, but set the False option to use a transparent shape (a transparent shape file is provided in the g-drive, and needs to be added as a custom shape).
This should make all the other states look like they disappear.
Make the chart dual axis, and synchronise the axis.
Format the Sales to be $ with 0dp, then add to the Tooltip shelf of the second marks card and adjust the tooltip. Remove all gridlines/row & column dividers and hide the axes.
Adding the Interactivity
Create a dashboard sized 1000 x 600.
Add a vertical container and add a text field to create the title. Below the title, add a horizontal container. Add the hexmap sheet into the horizontal container, then add a blank object to the right of it. Remove any legends etc that automatically get added. Set the width of the hexmap object to be fixed to 600 px
and set the height of the horizontal container the hex map sits within to 445px
Show the colour legend and set it to floating, and position bottom left.
Add a parameter action to set the selected state on click
Set Selected State
On selection of the Hexmap sheet, pass the State field into the pSelectedState parameter. Set the value to <empty string> when the selection is cleared.
If we click around on the dashboard page, we can see the colours being set, but everything is fading out on selection. To prevent this, create 2 calculated fields
True
TRUE
False
FALSE
Add these to the Detail shelf of the All Marks card of the hex map sheet. Then back on the dashboard, add a filter dashboard action
Deselect Map Marks
On select of the hex map object on the dashboard, target the hex map sheet itself, setting selected fields such that True = False. Show all values where selection cleared.
Now if we click on the dashboard, the shapes shouldn’t fade into the background. However we can’t ‘unselect’ the state and get back to its original state. This is because the filter action we added to stop the marks from fading actually unselected the mark, so when we click again, we’re not undoing any selection.
To resolve this, we need another calculated field.
State for Param
IF [State] = [pSelectedState] THEN ” ELSE [State] END
If the state being clicked is the one already captured in the parameter, then set the field to <empty string>, else set the fields to the state being clicked.
Add this to the Detail shelf of the All marks card on the hexmap sheet, and then update the Set Selected State parameter action to pass the State For Param field into the parameter instead of the original State field..
Now when the map is first loaded, the State For Param field contains the name of the state, so that is passed into the parameter on click. As the parameter now has a value, the State For Param field changes for the selected state to be <empty string>, so if the same state is clicked, <empty string> is then passed into the parameter and the view resets.
Building the calculations for the other charts
On a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter.
When a state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ .
State Label
IF [Is Selected State] THEN [State] ELSE ‘Other States (Avg)’ END
Now we need to display a different value for the sales measure depending on whether its the selected state or not.
Sales To Display
IF ATTR([Is Selected State]) THEN SUM([Sales]) ELSE SUM([Sales])/COUNTD([State]) END
If the state is the selected one, then use the sales, otherwise average the total sales over the number of states that make up the sales. Format this to $ with 0dp.
Note – in hindsight, this could have just been SUM([Sales])/COUNTD([State]) even for the single state, as since the count of state will be 1, this would just equate to the SUM([Sales]) itself.
Add this to the table, and remove State from the display
Building the bar chart
Duplicate this sheet (as we want to retain the filters), then move Sales To Display to Columns, and add Category to Rows in front of State Label. Add Is Selected State to the Colour shelf.
To ensure the Selected State is always listed first, even if alphabetically it comes after ‘Other States (Avg)’, add Is Selected State to the Rows shelf between Category and State Label. Manually sort it so True is always listed before False, then hide the column (uncheck show header).
Reduce the Size of the bars, remove gridlines and column dividers. Lighten the row dividers. Adjust font sizes. Hide the column labels (hide field labels for rows) and hide the axis.
Remove Sales from the Text shelf, and check Show mark labels instead. Update the Tooltip.
Building the line chart
On a new sheet add Country/Region to Filter and set to United States. Add Order Date to Columns and set to the continuous month level (eg May 2021). Add Sales To Display to Rows, State Label to Detail and Is Selected State to Colour. Manually move the values in the colour legend so that True is listed first. Adjust the tooltip
Remove the axis titles, adjust the axis fonts. Remove row/column dividers and zero lines and axis rulers.
Putting it all together
On the dashboard, add a vertical container between the hexmap and the blank object. Add the line chart and bar chart on top of each other. Remove the title for the bar chart, and update the title of the line chart to reference the pSelectedState parameter.
Remove the blank object to the right of the bar/line charts.
We need to control when the bar and line charts display, so we’ll use dynamic zone visibility for this, and for this we need another boolean field
Show Viz
{FIXED: MAX(IF [Is Selected State] THEN TRUE ELSE FALSE END)}
Is Selected State is a boolean field which essentially is 1 for True and 0 for False. If there is a state selected, the maximum value across all the data records, will be 1, so the field returns true, otherwise its 0, so false.
Use this to control visibility using value for the line and bar chart objects.
Make any further adjustments to the layout required -the size of the hex shapes may need tweaking for example. Then interact with the viz to check all is working as expected.
Inspired by a discussion on Twitter that resulted in this blog post from Kevin Flerlage, Sean challenged us this week to build a viz that should adapt for different devices.
This makes use of the dashboard layout feature in Tableau (see here for further information). The focus on this week is really on the layouts, the charts are relatively straightforward, so I’m just going to summarise each one fairly quickly.
Note on the data
In the requirements, Sean talked about filtering the date to 2023 and assuming a YTD value where ‘today’ was 8th March 2023. The data set linked in the requirements didn’t contain that information. I chose instead just to use the 2022.4 version of Superstore I had. As this challenge wasn’t going to contain any complicated table calcs/LODs I wasn’t worried that my numbers might not match.
Filtering the data
To restrict the information based on Sean’s requirements, I created a parameter to represent ‘today’
pToday
date parameter defaulted to 8 March 2022
I then created a field to use to ensure I only counted data up to that date.
Dates to Include
[Order Date] <= [pToday]
I added this to the Filter shelf and set to True. In addition I added Order Date to the Filter shelf, and selected Years > 2022. Both these fields I set to apply to all worksheets using this datasource.
Building the KPIs
Format Sales and Profit to be $ with 0 dp. If it doesn’t exist, create
Profit Ratio
SUM([Profit])/SUM([Sales])
and format this to % to 1 dp.
Add Measure Names to Filter and filter to the three measures (Sales, Profit and Profit Ratio). Add Measure Names to Columns and Measure Values to Text. Re-order as required. Add Measure Names to Text too and format the Text as required, Remove row dividers and hide the column headers.
Building the bar chart
Add Sub-Category to Rows and Profit Ratio to Columns and sort descending, Add Sales to Colour and add Profit to Tooltip. Adjust tooltip. Hide the Sub-Category column heading from displaying (right click > Hide field labels for Rows). Change the title of the viz.
Building the Map
Double click State/Province to automatically generate a map of the USA (if it doesn’t display, check the location is set to USA – Map menu > Edit Locations).
Add Profit Ratio to Colour and add Sales and Profit to Tooltip. Adjust tooltip.
Remove all the background map layers by selecting Background Layers from the Map menu, and unchecking all the options listed on the menu that displays on the left hand side.
Hide the ‘unknown’ indicator if its displaying (right click > hide indicator).
Stop the map options (that allow zoom & pan etc) from displaying by selecting Map Options from the Map menu and unchecking all the options that are presented.
Remove row and column dividers and change the title of the viz.
Building the Scatter Plot
Add Sales to Columns and Profit to Rows. Add Order ID to Detail and Profit Ratio to Colour. Make the zero lines slightly more prominent and title the viz.
On a new sheet, add Customer Name and Product Name to Rows. Add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and restrict to Quantity, Sales and Profit. Reorder the measures as required.
Add subtotals -from the Analysis menu select Totals > Add all Subtotals.
Set the row banding to none. Centre align the Customer Name column. Name the sheet Order Details or similar
Back on the scatter plot sheet, adjust the Tooltip and add a reference to the Order Details sheet by using the Insert > Sheets > select sheet
How well the viz in tooltip displays can often be a bit of trial and error. You may need to adjust the width and height properties on the referenced sheet in the tooltip. Also, sometimes setting the Fit property of the Order Details sheet may help too.
Building the default dashboard
Create a dashboard sized Generic Desktop. Set the background of the dashboard to grey (Format menu > Dashboard) Arrange the four vizzes on the dashboard. You may need to use horizontal and vertical containers to help you align everything where you want. Add padding around all the viz objects (I set mine to 10). Hide the title of the KPI viz. For the other 3 vizzes, set the background to white, so the title is also in white.
Set filter actions on both the bar chart and map, by selecting the object and then choosing Use as Filter from the context menu.
Click on the bar and check the other vizzes all filter. Do the same with the map. Now we’re ready to apply the different device layouts.
Creating the Tablet layout
The dashboard initially created is the Default dashboard, and we can tell this by looking at the left hand pane.
To add a new layout for a generic tablet, click the Device Preview button.
A Device Preview bar will appear at the top of the dashboard, Scroll through the Device type options until Tablet is displayed. By default the model should already be Generic Tablet.A border will display over the dashboard which indicates the boundaries of the dashboard based on the dimensions of the device. Click the Add Tablet Layout button.
The dashboard will immediately be resized to fit the boundary. Some of the vizzes are squashed up. Manually readjust so everything is displayed as required. A Tablet option will have appeared on the left hand side, and you can now toggle between Default and Tablet (click on the words in the left hand pane) and see the dashboard adjusting.
Creating the phone layout
Click on the Phone option (top left under Tablet), or scroll through and select Device Type = Phone.
By default, everything on the default dashboard is displayed on the Phone layout, which is obviously a portrait layout, optimised for scrolling down.
We don’t want the title, or the Scatter plot on this layout. To remove them, click the locked padlock icon and it will change to an unlocked icon, and the dashboard will be editable..
Remove the title object and the scatter plot object. In my case I also removed my footer information. Adjust the bar chart so its a bit longer, so the bars and labels are readable. It doesn’t matter that it will extend outside the bounds.
And that’s it – you now have 3 layouts for a single dashboard. Publish the viz to Tableau Public, then test out by accessing the viz from a tablet and/or your phone. Tableau will detect the type of device you’re accessing from, and show the most suitable display. Below are images of my viz accessed from my laptop, and on my mobile.
In this week’s challenge, Erica set us the task of building a filter that only contained a subset of the dimension values – ie a set of core values always had to remain in the view, and weren’t available to be filtered out.
Erica advised there were hints available, and that she had solved the problem herself via an existing Tableau Knowledge Base article.
The requirements stated Sets were involved and so I attempted down this path, creating a set to store the ‘core’ cities (as per the requirements), and then using a combined set of all cities and ‘core’ cities to just display the values not in ‘core’. However I couldn’t get things working, so I checked out the hints.
The first hint alluded to 2 sheets, which initially I thought one for the ‘core’ cities and one for the rest, but quickly realised this would only work if there hadn’t been the additional ‘bonus’ requirement to sort the data based on the sales (ie the core cities and the rest could become interspersed in the viz).
So after further fiddling, and unsuccessful ideas, I ended up referencing the KB article and built out a solution. After publishing, my good friend and fellow #WOW participant Rosario Gauna, published her solution which she managed in a single sheet, and in a manner that was much more elegant. So it’s a double solution guide today – what I did based on the KB article and a recreation of Rosario’s solution (so I have this to reference and remind myself if ever I have the need to recreate).
Solution 1 – The 2 sheet solution
Firstly, create a set called Key Cities (right click on City > Create > Set) and select the 5 cities listed in the requirements.
Key Cities
On a new sheet, add State/Province to the Filter shelf and choose Ohio, then add City to Rows and Sales to Columns and sort descending. Add Key Cities to Colour and adjust accordingly.
Call this sheet Sales by City
On a new sheet, add State/Province to Filter and select Ohio, then add City to Rows. Call this sheet FilterSheet.
Duplicate the City field (right click field in the data pane and select Duplicate). This will create a new field called City (copy) in the data pane.
Add City (copy) to the Filter shelf of the Filter Sheet sheet, select the 5 core cities and then check the Exclude checkbox.
Add theoriginal City field to the Filter shelf as well and select All. Show the filter on the sheet, and adjust so it displays Only Relevant Values
The list of options in the filter list should only show the cities in Ohio that aren’t one of the five key states.
Set the City filter to Apply to Worksheets > Selected worksheets and select the Sales by City worksheet
Customise the City filter in the Filter Sheet sheet so that the All option does not display. From the context menu of the City filter control, select Customise and ensure Show ‘All’ Value is unchecked.
Navigate back to the Sales by City sheet and show the City filter values, ensuring all are displayed. This list will include the key cities, but don’t worry. Uncheck a value that isn’t in the list of key cities eg Bowling Green. The city will disappear from the viz, but if you navigate to the Filter Sheet, you should also see the value is unselected in that list too.
This is the filtering behaviour we’re after – selections made to the City filter on the Filter Sheet affect the values in the City filter on the Sales by City sheet.
Now we need to address the sorting.
Again I think I ended up doing something a bit more complicated than needed – check out the sorting described in the 2nd solution, as that would apply here too – it just isn’t what I did at the time.
Firstly, we need a parameter to determine which sort selection to use
pSort
string parameter containing two list entries Key Cities and Sales, defaulted to Key Cities
I decided I wanted to sort by a number, which for Sales was fine, but when Key Cities was selected, I needed to ensure the values for the Key Cities were always greater than the maximum value for the non key cities. For this I needed to get a handle on the value of sales for the non key city that had the largest sales.
Max Non Key Sales
{FIXED : MAX(IF NOT [Key Cities] THEN ({FIXED [State/Province], [City] : SUM ([Sales])})END)}
If the city is not a key city, then get the total sales for each State & City (potential that a city can exist in multiple states, hence the need to declare the State), and then return the max of those.
To see what this is doing, on a new sheet, add State/Province to Filter and select Ohio, then add Key Cities and City to Rows and Sales to Text and sort by Sales descending
We’re looking for the value 8203 as this is the largest sales for the cities not tagged as a Key City.
Add Max Sales Non Key City to the view…. the value doesn’t match what we expected.
This is because a FIXED level of detail (LOD) calculation works across the entire data set, so the fact we’ve filtered by Ohio is being disregarded. To resolve this, set the State/Province field on the Filter shelf so it is Add To Context
This pill will change to grey, and the values should update, as now the LOD is being applied after the context filter has been applied.
With this we can work out a sort field
Sort
CASE [pSort] WHEN ‘Sales’ THEN SUM([Sales]) * -1 ELSE (IF ATTR([Key Cities]) THEN SUM([Sales]) + SUM([Max Sales Non Key]) ELSE SUM([Sales]) END) * -1
END
If we’re sorting by Sales then use the total Sales value * -1, otherwise, if we’re sorting by Key Cities then, if the City is a key city, then add the total sales to the max sales value, otherwise just use the total sales value. Multiple the result by -1. By adding this value, it ensures the values for the Key Cities are always larger than those for the non key cities. The -1 means the sort will be descending.
Test this out, by adding the Sort field as a discrete (blue) field to the Rows of the test sheet we’ve been using above. Ensure the Sort field is listed first, and move the Key Cities field to be third. Show the parameter control, and test switching between the options. The values in the Sort field are always in an ascending order, but the displayed Sales values will be ordered depending on the sort option chosen
Back on the Sales by City sheet, add the Sort pill to the Rows before the City pill, and add the State/Province filter to context.
Hide the Sort field.
The labels need to be displayed inside the bars, so for this we need a dual axis.
Add another copy of Sales to the Columns. On the second Sales marks card, set the option to Show mark labels from the Label shelf. We need the text of the label to be different to the existing bars, so create a duplicate of the Key Cities field, so we have Key Cities (copy) and add this to the Colour shelf of the second marks card. Adjust the colours accordingly to white and black.
Change the mark type of the 2nd marks card to Gantt bar, reduce the opacity of the Colour to 0% and reduce the Size to as small as possible. Adjust the alignment of the Label to left middle, and set the font to be bold and match mark colour.
Make the chart dual axis and synchronise the axis. Set the mark type of the first marks card back to a bar if it changes.
Remove all row and column dividers, and hide the top axis. Hide the City column label too. Edit the bottom axis, and fix to start from 0 and end automatic. Adjust the tick marks to display every 5000 values.
Add this sheet to a dashboard. Remove the colour legends that automatically get added and remove the City filter control too. Leave the sort parameter.
Then add the Filter Sheet as a Floating object and position bottom right. The City filter for this sheet should also automatically display. If it doesn’t show it (click the context menu of the Filter Sheet object > Filters > City).
Change the City filter to be a multiple values dropdown control and set it to be fixed (unselect the Floating option on the context menu).
Now hide the title on the Filter Sheet object and resize to make it teeny tiny, so you can’t see anything
Now you have the core objects needed for a functional dashboard – you’ll just want to take some time moving them into place, and excluding other Cities.
So shout out again to Rosario Gauna, as this is actually her solution!
We’ll build this out in a table first, so we can see what’s going on.
On a new sheet add State/Province to Filter and select Ohio, then add City and Key Cities (the set created above) to Rows. Add Sales to Text.
What we’re going to do is create another set which will just contain the cities not identified as key cities. For this, we need to store against every row (including the key cities) the name of a City that isn’t a key city. For those that already aren’t a key city, that is just its own City name, but for those that are key cities, we want to store a non key city…. sounds confusing right…. let’s build this up.
Firstly, let’s just get those non key cities
Non Key City
IF NOT [Key Cities] THEN [City] END
Add this to the sheet. It shows NULL against all the Key Cities and the City value for all the others
We’re going to use this to set a ‘default; value against the key cities.
Min Non Key City
{FIXED: MIN([Non Key City ])}
Theis returns the value from the Non Key City field which is alphabetically first. Using MAX would work just as well. When we add this to the sheet, we also need to set the State/Province filter to be Add to Context, otherwise we get a City from the whole data set, and not just Ohio.
We can now create a field that will just contain a distinct list of the non key cities
Other Cities
IF NOT [Key Cities] THEN [City] ELSE [Min Non Key City] END
Add this to the sheet
For every City, the Other Cities field contains a non key city value. Now we have this, we can create a set from this field
Other Cities Set
Ensure all values are selected – don’t worry that you can see cities that aren’t relevant at this stage
Add Other Cities Set to the Filter shelf and also add to the Rows shelf next to the Key Cities field. From the context menu of the Other Cities Set on the Filter shelf, select Show Set. The list of non key cities should be displayed. If there’s more than you expect, ensure the control is set to All Values in Context.
If you uncheck Bowling Green from the list, all the key cities and Bowling Green will disappear, but we don’t want this. We only want the row where City=Bowling Green to disappear. For this we need
Records to Keep
[Key Cities] OR [Other Cities Set]
Add this to the Filter shelf and set to True. Remove the Other Cities Set from the Filter shelf (the list should remain). Now if you remove Bowling Green, only that row should disappear, and if you uncheck all, so no city is selected, the key cities should remain
Adjust the display so the rows are sorted by Sales descending (either use the Sort button in the toolbar, or set the sort on the City field).
Then create a field
Sort Order
CASE [pSort] WHEN ‘Key Cities’ THEN [Key Cities] ELSE TRUE END
Add this to the Rows in front of City, and show the pSort parameter (see above for details if you haven’t created this yet). When pSort is set to Key Cities, manually change the order of the values displayed so True is listed before False. The rows are displayed in descending Sales value for each Sort Order Value. When Sales is selected to sort by, the Sort Order is true for all rows.
Now we’ve got all the components needed to build the viz, and you should be able to adapt the steps above to get it to work. The key difference is using the Records To Keep field on the Filter shelf, displaying the members of the Other Cities Set to control the filtering, and managing the sort using the Sort Order field instead.
My workbook showing this solution is published here.
In the final week of ‘alternative charts month’, Luke set this challenge as different way of presenting data that you might typically see in a side-by-side bar chart.
Luke had indicated on the #WOW splash screen, that this challenge was ‘easy’, but that’s always dependent on your level of Tableau. He also added a note in the requirements that if you wanted to be ‘advanced’ to solve it with Table Calcs only.
I figured I’d just start and see what I ended up with (sometimes, my natural brain thinking takes me down a table calc route..)
In a change to my usual starting point, I started trying to remember what I needed to do to get the comet… I felt pretty sure that path would be involved somewhere.
So, I added Order Date to the Filter shelf and filtered to years 2021 and 2022 only.
Then I added Sub-Category to Rows, Sales to Columns and Order Date (which defaulted to YEAR(Order Date)) to Detail. I changed the mark type to circle initially.
Ok – I had what I was expecting – 2 circles per row, one for each year.
So then I change the mark type to line and moved YEAR([Order Date]) from Detail to Path. This meant my lines were joined.
I then added Order Date to Size, and reset Order Date to be at the YEAR level. Hey presto! My comet shapes appeared.
I now wanted to show a white circle mark just for the 2022 sales, so I created
Max Year Sales LOD
{FIXED [Sub-Category]:SUM(IF YEAR([Order Date]) = {FIXED:MAX(YEAR([Order Date]))} THEN [Sales] END)}
this looks a bit long-winded ( I do usually break this up)… so let’s review what’s going on…
{FIXED:MAX(YEAR([Order Date]))} returns the latest year in the data set (ie 2022) and spreads that across every row of data. So the formula is comparing each row, and if the Order Date year matches 2022, the value of the Sales is returned. This is then all aggregated and totalled for each Sub-Category.
Add this field to Columns, make dual axis and synchronise axis.
Remove Measure Names from the All marks card and change the mark type of the Max Years Sales LOD card to circle. Colour white.
Remove the YEAR([Order Date]) pill from the Size shelf of the Max Years Sales LOD card, so the size of the comet (the Sales card) and the circle can be adjusted independently. Adjust the sizes enough so the comet is visible around the circle.
Sort the Sub-Category field by Max Year Sales LOD descending
Next we need to colour the comets based on whether Sales increased or decreased.
Prev Year Sales LOD
{FIXED [Sub-Category]: SUM( IF YEAR([Order Date]) = {FIXED:MAX(YEAR([Order Date]))} -1 THEN [Sales] END)}
is doing similar to the above calculation, but {FIXED:MAX(YEAR([Order Date]))} -1 returns 2021 instead.
and with this we can created
Sales Increased? LOD
[Max Year Sales LOD] > [Prev Year Sales LOD]
Add this to the Colour shelf of the Sales marks card, and adjust accordingly.
To label the comets, check the show mark labels checkbox on the Label shelf dialog, and set to line ends and label end of line. You may need to check the allow labels to overlap option too if you’re not seeing all the labels.
The dashboard shows a circular size legend which is related to the circle mark, so I created
Order Date (Years)
YEAR([Order Date])
and added this to the Size shelf of the Max Year Sales LOD marks card.
Add Sales to the Tooltip shelf of the Max Year Sales LOD marks card too and adjust the tooltips.
Add row dividers, and remove all column dividers, gridlines and axis. Adjust the formatting of the Sub-Category row labels and hide the column title. Set the background of the worksheet to a grey colour.
And so that ended up being the LOD version of the chart, which is accessible from here.
But I had time, so I figured I’d see if I could crack the Table calcs version…
Building the Table Calculation Solution
This starts by repeating the intial steps above to get a basic single axis comet chart for Sales, split by Year.
We now need to get the sales for 2022 only. For starters, let’s identify the latest year
Latest Year
WINDOW_MAX(MAX(YEAR([Order Date])))
and let’s build up a table, so we can start to sense check what’s going on, as table calcs can be pesky!
Our Latest Year table calc is returning 2022 for every row in our table. To get the sales just for 2022
Window Max Year Sales
WINDOW_MAX(IF MIN(YEAR([Order Date]))=[Latest Year] THEN SUM([Sales]) END)
if the order date year is 2022, then return Sales (otherwise null) and spread the maximum value across the rows. When we add this into the table, we need to set the table calculation to compute using Year or Order Date, so that it is calculating the WINDOW_MAX for each Sub-Category
Add this field to Columns on the comet chart, and adjust the table calculation so Window Max Year Sales is computing by Year of Order Date only, and Latest Year by both fields (see the Nested Calculations dropdown)
Make the chart dual axis, and synchronise the axis. Make the adjustments to the mark types and sizes as described above.
We can’t sort the Sub-Category field in the way we did above, as table calculation fields aren’t accessible in the sort dialog. Instead add Window Max Year Sales to Rows and change it to be discrete (blue pill) and move it to be in front of Sub_Category. Adjust the table calc settings to match that described above. This should make the chart sort ascending.
To reverse, double click into the blue Window Max Year Sales pill on Rows and add * -1 to the end
Annoyingly this will revert it back to a measure, so reapply the steps above, and you should end up with a correctly sorted display. Hide the Window Max Year Sales blue pill.
Now to colour the comet.
Back to the tabular view. Add a Difference quick table calculation to the Sales pill and edit the table calculation to compute using Year(Order Date) only.
Drag the Sales pill with the difference table calc from the Measure Values section and drop into the left hand data pane. This will create a dedicated instance of the calculation. Rename it to
Sales Diff – TC
ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), -1)
If you examine it, it should contain the above calculation.
With this we can then work out if sales have increased or not
Adding this into the table, and setting the nested table calcs to both compute by Year Order Date, you can see that the values for each Sub-Category are either 1 or 0.
Add this to the Colour shelf of the Sales marks card. Make sure the field is discrete and the table calcs are set to compute by Year Order Date. Adjust the colours.
Finally make adjustments for the tooltip and adjust the formatting to clean up the chart. My table calc version of the viz is here.
The theme of alternative chart types continued with Lorna setting this challenge to create a jittered bar chart. We needed to use the same fake survey data set used in week 4, so initially some data remodelling was required. I’ve already blogged that here, so please refer to the modelling the data section if you need help.
The questions all need to be grouped based on the text that preceded the question, so I created
Question Group
TRIM( SPLIT( [Question], “-“, 1 ) )
which split off the text in front of the first ‘-‘. I actually created this by right-clicking on the Question field > Transform > Custom Split and selecting to to split off the first 1 column using the – separator.
I also created a field to number the responses
Response No
CASE [Response] WHEN ‘Strongly Disagree’ THEN 1 WHEN ‘Disagree’ THEN 2 WHEN ‘Neutral’ THEn 3 WHEN ‘Agree’ THEN 4 ELSE 5 END
Once I’d done that I was able to ratify the numbers by building a simple table with Response No and Response on Columns, Question Group on Rows, and CNT(Id) on Text.
Eyeballing these numbers against where the ‘bars’ in Lorna’s viz stopped and I figured that was on the right track. However then I wasn’t particularly sure what to do next.
Lorna’s instructions were very brief. I figured I’d need to use a jitter (ie RANDOM()) somewhere, and as I’d need to add Id to the Detail shelf at some point, to generate 1 dot per respondent, I deduced I’d also need
Total Respondents
{FIXED [Response], [Question Group]:COUNT([Id])}
Adding this into by table above and I got the same values as CNT(Id) which is what I expected.
After scratching my head a bit, I decided that Lorna was possibly being vague, as there was probably help ‘out there’. So I googled, and immediately stumbled upon this very useful blog post, which pointed me in the right direction for the additional calculations required.
I created
Columns
RANDOM()
Jitter
RANDOM()
note – due to the way RANDOM() works, Jitter and Columns won’t contain the same value.
Rows
[Jitter] * [Total Respondents]
And with these I could build out the viz
Add Response No, Response and Columns to Columns. Change Columns to be a dimension.
Add Question Group and Rows to Rows. Change Rows to be a dimension. Add Id to Detail.
Change the mark type to circle, reduce the size and add Question Group to Colour and adjust accordingly.
Finally add Question to Tooltip and adjust the tooltip. Then remove all gridlines, hide the Columns axis, change the title of the Rows axis, and remove the row and column titles (hide field labels for rows / columns).
A pretty short blog today! My published viz is here.
I’ve built jitter plots in the past for #WorkoutWednesday challenges (the hidden RANDOM() function is your friend in this), but I wanted to see how far I could get without having to peak at my previous solutions.
So I connected to the baseball data provided, and cracked on, building the jitter in a single sheet using Measure Names on the columns. But then I got stuck when it came to labelling the tooltips…. surely this didn’t need a sheet per measure did it…
…maybe it did… so I proceeded to recreate as 4 separate sheets, and felt quite smug that I’d managed to make use of the ‘little used’ worksheet caption to provide the summary detail at the bottom of each measure. When I’d finished, I checked Kyle’s solution, as the summary values for the SLG & OPS measures seemed to be mixed up…. and what did I find…. he had managed to build the jitter within a single sheet as he had pivoted the data first! Argggghhhh! It just hadn’t crossed my mind, and Kyle had chosen not to drop that hint in the requirements…. hey ho! c’est la vie! I may well recreate with a pivoted version at a later date, but for now, I am blogging what I did…
My solution has ended up with a lot of calculated fields as a result, as equivalent fields needed to be created for every measure. Most of this was managed via duplicating and editing existing fields, so it actually wasn’t too onerous.
Building the calculated fields
We’ll start as usual by building out the fields required, and will focus on the BA measure initially.
Add Name and BA into a tabular view and Sort descending. Format the BA measure to be a number with 3 decimal places.
We need to know the rank of each player. Create a calculated field
Rank BA
RANK(SUM([BA]))
Add this to the view, and we should get the player rankings displayed from 1 downwards.
Now the requirement wants us to normalise the measures so they can be displayed on the same axis (or in my case, since it’s not a single chart I’m building), within the same axis range.
What this means is we want to plot the measure on a scale between 0 and 1 where 0 represents the lowest measure value, and 1 the highest. for this we need
Min BA
{FIXED :MIN([BA])}
and
Max BA
{FIXED :MAX([BA])}
The normalised value then becomes
Normalise BA
([BA] – [Min BA])/([Max BA]-[Min BA])
The difference between the current value and the lowest value, as a proportion of the range (ie the difference between the highest and lowest values).
Adding this to the table, you should see that the Normalise BA value for the highest ranked player is 1 and that for the lowest ranked is 0.
As part of the information displayed, we also need to know the percentile each player is in.
Percentile BA
RANK_PERCENTILE(SUM([BA]))
Format this to a percentage with 0 dp and add into the table.
Next we need to identify the player selected, so we’re going to create a parameter based off of the Name.
Select a Player
Right click Name -> create -> Parameter. This will open the parameter dialog and auto populate the list of options with the values from the Name field. Default the parameter to Julio Rodriguez.
We can then create a field to identify if the player is the one selected
Is Selected Player?
[Name] = [Select a Player]
Add this into the table, on the Rows before Name and sort so True is listed at the top (just easier to check the results).
So now we need to identify the rank and percentile of the selected player only
Selected Player BA Rank
WINDOW_MAX(IF ATTR([Is Selected Player]) THEN [Rank BA] END)
format this to a number with 0 dp
Selected Player BA Percentile
WINDOW_MAX(IF ATTR([Is Selected Player]) THEN [Percentile BA] END)
format this to a percentage with 0 dp.
The window_max function has the effect of ‘spreading’ the result over all the rows.
Finally we need to get a count of all the players
Count Players
{FIXED:COUNTD([Name])}
format this to a number with 0 dp.
Building the Jitter Plot
To build a jitter plot, we need to plot each mark against 2 axes. The Normalise BA measure is one axis, but we need to create ‘something’ for the other. This is the value to make the ‘jitter’ which is essentially an arbitrary value between 0 and 1 that we can plot the mark against, and means the marks don’t all end up in a single line on top of each other, and we can get a better ‘feel’ for the volume of data being represented.
Jitter
RANDOM()
The random() function is a ‘hidden’ function that will, as it’s name suggests, generate a random number. It is ‘hidden’ as it only works with some data sources. Excel for example is fine, but if you were connected to a Snowflake database, you can’t use it.
The nature of random, also means that you can’t guarantee the value it produces, and it will regenerate on data refresh, so if you’re looking to compare your solution directly, your dots will not be positioned exactly the same.
On a new sheet add Jitter to Columns and Normalise BA to Rows. Add Name to Detail and change the mark type to Circle.
Add Is Selected Player to Colour, adjust accordingly and add a border to the circle. I dropped the opacity to 70%. Order the colour legend, so True is listed first, to ensure this circle is always ‘on top’.
Then add Is Selected Player to Size. Edit the sizes so they are reversed and adjust the sizes until you’re happy.
To label just the selected player mark
Label:BA
IF [Is Selected Player] THEN [BA] END
format this with a custom number font ,##.000;-#,##.000
Add this to the Label shelf and adjust the font colour, and align centrally
Add Rank BA and BA to the Tooltip shelf and adjust tooltip to suit. You will need to adjust the table calculation setting of the Rank BA field so that it is computing by all the fields.
Add Selected Player BA Rank and Selected Player BA Percentile and Count Players to the Detail shelf. Adjust the table calculations as above (including any nested calcs), then show the worksheet caption (Worksheet -> Show Caption), and edit the caption to display the relevant text.
From the analytics pane, drag the Median with Quartiles option onto the canvas and drop it on the table / Normalise BA axis option. Remove the quartile reference lines (right cick axis -> remove reference line), and edit the median reference line to be a dashed line with no label.
Finally remove all gridlines/dividers/axes lines and hide the axes. Title the sheet as per the measure ie BA, and align centrally.
Format the Caption and the Title to have a light grey background and a slightly darker thin border.
Now, repeat all that for the other measures 🙂 This isn’t that bad. All the fields above labelled BA, need duplicating, renaming and updated to reference the next measure eg OBP.
Once done, duplicate the BA jitter plot sheet, and replace all the ‘BA’ related fields with the equivalent ones, by dragging the equivalent field and dropping it directly on top. Sense check the table calculation settings are all ok. You may need to update the text in the caption, as that seems to lose anything to do with the table calculation fields referenced when they get touched.
Ultimately you should end up with 4 sheets.
Putting it all together
On a dashboard, use a horizontal container to position all 4 sheets in side by side. Show the worksheet caption for each sheet. Reduce the outer padding for each sheet to 0, and add a thin border around each sheet.
Add a parameter action to drive the interactivity ‘on click’ of a circle
Select Player
On select of any of the source sheets, update the Select a Player parameter with the value from the Name field. Retain the selected value on ‘unclick’
To prevent the selection on click from being ‘highlighted’, and al the other marks ‘fading’, we need one final step.
Create new calculated fields
True
TRUE
False
FALSE
Add both these fields to the Detail shelf of each of the 4 sheets.
Then add a dashboard filter action for each sheet which on select, goes from the sheet on the dashboard to the worksheet itself, passing the selected fields of True = False. Show all values when unselected.
My published viz is here. Kyle’s solution with a lot less calculated fields, and only 2 sheets (1 for the jitter and 1 for the summary section at the bottom) is here. You will need to pivot the data via the data source pane first through 🙂 Next time, when I really feel something should be able to be done in 1 sheet, I’ll try to think a little longer…. upshot though, I impressed myself at the use of the caption for the summary – something I must consider using more often!
For my first #WOW2023 challenge, as an ‘official’ part of the #WOW crew, I challenged everyone to build this funnel chart. Inspiration came from a colleague who posed me the challenge of building something similar, ensuring the bars were a single entity when clicked on. In that example we’d just used Superstore data, but it wasn’t really reflective of how a funnel would typically work, so I sourced some dummy sales opportunity data and made the challenge more relevant to a real business scenario.
Building the Funnel Chart
I saw this as a table calculation exercise, though I have subsequently seen solutions that manage it all through LODs. I’ll obviously blog my solution.
Let’s start by getting the core data into a table, so we can see what we’re aiming for
The total value of all the Opportunities is $91.4M. Based on the sales pipeline process and the assumptions documented in the requirements, all of that value will have passed through the first stage – Prospecting.
There is $6.1M still sitting in Prospecting, so when we move onto the next stage, stage 2 -Qualification, the total amount that has passed through will be $91.4M – $6.1M = $85.3M
As there is $6.5M sitting in Qualification, as we move to stage 3, the total amount that has passed through will be $85.3M – $6.5M = $78.7M. A similar pattern repeats for Stage 4, but for Stage 5, Closed Won, the value that has ‘passed’ through that stage, is equivalent to itself.
If we amend the Value field to be a Running Total quick table calculation (and add Value back into the view too), we get
The Running Sum is computing down the table , with the value in each row being the sum of the preceding rows. But this is opposite to what we want. We want Stage 1 to have the biggest value, the total, and the values to get smaller as you look down the table.
To resolve this, edit the table calculation and change the Sort order to custom sort by Minimum Stage No Descending
This results in the running total to sum from the last row (Stage 6) up to the first row (Stage 1), and you can see the value against Stage 1 matches the total for the Value column.
However, this still isn’t quite what we need. While we need to consider the amount for Closed Lost in stages 1-4, we shouldn’t be including it in stage 5, Closed Won.
While excluding the Stage 6 from the view (Stage No on Filter shelf set to Exclude 6) , gives the correct value for Stage 5, we’ve now lost the amount for Closed Lost in the other rows
So, we need some additional calculations to help resolve this.
Amount Lost
{FIXED : SUM(IF [Stage No]=6 THEN [Value] END)}
This just captures the amount of Stage 6 and ‘spreads it across every row of data.
Let’s now get our running total field, baked into it’s own calculation, as this will help with other calcs
Cumulative Value Per Stage
RUNNING_SUM(SUM([Value]))
Replace the Value table calc field with this one, ensuring the table calculation settings are identical to those described above.
Now let’s add the Amount Lost onto the Cumulative Value Per Stage unless we’re at Stage 5, Closed Won
Total Amount Per Stage Inc Lost
IF MIN([Stage No]) = 5 THEN [Cumulative Value Per Stage] ELSE [Cumulative Value Per Stage] + SUM([Amount Lost]) END
Pop this into the table (checking the table calc settings are as expected) and the total amount now stored against Stage 1, is the total value of all the Opportunities.
Ok, so now we know the values, how do we plot this in such a way that it represents a funnel. I chose to do this by first working out the proportion of the total value that the ‘reverse’ cumulative value represented.
Total Value
{FIXED:SUM([Value])}
meant I could determine
Proportion of Total
[Total Amount Per Stage Inc Lost] / SUM([Total Value])
I formatted this to percentage with 0 dp.
Adding these into the table
If we plotted this information on a bar chart, we’d get this
but we need to ‘centre’ each bar in relation to the first bar to give us the funnel effect. This means we need to shift each ‘bar’ to the right, by half the amount that is the difference between 100% and the Proportion of Total.
Position to Plot
(1 – [Proportion of Total])/2
We can’t represent this with a actual bar chart, but instead we can use a Gannt chart.
Stage No to Rows
Stage No to Filter and exclude Stage 6
Stage to Detail
Position to Plot to Columns, adjusting the table calculation as previously described
Change mark type to Gantt bar
Add Proportion of Total to Size (and verify the table calc is set properly)
Hey presto! A funnel!
To finalise
add Stage to Label and align centrally. Make the font bold and match mark colour.
add Cumulative Value per Stage to Colour and reverse the colour range (via the Edit Colours dialog)
Widen each row a bit.
Fix the Position to Plot axis to start at 0 and end at 1 to ensure the funnel is centred exactly.
Add Value and Total Amount Per Stage Inc Lost to the Tooltip and adjust the tooltip text accordingly (don’t forget to sense check the table calc settings!).
Hide the Position to Plot axis, and the Stage No column. Remove all gridlines, zero lines, axis rulers /row/column banding etc.
Building the KPIs
We need a few calculated fields to store the required numbers
Won
{FIXED:SUM(IF [Stage No]=5 THEN [Value] END)} / [Total Value]
formatted to a percentage with 0 dp.
Lost
{FIXED:SUM(IF [Stage No]=6 THEN [Value] END)} / [Total Value]
formatted to a percentage with 0 dp.
Outstanding
1-([Lost] + [Won])
formatted to a percentage with 0dp.
On a new sheet add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and select Total Value, Won, Lost, Outstanding. Manually re-order the columns.
Format Total Value to be a number displayed in millions with 1 decimal place & prefixed with $.
Add Measure Names to Text and adjust the text as required. Align the text to be centred.
Remove the row banding, and hide the column heading.
Then arrange the two sheets on the dashboard, ensuring both are set to fit entire view.
Donna + DataViz 