For this week’s challenge, Yusuke asked us to provide a solution to allow charts to be coloured by different dimension, but he sprinkled a few extras in just for good measure 🙂
Defining the parameter
The key driver here is going to be the use of a parameter to define the dimension we need to colour by.
pColourBy
string parameter defaulted to Order Date, listing the 4 options as below
We then need a field that uses this parameter to define the actual dimension we’ll colour by
Colour
CASE [pColourBy] WHEN ‘Order Date’ THEN STR(YEAR([Order Date])) WHEN ‘Region’ THEN [Region] WHEN ‘Category’ THEN [Category] WHEN ‘Segment’ THEN [Segment] END
Building the Order Date chart
On a new sheet, add Order Date to Columns and Sales to Rows. Change the mark type to Bar and add Colour to the Colour shelf. Adjust the colours to suit, set the opacity to 70% and add a white border. Show the pColourBy parameter.
Change the options in the pColourBy parameter and each time readjust the colours as you wish.
Add a reference line to the Sales axis that displays the value of TotalSales per cell
Format the reference line to format the displayed number in $M and bold font, and align top middle.
Create a new field
% of Sales
IF SUM([Sales]) / TOTAL(SUM([Sales])) <> 1 THEN SUM([Sales]) / TOTAL(SUM([Sales])) END
and format to % to 1dp. This will only display a value if its not 100%.
Add this to the Label. Adjust the table calculation setting so it is computing by the Colour field only.
Adjust the Label so the font is bold and the label only appears when Highlighted. Then update the Tooltip as required.
Although not explicitly called out in the requirements, I noted that if Yusuke clicked on the chart title, it reset the dimension to colour by. To deal with this we need to create
param Order Date
‘Order Date’
Add this to the Detail shelf.
We also need to ‘fake’ the title to be part of the chart itself (so it’s clickable). Double click into the Columns and manually type ‘Sales by Order Date’ and position the pill created before Order Date.
Right click on the column label (the text in darker font) and hide field labels for columns. Then right click on the column label to format – set the font to 12pt and bold, align left and shade the background to light grey. Increase the width of the column heading.
Then right click on the corner whitespace next to the heading just created, and format. Apply a light grey shading to the corner too.
If the ‘title’ is clicked, we don’t want it to be ‘highlighted’/’selected’. For this we will need fields
True
TRUE
False
FALSE
Add both of these to the Detail shelf.
Finally tidy up by removing the axis title, adjusting the font of the axis labels (I made them a bit darker), and removing row & column dividers. Name the sheet Order Date or similar.
Building the Region chart
Duplicate the Order Date chart and then click the option in the menu to swap axis so we have a horizontal bar chart.
Move the ‘Sales by Order Date’ pill from Rows to Columns and update the text to become ‘Sales by Region’ instead. Drag the Region pill and drop it directly over the Order Date pill on the Rows so it replaces it and all references to the field are replaced too. Widen the rows.
Right click on the ‘Region’ text in the column heading and hide field labels for rows. Format the reference line to align middle right.
Create a new field
param Region
‘Region’
and add this to the Detail shelf instead of the param Order Date field. Name the sheet Region or similar
Building the Category Chart
Duplicate the Region chart, and go through similar steps described above so the ‘title’ is Sales by Category and a new field
param Category
‘Category’
replaces param Region on the Detail shelf.
Building the Segment Chart
Repeat as above, this time setting the ‘title’ to Sales by Segment and a new field
param Segment
‘Segment’
replaces param Region on the Detail shelf.
Adding the interactivity
Add the sheets to a dashboard using layout containers and padding to organise as required. Then create the following dashboard actions
Highlight Action :Highlight
On hover of any of the charts on the dashboard, target all other charts, highlighting based on the Colour field only.
This action makes all the % labels appear when the mouse cursor is moved over the bars.
Parameter Action : Set Order Date Param
On Select of the Order Date sheet, set the pColourBy parameter with the value from the param Order Date field.
Parameter Action : Set Region Param
On Select of the Region sheet, set the pColourBy parameter with the value from the param Region field.
Parameter Action : Set Category Param
On Select of the Category sheet, set the pColourBy parameter with the value from the param Category field.
Parameter Action : Set Segment Param
On Select of the Segment sheet, set the pColourBy parameter with the value from the param Segment field.
These actions change the value displayed in the pColourBy parameter when the ‘title’ of the charts is clicked on.
Filter Action: Deselect Order Date Title
On select of the Order Date sheet on the dashboard, target the Order Date worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Region Title
On select of the Region sheet on the dashboard, target the Region worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Category Title
On select of the Category sheet on the dashboard, target the Categoryworksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
Filter Action: Deselect Segment Title
On select of the Segment sheet on the dashboard, target the Segment worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.
And once these have all been applied, you should have a functioning dashboard. My published version is here.
Erica set this week’s challenge, focusing on the ability to compare specific entities against themselves and ‘the whole’ without resulting in a mess of coloured spaghetti. 3 levels of difficulty were provided. As it stated the levels didn’t necessarily follow on from each, I just built (and am therefore blogging about) level 3 – the advanced challenge.
Defining the core parameters
For the user to select the main element they want to analyse we need
pPrimarySubCat
string parameter, that is sourced from a List based on the Sub-Category field when the workbook opens. Default to Binders.
This parameter will be visible to the user to select from a drop down list control.
To capture the secondary element to compare against, we need
pSecondarySubCat
string parameter defaulted to Bookcases.
This is just a ‘type in’ field, that won’t ultimately be displayed to the user, but populated via a dashboard parameter action on select of a line in the chart.
To control the different type of display options, we need
pDisplay
integer parameter sourced from a manual list which aliases the integer values for the displayed text strings. Defaulted to 2 (Difference from Primary)
Defining the additional calculations
As I often do, we’ll build out a tabular display to determine all the calcs required. On a new sheet, add Region and Sub-Category to Rows, then add Order Date at the Quarter level as a discrete (blue) pill to Columns. Add Sales to Text. Show the 3 parameters created above.
We need to identify which Sub-Categories will be coloured. This is based on whether they are a primary or secondary Sub-Category.
Is Primary or Secondary Sub Cat
[pPrimarySubCat] = [Sub-Category] OR [pSecondarySubCat] = [Sub-Category]
Add this to Rows. Based on existing selections, the rows for Binders and Bookcases should be set to True.
We will also need to identify which is the the Primary Sub-Category only to help determine how many rows are displayed, so create
Is Primary SubCat?
[pPrimarySubCat] = [Sub-Category]
Add to rows. In this case just Binders should be True at this point.
With this field, we can then work out how many ‘rows’ are going to be in our final viz display.
Display Row
IIF([pDisplay] = 0, TRUE, [Is Primary SubCat?])
ie, if the pDisplay parameter is ‘Raw values – overlay’ , then we’ll just display 1 row (so all rows set to True), otherwise there will be 2 rows, split based on whether the Sub-Category is the selected value in the pPrimarySubCat parameter or not.
Add this to Rows, and change the pDisplay parameter to see how this field changes.
We also need to display different values depending on what pDisplay option is selected. When the ‘Difference from Primary’ option is selected, then we need to show the Sales value for theprimary Sub Category, but the difference from this value for all others. For this we first need to capture just the sales for the primary Sub-Category
Sales For Primary Sub Cat
IF [Is Primary SubCat?] THEN [Sales] END
Add to the table and adjust Measure Names so it is displayed after the Order Date field. Rows for this column will only have values when the Sub-Category is the primary one selected.
Now we calculate the difference, but only if it’s not the primary Sub-Category; we want Sales in that instance
Sales Difference
IF MIN([Is Primary SubCat?]) THEN SUM([Sales]) ELSE SUM([Sales]) – WINDOW_MAX(SUM([Sales For Primary Sub Cat])) END
Here we’re using a WINDOW_MAX table calc to essentially ‘spread’ the value in the Sales for Primary Sub Cat column across all rows associated to the Region. Add this to the table, and adjust the table calculation setting of the pill, so it is computing by all fields except Region and Order Date
Finally, we need a field that will decide whether we’re displaying Sales or Sales Difference based on the pDisplay selection
Again, add to the table, adjust the table calc as above and then test the output of the field, as you adjust the pDisplay parameter.
While we’re here, we’ll just define another couple of calcs needed for the viz
Label Sub Cat
IF [Is Primary or Secondary Sub Cat] THEN [Sub-Category] END
Used to only display a label for either of the two selected Sub-Categories.
Tooltip – Value Label
IIF([pDisplay]=2 AND NOT([Is Primary SubCat?]), “Difference from ” + [pPrimarySubCat] + ” Sales”, “Sales”)
Will be used on the Tooltip to ensure the correct text is displayed depending on type of display selected.
Building the Viz
On a new sheet, show the 3 parameters and set them to the defaults (ie Binders, Bookcases and Difference from Primary).
Add Region to Columns, then add Order Date at the Quarter level as a continuous (green) pill to Columns. Add Display Row to Rows and adjust the Sort on the pill to be a manual sort, where True is listed first. Add Sub-Category to Detail, then add Value to Display to Rows and adjust the table calc so all fields except from Region and Order Date are selected.
Add Is Primary or Secondary Sub Cat to Colour. Some lines will disappear, but don’t worry. Then add Region to Detail, and then select the ‘detail’ icon to the left of the pill on the marks shelf, and change it to Colour so 2 pills are now on the Colour shelf. Adjust the table calculation setting of the Value to Display pill to ensure the Is Primary or Secondary Sub Cat field is also now checked – this should make all the lines reappear.
Then adjust the colours in the colour legend so all the entries that start ‘False’ are grey and the others are as required.
Adjust the sort on the Is Primary or Secondary Sub Cat pill on the marks card, so it is manually sorted with True first. This ensures the coloured lines are ‘on top’ and always visible. Add Is Primary SubCat? to Size shelf. Readjust the table calc on Value to Display again, and then adjust the Size so it is visibly thicker than the rest of the lines, which will probably be by adjusting both the range in the Size legend, and adjusting the slider on the Size shelf.
Add Label Sub Cat to the Label shelf (adjust table calc again), and set label to allow labels to overlap other marks. Add Tooltip – Value Label to tooltip and update the Tooltip as required
Add a reference line to the Value to Display axis, and set to be a constant of 0 displayed as a black dashed line
Edit both axis to update the axis titles on each, hide the Display Row pill (uncheck show header on the pill) and hide the Region column label (right click > hide field labels for columns).
Building the dashboard
Use layout containers to construct the dashboard as required
Create a dashboard parameter action to capture the value of the secondary Sub-Category
Set Second Sub Cat
On select of the Viz, set the pSecondarySubCat parameter with the value sourced from the Sub-Category field. When selection is cleared, set it <none>
Clicking one of the grey lines should now change the comparison Sub-Category. But you’ll notice the rest of the unselected lines are ‘faded’ and your selection is ‘highlighted’. We don’t want this to happen. To resolve, create new calculated field
HL
‘Dummy’
and add to the Detail shelf on the viz sheet itself.
Then add a dashboard highlight action
Un-Highlight
On selection of the Viz sheet on the dashboard, target the viz sheet on the dashboard, selecting the HL field only.
As all the marks have the HL ‘dummy’ field associated to them, they all become ‘highlighted’, giving the appearance of nothing actually being highlighted.
Finally, we need to make the title of the dashboard ‘dynamic’ and reflective of the selections made in the primary and secondary Sub-Category parameters. But the secondary one can be empty, so the text needs to handle this. An additional ‘ and ‘ needs to display if the secondary Sub-Category is set. I chose to use a parameter to help with this, as text objects on a dashboard can reference parameters.
Create a new parameter
pTitle-and
string field defaulted to the text <space>and<space>
Create a calculated field
Param-and
‘ and ‘
and add to the Detail shelf on the viz. Set it to be an attribute (this won’t impact the table calc).
Back on the dashboard, create another dashboard parameter action
Set ‘and’
on select of the Viz, set the pTitle-and parameter passing in the value from the Param-and field. When the selection is cleared, set to <none>.
Then create (or adjust) the title text object so it references the relevant parameters (notice the spacing – or lack of – between some of the fields)
This week’s #WOW2025 challenge was set live as part of TC25. Unfortunately, this year I couldn’t be there in person to meet everyone, which for the last 3 years has been my conference highlight 😦
Anyway, Kyle set the challenge, and conscious of time, provided a starting workbook, so the focus could be on the container and DZV functionality. For those who nailed this, he added some additional interactivity with dashboard actions.
So the first thing is to download the starter workbook from the challenge page.
I’m going to attempt to build this in the order of Kyle’s requirements.
Layout out the dashboard
So the requirement states that no floating objects are allowed. Typically when I build a dashboard for business purposes or where the layout is a little complicated, I always start by adding a floating container sized to the exact dashboard size and positioned 0,0. I then add tiled objects into it. Doing this means I don’t end up with Tiled container objects on my dashboard (or if any get added when legends/filters get automatically added, I just move any items I want to retain and then delete the Tiled container).
However, as Kyle says ‘no floating’, I will build adding to the ‘default’ dashboard which means there will be containers on there I don’t really want.
Now blogging about containers is usually very tricky as it’s hard to explain where things need to go. So I’ll be supplementing this with a lot of screen shots – fingers crossed following along works out ok!
To start, create a dashboard sheet and resize to 1200 x 900 as required. Observe the item hierarchy section of the Layout pane as this is where you’ll see all the containers and objects as we add them to the dashboard.
The main structure of the display is split into 2 columns, so start by adding a horizontal layout container to the dashboard. Once added, add 2 blank objects side by side to give the basic layout. Adding blank objects helps when positioning the required objects and is recommended when dealing with layout containers, especially if you’re new to them. They will ultimately be deleted as we go. Rename the horizontal container H – 2 cols or similar (right click on the container in the item hierarchy > rename).
Notice how a Tiled container has now also appeared on the dashboard, even though we only added a horizontal container.
The first column of the dashboard contains 2 charts – the Scatterplot and the Sales & Profit Ratio Comparison sheets – stacked on top of each other. For this, add a vertical layout container between the two blank objects. Rename this V – Col 1.
Add the Scatterplot sheet into the vertical container and then add Sales & Profit Comparison underneath it.
The various legends associated with these 2 sheets, automatically get added into their own vertical container on the right hand side. These aren’t required, so from the item hierarchy, select the Vertical container and then Remove from dashboard.
The right hand column of the display will show the Sales & Profit Ratio by Month sheet and another (hidden) chart that needs to be built.
Add another vertical container between the V – Col 1 container and the right hand blank object. Name this V – Col 2, and add the Sales & Profit Ratio by Month sheet and then another blank object underneath it. Once again remove the right hand vertical container that is automatically added with all the legends/filters.
Now we have the ‘core’ layout, the 2 blank objects we added to the horizontal container, H – 2 Cols, right at the start, can be removed, so hopefully you should have a layout organised as below.
Now add the dashboard title (Dashboard menu > Show Title, and then update the text). This will automatically add a vertical layout container around all the existing contents.
Building the Sales & Profit Ratio by Sub-Category bar chart
On a new sheet, add Sub-Category to Rows and Sales to Columns. Add Profit Ratio to Colour and adjust the colour legend to use the Red-Black Diverging colour palette. Hide the Sub-Category row label heading (right click > hide field labels for rows).
The bar chart needs to be filtered when a State in the Sales & Profit Ratio Comparison chart is clicked on, or when a Date is selected in the Sales & Profit Ratio by Month chart. However, I noticed when clicking around, that when clicking the Sales & Profit Ratio by Month chart, it filtered the above bar chart by both the State and Date. So based on this, create 3 parameters.
S&PR Comp State
String parameter defaulted to empty string
S&PR by Month State
String parameter defaulted to empty string
S&PR by Month Date
Date parameter defaulted to 01 Jan 1900 (essentially a null date)
Show these parameters on the sheet.
We want to filter the chart if the S&PR Comp State has a value and the S&PR by Month Date is the ‘null’ date (which means we’ve interacted with the Sales & Profit Ratio Comparison chart), or if the S&PR Monthly State has a value AND the S&PR by Month Date has a value (which means we’ve interacted with the Sales & Profit Ratio by Month chart). So create
Filter – S&PR by SubCat
([State Name] = [S&PR Comp State] AND ([S&PR by Month Date]=#1900-01-01#))
OR
(([State Name] = [S&PR by Month State]) AND (DATETRUNC(‘month’, [Order Date]) = DATETRUNC(‘month’, [S&PR by Month Date])))
Enter a State name into the S&PR Comp State parameter (eg New Jersey), then add the Filter – S&PR by SubCat field to the Filter shelf and set to True. The chart should change.
Verify the functionality by adding a state and date into the other parameters eg 01 March 2021 and Texas
Empty the state parameters and set the date back to 01 Jan 1900. Name the sheet Sales & Profit Ratio by SubCat. The chart contents will disappear.
Creating a dynamic title sheet
Originally I hoped to do this without using another sheet and just using the title of the bar chart, but I need the date to show nothing rather than Jan 1900 depending on the user interactivity, so a new sheet is required.
But for it, we need some additional calculated fields.
State for Title
IIF([S&PR by Month State]<>”,[S&PR by Month State], [S&PR Comp State])
We only want to show the name of the state once, and both parameters may have it set.
Date for Title
IF [S&PR by Month Date]=#1900-01-01# THEN ” ELSE DATENAME(‘month’,[S&PR by Month Date]) + ‘ ‘ + STR(YEAR([S&PR by Month Date])) END
Line
IF [S&PR by Month Date]<>#1900-01-01# THEN ‘|’ ELSE ” END
Add all 3 fields to the Detail shelf of a new sheet. Change the mark type to polygon. Update the sheet title as below
Name the sheet S&PR Title Sheet or similar
Adding the bar chart, title & legend to the dashboard
All 3 of these objects – the bar chart, the title sheet and the profit ratio legend need to show or hide based on interactivity. To do this in one step, we can encapsulate the 3 objects within containers within another ‘parent’ container and control the visibility on the ‘parent’ container.
Add a vertical container between the Sales & Profit Ratio by Month chart and the blank object. Name this V – S&PR SubCat Chart
Add the Sales & Profit Ratio by SubCat sheet into this. Then add another horizontal container and place it above the Sales & Profit Ratio by Sub Cat chart (making sure it’s within the V – S&PR Sub Cat Chart container. Rename this H – S&PR Sub Cat Title.
Add the S&PR by Title sheet into this horizontal container, and then click on the Profit Ratiolegend on the right hand side and move this object to sit to the right of the title sheet. Then click on the right hand column containing all the remaining legends, and delete this container from the dashboard. Then remove the blank object that’s sitting beneath the Sales & Profit Ratio by SubCat sheet. You should have something like below…
Adjust the width of the S&PR Title sheet so its wider. Set the sheet to Fit Entire View. Then select the H – S&PR SubCat Title container and edit the height to be 90 px.
Hide the title of the Sales &Profit Ratio by SubCat sheet.
Hiding and showing the Sales & Proft Ratio by Sub Category section
Create a new calculated field
Show S&PR by Sub Cat
[S&PR by Month State]<>” OR [S&PR Comp State]<>”
On the dashboard, select the V – S&PR SubCat Chart container and on the Layout pane, check the Control visibility using value checkbox, and select the Show S&PR by Sub Cat field. Assuming all the parameters are set to their default values, then the whole section should disappear, although the container will still be selected.
To make the section show, we need to set the parameters using dashboardparameter actions.
Set S&PR Comp State
On select of the Sales & Profit Ratio Comparison sheet, set the S&PR Comp State parameter passing in the value of the State Name field. When the selection is cleared, set the value back to <emptysrting>
Click on a row in the Sales & Profit Ratio Comparison bar chart, and the Sales & Profit Ratio by SubCat chart should display, filtered to that State, with the selected state name in the title.
Click the state again, and the chart disappears.
Create 2 further dashboard parameter actions
Set S&PR by Month State
On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month State parameter, passing in the value from the State Name field. When the selection is cleared, set it back to <emptystring>
Set S&PR by Month Date
On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month Date parameter, passing in the value from the Month([Order Date]) field. When the selection is cleared, set it back to 01/01/1900
Now click on a point in the line chart, and the Sales & Profit Ratio by SubCat chart should display filtered to the relevant state and month
Adding the Additional Interactivity
When the Scatterplot is clicked, the State in the existing ScatterplotState parameter should be updated. Create a dashboard parameter action
Set Scatterplot State
On select of the Scatterplot sheet, set the Scatterplot State parameter, passing in the value from the State field. When the selection is cleared, retain the value
If you click around the scatterplot, the Sales & Profit Ratio by Month line chart and Sales & Profit Ratio Comparison charts should update.
But we don’t want the other marks on the scatter plot to ‘fade’. To solve this, create a dashboard filter action.
Deselect Scatter marks
On select of the Scatterplot sheet on the dashboard, target the Scatterplot sheet directly, setting the fields TRUE = FALSE. On clearing the selection, show all values.
Finally, the last requirement is to highlight the line in the Sales & Profit Ratio by Month chart associated to the State selected in the Sales & Profit Ratio Comparison chart. For this first create a dashboard set action to capture the selected state
Add State to Set
On select of the Sales & Profit Ratio Comparison sheet, target the State Name Set. Check the single-select only checkbox. Running the action should Assign value to set and clearing the selection should remove all values from set
Then add a dashboard highlight action
Highlight Monthly Trend Chart
On select of the Sales & Profit Ration Comparison sheet, target the Sales & Profit Ratio by Month sheet targeting the State Name field only
And hopefully, with all this, you should have a fully interactive dashboard. My published viz is here.
It was Yusuke’s turn for this week’s #WOW2025 challenge, posing a twist on a the creation of a highlight table.
Whenever I start a challenge, I take note of what’s going on – I interact with it, move my mouse around to see if there’s any clues. The main takeaway from this, is that I’d need a dual axis so I could have multiple marks cards to style differently – one to be coloured based on the Profit and one to be coloured based on whether the cell was selected or not. So we need to build a table using an axis.
Building the basic table
Add Order Date to Columns and then click the pill to expand the date hierarchy so Year and Quarter are displayed. Add Sub-Category to Rows.
Double click into Columns and manually type MIN(1.0) to create an axis. Change the mark type to bar, increase the size to as large as possible, and edit the axis to be fixed from 0 to 1. Add Profit to Colour and Profit to Label. Adjust the colour scheme as required (I used red-white-blue diverging and reduced the opacity to 70%). Adjust the label font to be grey text.
Widen each row; shrink each column and adjust the row and column dividers to be dashed grey lines. Update the font of the label headings. Adjust Tooltip to suit.
Storing the selected cells
To store the cells that have been selected, we’re going to use Sets. To build this set, click on a cell in the table, and then in the toolbar of the tooltip that displays, click the venn diagram symbol to create set
Name the set Cells Highlighted
Highlighting the selected cells
Each cell in the table we have built is a bar of length 1. We want to use a dual axis to create bars of length 1 only in the cells selected. So we need
Bar Length – Highlighted Cells
IF [Cells Highlighted] THEN 1 ELSE 0 END
We also only want the profit value to be displayed for these cells
Profit – Highlighted
IF [Cells Highlighted] THEN [Profit] END
Add Bar Length – Highlighted Cells to Columns to make a second axis, and a second marks card. Remove both existing Profit pills from this card. and instead change the Colour to black at 100% opacity, and add Profit – Highlighted to Label. Change colour of the Label text, and update the axis to be fixed from 0 to 1.
Make the chart dual axis and synchronise the axis.
Hide the axis (uncheck show header), and hide the Order Date label (right click -> hide field labels for columns)
Update the title of the sheet with the instructions and then add the sheet to a dashboard.
Adding the interactivity
On click, we want to add the cell (if unselected) to the set. For this we need a dashboard set action
Add to highlight
On select, target the Cells Highlighted set, adding values to the set on click, and keeping set values when cleared.
We also want to remove selected cells via a menu option, so create another dashboard set action
Remove from highlight
Display on the menu of the tooltip, and target the set Cells Highlighted by removing values from the set when the menu option is clicked, and keep values when selection cleared.
While these give us the functionality we need, it isn’t the best user experience – we have to click multiple times to get the display due to the ‘default’ behaviour of selected marks being automatically highlighted / non selected marks being faded out.
To resolve this, we need to utilise a couple of techniques blogged about here.
For the marks coloured by profit, we want to disable highlighting using a dashboard filter action and the true/false method.
Create calculated fields called
True
TRUE
False
FALSE
and add to the Detail shelf of the MIN(1.0) marks card only. Then on the dashboard add a dashboard filter action
Now if you click on an unhighlighted cell it should go black immediately. However, if you click on one of the black already highlighted cells, the other cells still fade.
Now we can’t apply the same method to that cell, as we then lose the hyperlink appearing on the tooltip on click. This is because the true/false method ultimately results in the cell being immediately deselected once selected, so the ‘click’ action, which results in the menu option showing, is cleared .
Instead we will use the dashboard highlight action technique also described in the blog.
Create a new field
Dummy
‘Dummy’
Add this to the Detail shelf of the All marks card (or add to both the MIN(1.0) and the Bar Length – Highlighted Cells marks cards). Then create a dashboard highlight action that just targets the Dummy field only, which as it exists in all cells, essentially selects them all.
Highlighting the word ‘colour’ in the title
I just did this by floating a blank object over the text which I set the background colour to black, and then set the object to move backwards. This did mean once I published to Tableau Public, I had to edit the viz online to ensure the object lined up to where I wanted.
This week’s challenge by Lorna was to deliver some functionality without using LODs or table calculations. She hinted that parameters and parameter actions would be your friends.
Setting up the parameters
Create a parameter to capture the month selected
pMonth
date parameter defaulted to 01 April 2023
and then create one that will store the value associated to the month selected
pMonthSales
float parameter defaulted to 0
Building the Viz
On a new sheet add Order Date as a continuous (green) pill at the month-year level to Columns. Add Sales to Rows.
Create a new calculated field
Difference from Selected Sales
SUM([Sales]) – [pMonthSales]
and add this to Rows.
Change the mark type of this second marks card to bar and add Difference From Selected Sales to the Colour shelf. Adjust the colour to a diverging scale and centre at 0
Set the Size of the bars to be Manual rather than Fixed and adjust the slider to suit.
Add a reference line to the Order Date axis, that references the pMonth parameter.
Adjust the Tooltips, remove gridlines and add a title.
Adding the interactivity
Add the sheet to a dashboard. Add a dashboard parameter action
Set Date
On select of the viz, update the pMonth parameter with the value from the Month([Order Date]) field.
Add another dashboard action
Set Value
On select of the Viz, update the pMonthSales parameter with the value from the SUM(Sales) field that is aggregated at the SUM level.
Now if you click on a point on the line chart, the bottom bars should alter, but they’ll all appear ‘faded’ initially.
To resolve this, create a new calculated field
HL
“HL”
and add this to the Detail shelf of the All marks card. Then create a dashboard highlight action
Highlight
On select of the viz, highlight the HL field only
When you now click, the bottom marks aren’t faded as they essentially are all ‘highlighted’ too.
Sean set this challenge this week, to build a connected scatterplot to allow additional insights to be gained.
We need to show a circle per country related to the specified year, and then show the data for all years if a country is specified from the drop down, or ‘clicked on’ by the user; these data points are all then connected.
Let’s start by building up the various parameters and calculated fields needed to help with this.
Setting up the data
For the user inputs, I used parameters
pYear
Integer parameter, defaulted to 2000, displayed in a format so no thousand separators are shown. I populated the list using the values from the Year field.
pCountry
string parameter defaulted to All. I populated the list of entries by first adding values from the Country field. I then manually added an All entry to the bottom of the list and dragged it to the top. I could then set All as the default value.
On a new sheet, show these parameters.
We’re going to use a dual axis chart to display the viz, and for this, we’re going to get the relevant measures for the specific Year and for the specific Country.
To see what I’m aiming for, lets’ build out the data in a table. Add Country to Columns and Year to Rows. Display the values of Fertility Rate and Life Expectancy. This just gives us all the data points
But we only want the points related to the pYear (2000) or if pCountry if it’s not All (in this case Afghanistan).
So we create
Fertility Rate for Year
[Year] = [pYear] THEN [Fertility Rate] END
Life Expectancy for Year
IF [Year] = [pYear] THEN [Life Expectancy] END
format these to 1 dp and then add to the table. The fields only contain values for the specified pYear.
Create
Fertility Rate for Country
IF [Country] = [pCountry] THEN [Fertility Rate] END
Life Expectancy for Country
IF [Country] = [pCountry] THEN [Life Expectancy] END
format these to 1 dp and also add to the table. We now have these entries only existing for the selected pCountry.
If pCountry is set to All, the Fertility Rate for Country and Life Expectancy for Country are empty for every Country.
We now have the basics we need to build the viz.
Building the ScatterPlot
On a new sheet, show the parameters, then add Fertility Rate for Year to Columns and Life Expectancy for Year to Rows and Country to Detail. Change the mark type to circle. Adjust the Tooltip.
Create a new field
All Countries
[pCountry] = ‘All’
and add to the Colour shelf. Adjust the colours so when pCountry = All, the All Countries colour legend is True and displays a darker instance of a colour, as opposed to when pCountry is set to something else, and the All Countries colour legend is false.
Now add Fertility Rate for Country to Columns and Life Expectancy for Country to Rows. Change both fields to be Dimensions. On the 2nd marks card, add Year to Detail. and remove the All Countries field from the Colour shelf. Change the mark type to line and move Year to Path. Set the colour accordingly.
Then set both the Rows and Columns to be Dual Axis and synchronise both axis. Remove Measure Names from the colour shelf on the all marks card.
Adjust the Tooltip of the 2nd (line) marks card.
Add Year to the Label shelf of the 2nd marks card and update so it just displays for the Min & Max value. Adjust font size and style.
On the first marks card (the circle) add Country to Label and adjust so it only displays when selected.
Hide the right and top axis. Remove row & column dividers. Hide the null indicator and update the title of the axes. Name the sheet Scatterplot or similar.
Building the Dashboard
Add the sheet to a dashboard, and float the parameters into a suitable location. Add a floating text box that references the pYear parameter and position bottom left of the chart. Add a parameter action to update the pCountry parameter when a circle is clicked.
Click Country
On select of the Scatterplot Viz, set the pCountry parameter, passing in the value from the Country field. When cleared, set the parameter back to All.
Finally, if you click a circle to select a Country, you’ll find that the circles ‘fade out’ more than what you want – you want them to look the same as the colour when a country is selected via the dropdown. Essentially, you want all the circles to be ‘highlighted’ on click. To do this, create a new field
HL
“Highlight”
and add this to the Detail shelf on the scatterplot sheet. Then on the dashboard, add a Highlight action
HL marks on click
On select of the scatterplot viz, target itself, highlighting the HL selected field
For this week’s challenge, Erica wanted us to be able to set a discount value for a Sub-Category which once set, overwrote the value displayed in the table and applied the discount to the other visuals. She added an additional complexity to display the input field aligned with the selected Sub-Category. She alluded to the fact this last requirement was likely to be tricky, and she wasn’t wrong. I managed to build a solution, and I’ll walk through the principles, but there will be a bit of trial and error involved….
Building the table
We will need to capture the discount value to apply in a parameter, so create
pDiscount
integer parameter defaulted to 5
and we also need capture the Sub-Category to apply the discount to
pSubCat
string parameter defaulted to Art
The discount to display will need to be adjusted for the selected Sub-Category
Discount to Display
IF [Sub-Category] = [pSubCat] THEN [pDiscount]/100 ELSE [Discount] END
format this to % with 1 dp.
Add Category and Sub-Category to Rows. Double-click into rows and manually type in MIN(1). Change the mark type to shape and change the shape to be a transparent shape (see this blog for more details). Add Discount to Display to the Label shelf, and change the aggregation to Average. Align middle centre. You should see that the value associated to Art is 5%.
Note – you may be wondering why this is not being displayed in a standard table – why the need for the fake axis? I can’t recall exactly why I ended up with this, but it will have been borne out of later steps, and the need to try and get the input parameter aligned – by all means feel free to try without and see how it goes 🙂
Hide the MIN(1) axis, remove column dividers and gridlines / zero lines, axis rulers etc. Add subtotals (Analysis menu > Totals > Add all subtotals). Stop the Tooltip from displaying. Adjust the height and width of the cells so you can see all the rows on the screen. Show the parameters, and test the functionality by manually changing the parameters.
Create a new field
Index
INDEX()
Set this to be a discrete field and add to Rows after Sub-Category. Adjust the table calculation so it is set to compute using both Category and Sub-Category, and you show see the rows numbered from 1 to 17 (except for the total rows).
In order to help us position the input parameter, we will need to capture the index of the selected Sub-Category, so create a parameter
pIndex
integer parameter defaulted to 6 (the value associated to Art)
For now, we’ll leave the index displaying in the table. But we will hide it eventually. Name this sheet Table.
Building the line chart
The line chart needs to show the actual Sales and the sales as they would be if the revised discount was applied for the selected Sub-Category. The value stored in the Sales field will already account for the original value stored in the existing Discount field. So to work out what the adjusted Sales will be, we need to determine the full sale price (Sales / (1 – Discount)) and then apply the inputted discount value from pDiscount (multiply by (1- (pDiscount/100))
Adjusted Sales
IF [Sub-Category] = [pSubCat] THEN ([Sales] / (1-[Discount])) * (1- ([pDiscount]/100)) ELSE [Sales] END
format this and the Sales fields to $ with 0do
On a new sheet add Order Date at the continuous Month/Year level (green pill) to Columns. Add Sales to Rows, and then drag Adjusted Sales and drop it on the Sales axis when the green ‘2 column’ icon appears. This will automatically add Measure Values to Rows and Measure Names to Filter and Colour.
Calculate the difference as
Difference
IF [pSubCat]<>” THEN (SUM([Adjusted Sales]) -SUM([Sales])) / SUM([Sales]) END
and apply a custom number format of â–²0.00%;â–¼0.00%;0%
Format the date axis to display dates as custom format mmm yy, and edit the axis to change the title to Month.
Add Sales, Adjusted Sales and Difference to the Tooltip and update to suit. Show the pDiscount parameter and update it to a really large value, say 10,000. The 2 lines will show more prominently and the Sales axis will adjust its scale.
The requirement is to ensure the grey line (the original sales) doesn’t move, so edit the value axis, adjust the title to Sales and then fix the start from 0, but end ‘automatic’
This will push the Adjusted Sales off the chart. Reset the pDiscount to something more reasonable like 5.
Remove row/column dividers and gridlines, but retain axis rulers. Name the sheet Line.
Building the KPI card
On a new sheet, add Sales to Text. Change the Mark type to shape and select a transparent shape. Align the text middle centre, and set the display to Entire View.
We want to only show the Adjusted Sales if a Sub-Category has been selected, but we need to line chart to display a blue line all the time, so we need another field
Label Sales
IF [pSubCat]<>” THEN [Adjusted Sales] END
format this to $ with 0dp and add to the Label shelf.
Adjust the layout and display of the text as required. Hide the Tooltip. Name the sheet KPI.
Right, we’ve got the key components. Let’s get these all on a dashboard first.
Building the dashboard
Getting all the objects you want on the dashboard (including titles, footers etc) positioned exactly where you want them, with the appropriate padding set is crucial to getting the method I’m going to use to reposition the input parameter. It’s also quite fiddly and I can’t guarantee that even if you follow the steps, you’ll get things looking right…
Anyway, let’s start with the dashboard.
I set the dashboard size to 1100 x 650, then I added a floating vertical container which I positioned 0,0 and sized 1100 x 650. I formatted the dashboard and set the background colour to light grey.
I then switched to Tiled and added a text box for my title. I set the background of this to white, outer padding to 0 and inner padding to 5.
I then added another text box beneath for the instructions. I set the outer padding to 0 and inner padding to 5. I then fixed the height to 70.
Next I added a horizontal container beneath the instructions. I add a blank object to it as a placeholder. Ensuring the horizontal container was selected (blue border), I set the outer padding to 5.
I then added another horizontal container beneath this, and added my standard footer (created by, recreated by etc). I set the outer padding of this container to have 5px on the left and right, and 0 on top and bottom. All the text boxes within I set to have 0 outer padding and 0 inner padding.
I then added another text box beneath to add in the link to the challenge, also part of my ‘standard footer. I set the outer padding for this text box to 0.
I then calculated how high all the ‘rows’ of objects were on the dashboard (the height of the title + the height of the instructions + height of my standard footer and challenge link), and then subtracted this from 650. I then fixed the height of the central horizontal container based on this value
Add the Table sheet into the left side of this container. Remove the title. Set the background to white. Adjust the outer padding to 0. Set the sheet to Fit Width. Very carefully, adjust the width of a row, so the table fills as much of the vertical space as possible without there being a scroll bar. This is really fiddly to do.
The addition of the table will have automatically added some parameters and a Tiled object to the layout. We’ll deal with these shortly, but leave them be for now.
Add a Vertical container to the right hand side. Add the KPI sheet and then then Line sheet underneath. Remove the blank object that was the placeholder. Widen the vertical container so the vizzes have more space. For both the KPI and the Line objects, remove the title, set the background to white, set the outer padding to 0. Set the inner padding of the KPI chart to 20, and the inner padding of the line chart to 10. Adjust the height of the KPI chart so its visible.
If all is well, you should have something like
Adding the interactivity
Create a parameter action
Set Sub Cat
On select of the Table chart, set the pSubCat parameter, passing in the value from the Sub-Category field. Reset to ” when unselected.
Set Index
On select of the Table chart, set the pIndex parameter, passing in the value from the Index field aggregated to None. Reset to 0 when unselected.
If you click different Sub-Categorys, the KPI and line chart will change. Once unselected, no adjusted sales or discount will display.
Getting the parameter to move
Duplicate the Table sheet, and remove all subtotals. On this sheet, we’re only going to display the rows up to the row before the selected Sub–Category. For this we need
Show Top Rows
[pIndex]=0 OR [Index]<[pIndex]
Add this to the Filter shelf and set to True. Verify the table calculation is computing by both Category and Sub-Category. If need be adjust, then recheck the filter is just displaying True still. Show the pIndex parameter and just test the filter is working, by changing the value. Here, with the parameter set to 6, it is showing rows up to 5.
What we’re trying to do is just display the rows necessary so that the parameter input can be added beneath this sheet on the dashboard. The reason we have removed the subtotals, is that if the index is set to 2, we only want to display 1 row above; that for Bookcases. With subtotals included we’d get a row for Bookcases and a Total row. However once we get to a new Category as we have above, we need to show an additional row to accommodate for the subtotal displayed within the original table.
So we need to force additional rows to show in some circumstances but not others.
To help with this I have made use of the Region field. I checked that for two regions, Central & East, there were Sales in both Regions for every Sub-Category.
Add Region to the Filter shelf and set to Central & East only. Add Region to the Detail shelf. Remove Discountto Display from the Label shelf. Create
Extra Row
IF LAST()=0 THEN STR(ATTR([Region]) )
ELSE ''
END
Add this to Rows after Index. Adjust the table calculation so that it is computing by Sub-Category only. With pIndex set to 0, this should just display the two Regions against the last Sub-Categorys in each Category, simulating the subtotal rows.
But set the index to 6 and we just get the additional rows for the Furniture Category
and set to 2 and we just the row for Bookcases
Hide the Category column (uncheck show header). Name the sheet Top. Set the pIndex back to 0, and let’s start seeing how this works on the dashboard.
Add a vertical container between the existing table and the kpi/line chart. Reduce the width. Add the Top sheet into this container. Remove the title and set the inner and outer padding to 0. Set the sheet to fit width. If you’re lucky, all the rows should align. If not you may need to tweak again.
Select a Sub-Category in the original table, and the 2nd table should shrink.
From the Tiled section on the layout item hierarchy, navigate through until you find the pDiscount parameter. Click it to select it on the dashboard, then move that object to sit beneath the shortened table. Then select the Tiled section on the item hierarchy, and right click and remove from dashboard, which will remove all the unnecessary parameters/legends that were being displayed.
For the pDiscount object, remove the title, set the background to white and set the outer padding to 0. Set the background of the vertical container to white too. We only want this discount parameter to show when a Sub-Category has been selected. To manage this, we need
Show pDiscount
[pSubCat]<>”
Select the pDiscount object on the dashboard, and then from the Layout tab, check the Control visibility using value and choose the Show pDiscount field
Unselecting the Sub-Category and the field won’t display
So now we’ve got the basics of what we’re trying to do, we obviously don’t actually want any of the table to be visible. But if we hide all the fields (uncheck show header), we lose the column headings which was helping with the positioning, as we can see below – the input box is no longer aligned with Art.
To fix this, create a new field
Dummy Header
”
and add to the Columns of the Top sheet. If you haven’t already, uncheck show header against all the other blue pills (Category, Sub-Category, Index and Extra Row). The sheet should look like below, and what the Dummy Header has done is create an extra spacing at the bottom of the page, which compensates for the heading we don’t have at the top… and this is the reason for creating a table using a fake axis 🙂
Back on the dashboard, everything is aligned again.
… except when we select Bookcases … argghhh!
Add a blank object above the parameter. Set the padding to 0, and adjust the height to about 18 px – enough to bring the parameter in line. Create a new field
Show Blank
[pIndex]=1
and use this to control the visibility of the blank object – ie we only want the blank object to come into play when Bookcases is selected, and nothing else.
Click around every Sub-Category and hopefully the parameter box is aligned each time.
Final touches
On the Top sheet, remove row dividers, so the sheet just always looks empty.
On the Table sheet, hide the Index field (uncheck show header).
Add left outer padding of 10px to the vertical container that contains the Top sheet and the pDiscount parameter. This should mean some grey spacing appears between the table and the input field.
Adjust the width of the objects to suit BUT DON’T fiddle with the heights at all!
To stop the other discounts from ‘fading’ when a Sub-Category is clicked, create a new field
HL
‘HL’
and add to the Detail shelf on the Table sheet. Then on the dashboard, add a highlight dashboard action that on select of the Table sheet, targets the Table sheet with the HL field only. This essentially has the effect of highlighting all the fields, since the HL field is applicable to every row.
Phew! This took some time and a lot of fiddling to get right, and even then I know there’s every chance that you can’t quite get things to align just right, or publishing to Tableau Public and it all seems to shift … My published viz is here. Fingers crossed you’re successful!
For this week’s challenge, Sean Miller introduced multiple ways to get insight from a stacked bar chart. I managed this using 5 sheets and 1 dashboard.
Preparing the data
As the requirement stated only 2024 was to be considered, I chose to add a data source filter (right click data source -> Add Data Source Filter) where the Order Date Year = 2024.
Option 1 : The Analytics Pane
On a new sheet, add Order Date to Columns as a discrete month (blue pill) and Sales to Rows. Format Sales to $ with 0 dp. Change the mark type to bar and add Ship Mode to the Colour shelf.
Format MONTH(Order Date) to display the dates as Abbreviation. Manually move Ship Mode = Same Day in the colour legend so it is listed first. Hide the Order Date heading (right click -> hide field labels for columns).
Add a reference line (right click Sales axis -> add reference line) that sets a reference line per cell to the sum of Sales and displays the Value on the label.
Format the reference line (right click on one of the lines) and align the label top centre. Adjust the Tooltip if required. Add a white border around the bars (via the colour shelf).
Right click on the Ship Mode pill on the Colour shelf and check the Show Highlighter option to display the highlight input box. Test that selecting an option in the highlight box shows a recalculated reference line.
Name this sheet Option 1 or similar.
Option 2 – Dual Axis
Duplicate the Option 1 sheet and rename to Option 2 or similar.
We will capture the selected ship mode in a parameter.
pShipModeSelected
string parameter defaulted to empty string.
Show the parameter and then enter the text ‘First Class’
Create a new calculated field
Selected Ship Mode Sales
IF [Ship Mode] = [pShipModeSelected] THEN [Sales] END
format this to $ with 0dp.
Add Selected Ship Mode Sales to Rows. Change the mark type on the associated marks card to line and remove Ship Mode from the Colour shelf. Adjust the colour of the line to a dark grey/black and show mark labels.
Make the chart dual axis and synchronise the axis. Hide the right hand axis (uncheck show header) and remove column and row dividers.
Change the text in the parameter to Same Day. You should now get a broken line. Right click on the Selected Ship Mode Sales pill and format. Set the marks for special values to Hide (Connect Lines).
Option 3 – Dynamic Stacks
Duplicate the Option1 sheet again and rename to Option3. Show the pShipModeSelected parameter and enter text ‘Same Day’.
Create a new calculated field
Sort
IIF([Ship Mode]=[pShipModeSelected],1,0)
and drag it into the ‘dimension’ section of the data pane (above the line).
Right click on the Ship Mode pill on the Colour shelf and select Sort.. Change the Sort By option to Field ascending and Field Name = Sort. This will push the bars related to Same Day to the bottom of the stacked bar.
Building the Ship Mode Selector
On a new sheet, add Ship Mode to Columns and then double click into Columns and manually type MIN(1). Change the mark type to bar and edit the MIN(1) axis to fix it from 0 to 1.
Add Ship Mode to Colour and to Label (you may have to widen the bars to make the label visible). Align the label middle centre and bold the font. Adjust the size to the largest possible.
Hide the axis and the Ship Mode headings (uncheck show header). Remove all column/row dividers. Hide the Tooltip from displaying. Name the sheet accordingly.
Building the Navigation selector
I chose to add all the sheets onto a single dashboard (rather than separate dashboards), so created a navigation sheet.
To help with this, I basically utilised the Segment field that wasn’t being used, and essentially translated the values to repurpose them for the navigation options.
Navigation
CASE [Segment] WHEN ‘Consumer’ THEN ‘Option 1: The Analytics Pane’ WHEN ‘Corporate’ THEN ‘Option 2: Dual Axis’ ELSE ‘Option 3: Dynamic Stacks’ END
Add this field to Columns and type in a MIN(1) in Columns too. Change mark type to bar, fix the axis from 0-1. Make the Size as large as possible. Add Navigation to Label and align middle centre and set the font to white. Adjust the column divider to be a thick white line and remove row divider. Hide the axis and the Navigation headings.
Create a new parameter to capture the navigation selection.
pSelectedDisplay
string parameter defaulted to : Option 1: The Analytics Pane
Show the parameter. Create a new field
Is Selected Display
[Navigation] = [pSelectedDisplay]
and add to the Colour shelf. Adjust to suit. Adjust Tooltip as required and rename the sheet.
Building the Dashboard
Create a dashboard and arrange all the objects on the dashboard, with the different options placed above each other. Use containers if need be. You’ll have something like this – it’ll look a little messy but don’t worry.
We’ll be using Dynamic Zone Visibility to control which object displays based on which option from the Navigation sheet is selected.
First, let’s set the interactivity to control the navigation selection. Add a parameter action
Select Display
on select of the Navigation sheet, set the pSelectedDisplay parameter, passing in the value from the Navigation field. Keep current value when deselected.
Clicking on the different options in the Navigation control will now change the parameter value, but this won’t do anything yet. We need several calculated fields
Option 1 Selected
[pSelectedDisplay] = ‘Option 1: The Analytics Pane’
Option 2 Selected
[pSelectedDisplay] = ‘Option 2: Dual Axis’
Option 3 Selected
[pSelectedDisplay] = ‘Option 3: Dynamic Stacks’
Option 2 or 3 Selected
[pSelectedDisplay] <> ‘Option 1: The Analytics Pane’
All of these fields will return True if the condition is met, so we can use these to control which objects display.
Back on the dashboard, select the Highlight Ship Mode object, and from the Layout pane, check the Control visibility using value and choose the Option 1 Selected field.
Select the Option1 bar chart and apply the same settings.
Now select the Ship Mode Selection sheet, but this time, choose the Option 2 or 3 Selected field to control visibility. It’s likely this field will now disappear.
Select the Option2 bar chart and choose Option 2 Selected field. This will disappear.
Select the Option3 bar chart and choose Option 3 Selected field. This will disappear.
Now click on the different options in the Navigation control and the different charts should display.
(Note – I actually chose to contain the highlight selector and the option1 bar chart within their own layout container, which meant I could then just apply the setting to control visibility of the layout container rather than the individual objects).
Ensure either Option2 or 3 on the navigation bar is selected so the Ship Mode selector is displayed. Create another parameter action
Set Ship Mode
On select of the Option 2 & 3 Selector sheet, set the pShipModeSelected parameter, passing in the value from the Ship Mode field. When clearing the selection, set the value to <empty string>.
Clicking on a ship mode should now display the line or reorder the stack depending what Option you were on.
In clicking the nav or the ship mode selector, you will probably have noticed that the other options become ‘greyed out’ or ‘faded’ To stop this from happening, use a highlight dashboard action.
Create a new field, mine happened to be
True
TRUE
but it could just as easily be named anything containing any string. Add this field to the Detail shelf of the Navigation sheet and the Ship Mode Selection sheet.
Back on the dashboard create a new highlight dashboard action
Deselect Ship Mode Selector
On select of the Option 2&3 Selector sheet, target itself but using the selected field of True.
Create another highlight dashboard action apply the same principals for the Navigation sheet (for more information and worked examples on ‘deselecting marks’, see this blog.
And hopefully you should now have a working solution. My published workbook is here.
This week, Kyle set the challenge of recreating area charts combined with a tile map / small multiple. He’d been inspired by a viz he uses at work, which he then realised had actually been inspired by a previous #WorkoutWednesday challenge from 2020 which I actually blogged about here.
So some of the steps for this solution guide, I’ll lift from my existing blog :-), but there are no table calcs in this instance.
Building the basic tile map
As per Kyle’s instructions, I started by building a new field that I could then format to millions
Pop
[Population (Population)] * 1000
I formatted this to be a custom number with 2 dp and displayed with Millions unit.
Put this into a table where State and Date on Rows and Pop on Text, just so we can validate what we’re up to…
We need to standardise/normalise the display, so the population for each State is ranging from 0 to 1. For this we need to determine
Min Pop Per State
{FIXED [State]:MIN([Pop])}
Max Pop Per State
{FIXED [State]:MAX([Pop])}
and we can then work out
Standardised Pop
(SUM([Pop]) – SUM([Min Pop per State]))/(SUM([Max Pop per State]) – SUM([Min Pop per State]))
Format this to display as a number with 2 dp. Add all 3 fields to the table. For each state the Standardised Pop should have a year when then value is 0 (equivalent to when the population is lowest for the state) and a year when the value is 1 (equivalent to when the population is highest for the state).
For the tile map, we also need fields Rows and Cols which are calculations that map each State to a number (and can be copied straight from the challenge sheet).
On a new sheet, add Rows to Rows (as a discrete dimension) and Cols to Columns (also as a discrete dimension). Add State to Detail. Your initial ‘map’ layout should start to take shape.
Add Date to Columns and set to be a the continuous Year level (green pill) and add Standardised Pop to Rows. Set the display to Entire View, so all the tiles are visible.
Change mark type to Area and add Pop to the Tooltip. Update the Tooltip as required.
Adding the State label and max value
For this we’re going to plot a single point that will be at the centre of the Date axis and slightly higher than 1. For this we need
This looks a bit complex, so I’ll break it down. What we’re doing is finding the number of years between the minimum year in the whole data set (1900) and the maximum year in the data set (2023). This is
We’re then halving this value ( /2) and rounding it down to the nearest whole number (FLOOR).
We then add this number of years to the minimum date in the data set (DATEADD), to get our central year – 1961.
Now we need a point where we can plot a mark against that date. It needs to be above the maximum value in the area chart (which is 1). Based on what I did before, I decided 1.75 worked
Plot State Label
IF YEAR([Date]) = YEAR([Centre Date]) THEN 1.75 END
Add Plot State Label to Rows between Rows and Standardised Pop. Change the mark type of this axis to shape and use a transparent shape (see here for info). Note you can use a circle and reduce size to smallest and opacity to 0% if you wish. However, this will show a small dot when hovering, which you don’t get with a transparent shape.
Add Max Pop per State to Label and change State from Detail to Label. Adjust the Label accordingly
Remove all text from the Tooltip dialog of the Plot State Label marks card, and hide the Nulls indicator label. Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.
Then remove all gridlines, zero lines, axis lines and row/column dividers. Hide all the axis and the Cols and Rows pills (uncheck show header).
Building the bar chart
Create a new field
Latest Pop per State
IF [Date] = {FIXED [State]:MAX([Date])} THEN [Pop] END
If the date is the maximum date for the state, then get the population. Format this to be 2dp in Millions.
Add State to Rows and Latest Pop for State to Columns and sort descending. Adjust the colour to suit. Show mark labels, and remove all gridlines etc and row/column dividers and hide the axis. Adjust the Tooltip.
Creating the highlight action
Add the two sheets to a dashboard. Add a dashboard highlight action
Highlight State
On hover of the bar chart, target the map, via the State field only.
I referenced this Tableau blog post and downloaded the HexmapPlots excel file included. I then used a relationship to ‘join’ the Sample – Superstore excel file I was using with the HexmapPlots file, joining on State/Province= State
Since I had the other blog post already open, I then followed the steps included to start building the map.
Building the hex map
Add Column to Columns and aggregate to AVG, and add Row to Rows and also aggregate to AVG. Add State/Province to Detail. Edit the Rows axis and set to be Reversed.
Note – it’s possible you may have extra States showing. As I’m writing I’ve realised I’m rebuilding against an extracted data source that has a filter I originally applied as a global filter, which has now been included in the extract. So you may need to add State/Province to the Filter shelf, and set to exclude NULL and District of Colombia. This filter will need to be applied to all sheets you build.
Change the mark type to Shape and select a Hex shape. I already have a palette full of Hex shapes, but the blog post provides a shape to use and add as a custom shape if you haven’t got one. Increase the size of the marks.
Create a new field
Profit Ratio
SUM([Profit]) / Sum([Sales])
format this to % with 1 dp, and then add to the Colour shelf.
Now add a second instance of Row to the Rows shelf and set to AVG again. Set the mark type of this marks card to Map. Remove the Profit Ratio field from Colour on this card too. Assuming your Map location is set to USA, you should have State outlines depicted (Map -> Edit Location).
Set the chart to be dual axis and synchronise the axis. The State shape axis should now be inverted. Independently adjust the sizes of the Hex shape and the state shapes, so the states sit inside the hexagons.
Right click on the right axis and move marks to back. The adjust the Colour of the hex shapes so its around 85% transparent.
Now adjust the Tooltip on the hex marks to match the requirement.
To fill-out the available area, I also chose to fix both the axis (right click axis -> edit axis). The Column x-axis I set to range from 1 – 12, and the Row y-axis, I set to range from -0.9 – 9. Then hide all axis, and remove all row/column gridlines, divider lines, axis lines and zero lines. Hide the 10 unknown indicator, and set the background colour of the whole worksheet to a pale grey.
Building the Line Chart
This is super simple, Tableau 101 🙂
Add Order Date to Columns and set to be a continuous month (green pill showing month-year). Add Sales to Rows. Change the colour of the line to grey. Hide the x-axis and remove all gridlines, dividers, axis lines and zero lines. Set the background colour of the worksheet to None (ie transparent). Update the tooltip.
Building the Scatter Plot
Add Sales to Columns, and Profit to Rows. Add State/Province to Detail shelf and add Profit Ratio to Colour. Change the mark type to circle. Remove all gridlines, row column dividers and axis rulers. Only the zero lines should remain. Adjust the tooltip and set the worksheet background to None (transparent).
Putting it all together
Create a dashboard and set the size as stated. Set the background of the dashboard to pale grey (Dashboard – > Format).
Add the Hex map, and hide the title. Click on the Profit Ratio legend object and set to be floating. Then remove the right hand vertical container. Move the Profit Ratio legend to a suitable location.
Then add a text box as a floating object and use it to create the title. Add both the trend line and the scatter plot charts as floating objects without titles. Just position them as required. You can always use gridlines (Dashboard -> Show Grid) to help you line things up.
Finally add the interactivity.
Add a highlight dashboard action which highlights the hex map and the scatter plot when either of the other is selected ‘on hover’, and just targets the State/Province field.
Then add a Filter action which on hover of the Trend chart, targets the remaining charts.
And hopefully that’s it. My published viz is here.
Donna + DataViz 