Kyle set the challenge this week to recreate a drill-down, but with the stipulation that no parameters were to be used. I immediately figured this would be a challenge requiring set actions, and indeed the hint on the splash page of the #WOW site, confirmed this
Building the Viz
After connecting to the data source, create a Set off of the Category field (right click > create > Set). Select a single option eg Technology
Category Set
Create fields
Display Value
IF [Category Set] THEN [Sub-Category] ELSE [Category] END
and
Expand Indicator
IF [Category Set] THEN [Category] ELSE ‘+’ END
Add Category, Expand Indicator and Display Value to Rows and Sales to Columns and press the sort desc button in the toolbar, to sort all the bars . The click the Category pill to add another sort by sales descending.
Hide the Category pill (uncheck show header).
Format the Expand Indicator column, so the text is aligned vertically
Right click on the Expand Indicator text heading displayed in the view and hide field labels for rows. Widen each row a bit, remove all gridlines, remove the Sales axis title (right click axis > edit axis). Add a title to the sheet. Adjust the Tooltip.
Adding the interactivity
Add the sheet to a dashboard, then add a dashboard set action
Select Cat
On select of the viz on the dashboard, target the Category Set, adding values to the set when the viz is clicked (selected), and remove all values when the selection is cleared. Only allow 1 selection at a time to be made.
Erica set the #WOW2024 challenge this week, asking us to recreate this ‘drillable’ functionality within a single dashboard.
To achieve this, I used 4 sheets
A large map
A small map
A table of data
A navigational control
along with parameter actions to drive the interactivity, and dynamic zone visibility to control what displayed when.
Building the large map
On a new sheet, double click on State/Province to automatically generate a map of the United States – if this doesn’t display by default, then make sure the location of your map settings are referencing the USA (Map menu > Edit Locations). Hide the ‘unknown’ indicator displayed bottom right. Change the mark type to map to create a filled map. Change the colour to a grey (I used #c0c0c0). Add State/Province to the Filter shelf and select Arkansas. Show the filter.
Click on the Tooltip button, and delete all the text in the dialog.
Now drag City onto the map and drop it on the section that appears labelled Add a Marks Layer. On the ‘City’ marks card that now appears, change the mark type to circle. Move City to the Label shelf and then also add State/Province to Detail. Change the colour of the circles to dark grey (I used #767f8b).
Update the Tooltip on the City marks card. Turn off all the map options to stop the pan/zoom map control options from appearing (map menu -> map options -> deselect all the options). Remove all row/column dividers.
Name the sheet Map-Large or similar.
Building the Small Map
Duplicate the Map-Large sheet, and name the new sheet Map-Small. Remove all background layers so just the state outline remains (Map menu -> background layers -> set washout to 100%).
On the Label shelf of the City marks card, uncheck show mark layers so the name of the city is not displayed and click on the Tooltip shelf and uncheck show tooltips, so they aren’t displayed either.
On this map, we need to highlight a selected City. For this we need a parameter to capture the City, and a calculated field to identify which City has been selected. We will also need a parameter to capture the State/Province to ensure the interactivity works as required.
pSelectedState
string parameters defaulted to ” (ie empty string)
pSelectedCity
string parameter defaulted to ” (ie empty string)
Is Selected City
[City] = [pSelectedCity]
Add Is Selected City to the Colour shelf of the City marks card.
Show the pSelectedCity parameter and type in ‘Fayetteville’. The colour legend should display values for True & False. Adjust colours to suit.
Also add Is Selected City to the Size shelf and adjust the sizes accordingly.
Building the table
On a new sheet, add
State/Province to Filter and filter to ‘Arkansas’
Add Is Selected City to Filter and filter to True
Add Customer Name, Order ID and Product Name to Rows
Right-click on the State/Province field in the Filter shelf and apply to worksheets > selected worksheets and select the Large Map & Small Map sheets. This makes the filter shared between all those sheets.
Create a new field
Unit Price
SUM([Sales])/SUM([Quantity])
and add to Text.
Drag Quantity onto the canvas and drop on the ‘unit price’ column when you see show me appear. This will automatically add Measure Values to Columns and Measure Names to Filter.
Do the same for Sales. Reorder the pills in the MeasureValues box so they are arranged as required.
Add subtotals (Analysis menu > Totals > Add all subtotals), then display at the top (Analysis menu > Totals > Column Totals to Top).
Right-click on the Customer Name pill in Rows and uncheck Subtotals from the context menu.
Format the table: set the worksheet background colour to light grey and set the total (pane & header) background colour to a shade darker.
Adjust the width and height of the columns and rows to suit.
Uncheck show tooltip from the Tooltip shelf, and name the sheet Table or similar.
Building the Navigation sheet
The ‘back to main map’ navigational display is actually a worksheet rather than a navigational object on a dashboard, as we need to apply parameter actions to it in order to reset some parameters.
On a new sheet, double click into the space on the marks card below the shelf buttons, and type ‘X’
Change the mark type to Text and move the X field to the Text shelf. Update the text to include the additional words, adjust the font size and then align middle centre. Uncheck Show tooltip.
Create the following fields
City – Reset
”
State – Reset
”
and add both to the Detail shelf. Name the sheet Navigation or similar.
Building the dashboard
Using horizontal and vertical layout containers, arrange all the objects on a dashboard. You will need to adjust the background colour of some objects and the padding to get everything looking as expected. This can be quite tricky and is very hard to explain in a blog. The item hierarchy in the image below will help give you an idea
To draw horizontal or vertical lines, add a blank object to the layout container, change the background colour, reduce the padding to 0 and then adjust the height or width of the object so it is very narrow.
These are the properties associated with the red ‘tab’ in the heading – the background colour is red, inner and outer padding is 0, width is 4px and the object is contained within a horizontal container
For the City/State title, reference the pSelectedState and pSelectedCity parameters, along with a unicode symbol which I copy and past from here.
Adding the interactivity
Add the following dashboard actions
Set State
on select of the Map-Large sheet, set the pSelectedState parameter passing in the value of the State/Province field. When the selection is cleared, retain that value.
Set City
on select of the Map-Large sheet, set the pSelectedCity parameter passing in the value of the City field. When the selection is cleared, retain that value.
Reset State
On select of the Navigation sheet, set the pSelectedState parameter passing in the value of the State – Reset field. When the selection is cleared, retain that value.
Reset City
On select of the Navigation sheet, set the pSelectedCity parameter passing in the value of the City- Reset field. When the selection is cleared, retain that value.
Click around on the dashboard and sense check the parameters appear to be behaving – ie if you click on a city on the large map sheet, the table and small map should update, and if you then click the navigation sheet, the table shouldn’t show anything, and the small map shouldn’t show anything highlighted.
Hide and show the objects
Finally, we need to only show the required objects depending on the actions taken. For this we need some additional calculated boolean fields. Go back to any sheet, and create the following calculated fields
City is Selected
[pSelectedCity]<>”
City Not Selected
[pSelectedCity]=”
Note, these fields just have to exist, they don’t need adding to any sheet.
Navigate back to the dashboard.
Click on the Map-Large object, and on the Layout tab, check the control visibility using value option, and choose the City Not Selected value.
This means that when City Not Selected is true (ie the pSelectedCity parameter is empty), this object will display. When City Not Selected is false (ie the pSelectedCity parameter has a value), this object will be hidden.
Depending on what you’ve been clicking, this object might disappear immediately.
Click on the Table object, and do the same steps, but this time choose the City is Selected field. This means the table will only show then the pSelectedCity parameter has a value.
Apply the same settings so the small map, the table title, and the navigation sheet only show when City is Selected. Note – depending on how you have built the dashboard, you may find you can apply the setting once against a container which contains all the objectsyou want to hiderather than against each individual object.
I also chose to only display the Select a State filter control when the City Not Selected was true.
And hopefully, that should be it. My published viz is here.
Global Recognition month continued this week for #WOW2023, and I was able to enlist Norbert Borbas to set the challenge this week, which was published in both Norbert’s native Hungarian, as well as English.
Norbert provided a challenge based on a solution he had implemented at his company, and involved the creation of 2 dashboards with interactivity between them both. There’s a fair amount going on with this one, so let’s get cracking.
Building the Sales KPI
For this viz, we need to get information about the latest year sales in conjunction with the previous year. Rather than hardcode any years relating to the data, I created
Latest Year
{MAX(YEAR([Order Date]))}
which for the data set I was using, returns 2022. Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line).
With this I then created
LY Sales
IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END
to the sales for 2022. Format this to $ with 0 dp.
To get the sale for the previous year (ie 2021) I created
Previous Year
[Latest Year]-1
Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line), and then create
PY Sales
IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END
Format this to $ with 0 dp.
We then needed the % difference between these values
The line chart needs to change based on whether the Sales KPI or the Profit KPI sheet has been selected. We need a parameter to capture this ‘decision’.
pMeasureToShow
string parameter defaulted to Sales
To the determine which actual value to display we need
Line – Measure to Display
IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END
format this to $ with 0dp.
I also created
Line – Measure to Display Axis (k)
IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END
ie the same field, but this was formatted to $ with 0 dp but display units of Thousands (k).
Having the two fields means that the axis can display in one format while the tooltip can show the more detailed value.
On a new sheet add Order Date to Columns and change to the discrete ‘month’ level (blue pill – May). Add Line – Measure to Display Axis (k) to Rows. Add Order Date to Colour. This will default to the Year level, and show all years from the data set, but we only want the latest 2 years. So create
Filter Years
YEAR([Order Date]) >= [Previous Year]
and add to the Filter shelf and set to True. Adjust colours to suit.
Add Order Date to the Label shelf and change to the Year level. By default the lines should be labelled. Edit the label and set the font to match mark colour. I also set the font to be Tableau Medium and bold. Adjust the order of the years in the colour legend, so 2022 is listed first which makes the line for 2022 sit ‘on top’ of the 2021 line.
Remove all gridlines/row column dividers, and set the axis lines to be bolder. Hide the Order Date label (right click > hide field labels for columns). Adjust the formatting of the Order Date axis, to display the months in an abbreviated form. Adjust the title of the y-axis to reference the pMeasureToShow parameter (right click the axis > edit).
Add Line – Measure to Display to the Tooltip shelf.
Adjust the Tooltip to display as
Finally, to help with the interactivity later, we will need
Month Order Date
DATEPART(‘month’, [Order Date])
This returns the number of the month ie 1 for January, 2 for February etc. Move this to the ‘dimensions’ section of the left hand data pane (drag above the line), and then add this to the Detail shelf. Change the field to be a discrete attribute
Name the sheet Line Chart.
Building the Symbol Chart
On a new sheet add Filter Years to the Filter shelf and set to True. Add Order Date to Columns and change to be at the discrete month level. Double click into the Rows shelf and manually type in MIN(0). Add Month Order Date to the Detail shelf.
We need to display coloured arrows depending on whether the change is up or down. For this we need
Symbol – Difference to Display is +ve
IF [pMeasureToShow] = ‘Sales’ THEN IIF([% Diff Sales From PY]>=0,TRUE,FALSE) ELSE IIF([% Diff Profit From PY]>=0,TRUE,FALSE) END
If the measure to display is Sales, and the difference in Sales from previous year is +ve, then return true, otherwise false, Else if the measure to display is Profit and the difference in Profit from the previous year is +ve, then return true, else false.
Change the mark type to Shape and then add this field to both the Colour shelf and the Shape shelf. Adjust colours and shapes accordingly.
Edit the axis and delete the title and set the major and minor tick marks to None. We need the axis to remain as we will need to ‘line up’ this chart with the line chart, and having a left hand axis will help.
Hide the months from showing (uncheck show header against the pill on Columns. Hide all gridlines, axis lines, zero lines & row/column dividers.
Name the sheet Symbol Chart.
Building the Main dashboard
Using horizontal and vertical layout containers, position the sheets in the required locations along with the title and the instructional text. Use background colours and inner & outer padding to give space between the objects.
For the line chart and symbol chart, these were placed in a vertical container, and the width of the ‘blank’ y-axis on the symbol chart widened to be in line with the axis on the line chart. The hierarchy of objects I used is pictured.
To make the Sales display on the line chart when the Sales KPI sheet is clicked, create a dashboard parameter action
Show Sales Line
On select of the Sales KPI sheet, set the pMeasureToShow parameter, passing in the value from the Sales Label field. When the selection is cleared, keep the parameter set to the current value.
And create a similar action to show the profit
Show Profit Line
On select of the Profit KPI sheet, set the pMeasureToShow parameter, passing in the value from the Profit Label field. When the selection is cleared, keep the parameter set to the current value.
We will need to return to this later, to add more interactivity, but for now we’ll move onto the analysis/drill down sheet.
Building the drill down table
We’re going to need a few more fields to build this type of display. Firstly, for the first bar chart column we need
Bar – LY Measure to Display
IF [pMeasureToShow] = ‘Sales’ THEN [LY Sales] ELSE [LY Profit] END
Bar – PY Measure to Display
IF [pMeasureToShow] = ‘Sales’ THEN [PY Sales] ELSE [PY Profit] END
Format both these fields to $ with 0dp.
Add State/Province to Rows and Bar – LY Measure to Display to Columns. Sort the states by the measure descending. Adjust the colour of the bar to suit. Add Bar – PY Measure to Display to the Detail shelf and add a reference line per cell that displays the average of this measure.
Show mark labels and adjust the font of the labels to be size 8pt. Widen each row, and align the State labels to the left, and change the font to be bold & black. Reduce the Size of the bars. Remove gridlines, but add row dividers.
Double click into the columns shelf and manually type MIN(0) to create a ‘fake’ axis and generate a MIN(0) marks card.
Create a new field
Measure Rank LY
RANK(SUM([Bar – LY Measure to Display]))
and add this to the Label shelf of the MIN(0) marks card. Adjust the table calculation so it is explicitly set to Compute by State/Province. Remove the Bar – PY Measure to Display field from the Detail shelf. Change the mark type to shape.
To determine what type of shape and what colour to apply, we need
Measure Rank PY
RANK(SUM([Bar -PY Measure to Display]))
and then
Measure Rank Change
IF [Measure Rank LY] < [Measure Rank PY] THEN ‘Up’ ELSEIF [Measure Rank LY] > [Measure Rank PY] THEN ‘Down’ ELSEIF ISNULL([Measure Rank LY]) THEN NULL ELSE ‘N/A’ END
Add this field to both the Shape and the Colour shelf, and adjust the table calculation so it is explicitly set to Compute by State/Province.
Adjust the shapes, and use a transparent shape against the Null option (see here for details). Adjust colours to suit. Increase the size of the shape, and align the label to the left.
For the next column, create
Bar – Measure Difference
SUM([Bar – LY Measure to Display]) – SUM([Bar -PY Measure to Display])
and custom format to +”$”#,##0;-“$”#,##0
Add to the Columns shelf, and labels should automatically get added.
Create a field
Bar – Measure Diff is +ve
[Bar – Measure Difference] >=0
and add to the Colour shelf of this marks card. Adjust colours to suit.
For the final column, we need to separately identify the values when the YoY measure difference is positive from those that are negative, and then apply ranking to each of these fields. So we need
+ve Measure Diff
IF [Bar – Measure Diff is +ve] THEN [Bar – Measure Difference] END
-ve Measure Diff
IF NOT([Bar – Measure Diff is +ve]) THEN [Bar – Measure Difference]*-1 END
Note, as the difference in this instance is negative, the values returned will also be negative, but when it comes to ranking, we want the record with the biggest negative difference to be ranked 1st ie if one value had a difference of -10 and another had a value of -100, in typical ranking, -10 is ‘higher’ than -100, so -10 would be ranked 1 and -100 2. But we want -100 to be ranked 1. So by multiplying the values by -1 in the calculation we actually return values 10 and 100. So when we rank them later, 100 is ranked 1 as it is bigger than 10.
Ranke +ve Measure Diff
RANK_UNIQUE([+ve Measure Diff])
Rank -ve Measure Diff
RANK_UNIQUE([-ve Measure Diff])
We will be displaying the information for the positive and negative ranks in separate ‘columns’ which we can do with
Rank YoY X-axis
IIF([Bar – Measure Diff is +ve], 1,2)
Add this field to columns and change the mark type to Circle. Add Bar – Measure Diff is +ve to Colour. Add Rank +ve Measure Diff and Rank -ve Measure Diff to Label. Ensure the table calculations for both fields are explicitly set to Compute by State/Province. Increase the Size of the circle, and align the label to be middle centre using a bold white font.
Now we have all the information displayed, we need to sort the tooltips.
This sheet, will be accessed through interaction and will be ‘filtered’ to just a specific month. For now, we’ll ‘hardcode’ the month by adding Month Order Date to the Filter shelf and selecting 3 (for March).
On the All marks card, add Latest Year, Previous Year,Month Order Date, Measure Rank PY to the Tooltip shelf.
We will also need
TOOLTIP – Rank statement decrease
IF [Measure Rank Change] <> ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank decreased vs. same month last year.’ END
TOOLTIP – Rank statement increase
IF [Measure Rank Change] = ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank increased vs. same month last year.’ END
TOOLTIP – Rank YoY Statement Negative
IF NOT([Bar – Measure Diff is +ve]) THEN MIN([State/Province]) + ‘ is a negative (Rank: ‘ + STR([Rank -ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END
TOOLTIP – Rank YoY Statement Positive
IF [Bar – Measure Diff is +ve] THEN MIN([State/Province]) + ‘ is a positive (Rank: ‘ + STR([Rank +ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END
Add all 4 of these fields to the Tooltip shelf of the All marks card. Ensure all the table calculation fields are set to explicitly Compute by State/Province.
Now adjust the tooltip with all the relevant fields, applying colouring as required
Hide the axis and hide the null indicator. Hide the State/Province column label heading. Finally, remove the Month Order Date field from the Filter shelf. The tooltip will look a bit funny at this point, but that will get sorted later.
Name the sheet Drill Down Table.
Building the drill down dashboard
Again using vertical and horizontal containers, arrange the sheet on a dashboard along with the title. Use text boxes arranged in a horizontal container directly above the Drill Down Table sheet to display the column headings.
As I didn’t want to hardcode any years, I created the following parameters
pLatestYear
integer parameter defaulted to 2022 and with a display format that did not include thousand separators.
and
pPreviousYear
integer parameter defaulted to 2021 and with a display format that did not include thousand separators.
and
pMonth
integer parameter defaulted to 3 and with a display format that did not include thousand separators.
When building the column headings, I referenced all these parameters instead.
To ‘set’ these parameters, I added Previous Year and Latest Year to the Detail shelf of both the Line Chart and Symbol Chart sheets.
I then added 3 dashboard parameter actions to the main dashboard which on select of the Line Chart or Symbol Chart sheet, set the relevant parameter with the value from the appropriate field.
To ensure the drill down gets ‘filtered’ to the month selected on the main dashboard, add a dashboard filter action
Drill down
On select of the Line Chart or the Symbol Chart, target the Drill Down Table sheet on the Drill Down dashboard, passing the selected field of MONTH(Order Date) only. Exclude all values when selection is cleared.
The final step is to add a Navigation Button to the drill down dashboard which displays the text ‘Go back to landing page’ and navigates back to the main dashboard.
And hopefully, with all that, you have a completed interactive navigational dashboard! My published version is here.
Retro month continues, with Kyle setting this challenge to recreate Andy Kriebel’s WOW challenge from 2017. A lot has moved on with the product since 2017 and this is a great example of how it can be simplified.
I completed the original challenge (see here) and was having a look to refresh myself… boy! it took a LOT more effort – sometimes I surprise myself that I managed it!
Now that we have parameters and parameter actions, the solution is WAY more simpler.
So let’s crack on…
As alluded to above, we’re going to need a parameter which is going to store the name of the state ‘on click’
pSelectedState
string parameter defaulted to ” (ie empty string)
We also need to display either the name of a State or a City dependent on the value of this parameter
Display Name
IF [pSelectedState] = ” THEN [State] ELSE [City] END
Pop these into a tabular view with Sales and Profit and show the pSelectedState parameter so we can test things out.
When the pSelectedState is empty, a row is displayed per State
but when pSelectedState contains the name of a State (or any text to be honest), a row is displayed per City (note all Cities are displayed, at this point, not just those for the State).
To restrict the list of Cities just to those that match the State in the pSelectedState parameter, we need
Records to Filter
[pSelectedState] = ” OR [pSelectedState] = [State]
Add this to the Filter shelf and set to True. Now the list should be restricted to the Cities in the State.
So lets’ start to build the basic viz.
Set the pSelectedState parameter to empty, then add Sales to Columns, Profit to Rows and Display Name to Text. Add Records to Filter To Filter and set to True. Change the mark type to Circle.
Create a new field
Profit Ratio
SUM([Profit]) / SUM([Sales])
format to % with 1 dp and then add this to the Colour shelf.
Add this sheet to a dashboard, then add a dashboard parameter action
Set State
on select of a mark on the scatter plot chart, set the pSelectedState parameter with the value from the Display Name field.
If we now click on a state, the cities should be displayed instead – great! But if we now click a city, we don’t get what we want – boo! This is because the selection of a City has passed the name of the City which is stored in the Display Name field into the parameter, so the scatter is trying to display records relating to a State = City Name which doesn’t exist.
To resolve this, we need to pass a different field into the parameter action
Drill Value
IF [pSelectedState] = ” THEN [State] ELSE ” END
Add this to the Detail shelf of the Scatter plot viz, then update the dashboard action to pass this field into the pSelectedState parameter instead
Reset the pSelectedState parameter to empty string, and then test again – clicking on a state and then clicking on a city should get you back to the states.
And that’s the core functionality achieved with 1 parameter, and 2 calculated fields!
We just need some additional fields to provide the relevant display for the title & sub title
Title text
IF [pSelectedState] = ” THEN ‘by State’ ELSE ‘for ‘ + [pSelectedState] END
Subtitle
IF [pSelectedState] = ” THEN ‘Click a State to drill down to City level’ ELSE ‘Click a City to drill up to State level’ END
Add these to the Detail shelf of the scatter viz, and then update the title of the sheet to reference the fields
Update the Tooltip and adjust the size of the axis fonts, and tidy up the dashboard layout, and you should be good to go!. My published viz is here.
Erica provided a set action based challenge this week requiring us to drill down to 2 levels in a hierarchy. I know there’s been similar challenges to this in the past, but I resisted checking them out, and attempted to build from memory. I succeeded – yay! Let’s crack on…
Note – I used the v2022.1 Superstore data that I already had on my laptop so filtered to December 2022. I also connected to the provided tds file rather than the excel instance, as for some reason, the tds includes Manufacturer which the excel file doesn’t.
Creating the main viz
On a new sheet, add Category to Rows and Sales to Columns and display as a bar chart. Add Order Date to Filter and restrict to the latest month/year in the data you have (in my case Dec 2022).
Create a set based off of Category (right click the Category pill > create set) and select a single value (eg Furniture) to be in the set. A field called Category Set should now exist in the data pane.
What we need to do is create a 2nd level in the hierarchy that shows the sub-categories related to the Category in the set, otherwise just display the Category.
2nd Level
IF [Category Set] THEN [Sub-Category] ELSE [Category] END
Add 2nd Level to Rows. Then right click on the Category Set field in the data pane and select Show Set to display the set values in a control on the sheet.
If you manually change the options in the Category Set control, the values displayed in the 2nd Level column will change.
Change the sort of the 2nd Level pill to sort by Sales descending
Create a set based off the 2nd Level field (right click field > create set), and select a single option that is one of the Furniture sub categories (eg Bookcases). A field called 2nd Level Set will be added to the data pane.
What we now need to do is create a 3rd level in the hierarchy that either shows the Category, the Sub-Category or the Manufacturer depending on the options selected in the sets. This is the only field which will actually be displayed on screen.
Display
IF [Category Set] AND NOT([2nd Level Set]) THEN ‘- ‘ + [Sub-Category] ELSEIF [Category Set] AND [2nd Level Set] THEN ‘ — ‘ + [Manufacturer] ELSE [Category] END
If we have a value selected in the Category Set but no value selected in the 2nd Level Set then display the Sub-Category (with some additional text formatting to give the indented effect).
Else, if we have a value selected in the Category Set and a value selected in the 2nd Level Set, then display the Manufacturer (again with additional text formatting and spacing).
Otherwise, display the Category.
Add Display onto the Rows, and then right click 2nd Level Set and select Show Set. Apply a sort to the Display pill so it’s also sorting by Sales descending.
Test the functionality, by changing the options in the set controls. For representative testing, ensure only 1 option maximum is selected in each set, and if an option in the 2nd Level Set is chosen, ensure it’s a Sub-Category related to the value selected in the Category Set.
Once happy, revert the options back to Furniture and Bookcases.
Format the sheet, and adjust the Row Divider so the slider is at the 2nd mark, and set the Column Divider to none.
Remove all gridlines. Then uncheck Show Header against the Category and 2nd Level pills in Rows. And finally, click on the Display title at the top of the column and Hide field labels for Rows.
To colour the bars, we’re going to use similar logic to that used to determine what to display
Colour Bar
IF [Category Set] AND NOT([2nd Level Set]) THEN ‘Sub Category’ ELSEIF [Category Set] AND [2nd Level Set] THEN ‘Manufacturer’ ELSE ‘Category’ END
Add this to the Colour shelf and adjust the colours. Update the tooltip and widen the rows slightly.
We’ve got the viz, now to add the interactivity so the changes happen ‘on click’ rather than via the set controls we’ve displayed.
Adding the interactivity
Create a dashboard and add the viz. Uncheck the options selected in both sets, so no options are selected – this should collapse the viz to just show the 3 Category values. Delete the container that shows the set controls/colour legend.
Create a new set action as below
Category Selected
on select (single selection only) assign the values to the Category Set, and remove all values when selection is cleared.
Create a 2nd set action
SubCat Selected
on select (single selection only) assign the values to the 2nd Level Set, and remove all values when selection is cleared.
Start clicking around, and BOOM! you should get the desired behaviour (note, once you reach the Manufacturer level, you’ll need to click twice on a manufacturer to get the viz to completely reset.
Bonus – building the ‘header’
I deduced from hovering over Erica’s solution that the ‘header’ was made up of multiple sheets, all aligned in a single row in the dashboard. I did have a play to see if I could come up with a single-sheet option, and while I think there is an alternative, it wouldn’t necessarily display exactly as Erica had shown. So I went down the multiple sheet option too.
Firstly we need a sheet to simply display the text >> Category.
On a new sheet, double click in the space below the Detail shelf on the marks card and type the text ‘dummy’. Then drag the ‘dummy’ pill onto the Label shelf, which will automatically change to now be labelled Text. Click on the Text shelf, and the 3dots to open the Edit Label dialog.
Delete the ‘dummy’ field, and replace it with ˃˃ Category. HOWEVER the ˃ symbol isn’t the > character I typed from keyboard. I found the double >> wasn’t retained this way, which I’m putting down to some HTML/XML related encoding that isn’t being handled. Instead I copied the symbol from this page https://www.compart.com/en/unicode/U+02C3 and pasted it into the text window. I then formatted the font size and style and coloured it the relevant blue.
Next we want to create a sheet showing the Category selected. Click on your viz to add a Category to the set (eg click the Furniture bar).
On a new sheet, add Category Set to the Filter shelf (this by default filter to values IN the set). Then add Category to Text and update the Text field as below, adjusting the font size/colour etc as before.
Duplicate this sheet as we can use this as the basis of the >>Sub-Category display.
On the duplicated sheet, update the text field to contain ˃˃ Sub-Category, where again the > is the character pasted. Format/colour the text
Now click on your viz to add a Sub-Category into the 2nd Level Set (eg click the Chairs bar).
On a new sheet, add 2nd Level Set to the Filter shelf (this by default filter to values IN the set). Then add 2nd Level to Text and update the Text field as below, adjusting the font size/colour etc as before.
Finally, once again, duplicate this sheet. Then on the duplicated sheet replace the text with >> Manufacturer instead
Now you have all the components you need. Add all these sheets onto your dashboard using a horizontal container to keep them all together. Apply the grey background shading if required (you’ll need to format the background colour on each worksheet, as well as the various objects on the dashboard.
Lorna’s final #WOW challenge of 2021 was a parameter action based challenge, where the line chart at the bottom reflects the choices made by interacting with the bar chart at the top.
When I saw this was a drill down challenge, I immediately thought of previous similar challenges, so built based on the techniques I’d applied before (see this blog). This involved a parameter to capture the ‘level’ and a ‘drill down’ calculated field to pass through into the parameter on click. The solution I built (here) worked fine on Desktop (see tweet), but when published to Tableau Public, failed to display the Sub-Categories on selection :-(. I don’t know why. Having checked Lorna’s solution after, I realised I’d over complicated my solution, and had no need for the ‘level’ or ‘drill down’ fields. So I rebuilt to see if that fixed my issue with Tableau Public, and it did. So that is the solution I’ll blog about.
As stated in the requirements, I used Superstore v2021.3, but my numbers didn’t match Lorna’s. This isn’t an issue, but explains why my solution looks different from Lorna’s if you’re comparing. Having spoken to Lorna, we assume she didn’t use that version after all, but can’t recall what she may have used instead.
Anyway, onto the build.
First up, we need to define 2 parameters
pSelectedCategory
A string parameter defaulted to ‘nothing’ / empty string. This will be used to store the Category that the user clicks on in the bar chart.
pSelectedSubCat
Another string parameter defaulted to ‘nothing’. This will be used to store the Sub-Category that the clicks on in the bar chart.
We can’t just display the Sub-Category field in the bar chart, as it should only display a value ‘on click’. So we need a calculated field to store the value that needs to be displayed in the 2nd column of the bar chart – ie ‘nothing’ or the Sub-Category.
Sub Cat Display
IF [pSelectedCategory]=[Category] THEN [Sub-Category] ELSE ” END
If there’s a value in the pSelectedCategory, then display the Sub-Categorys, otherwise display a blank.
Build out the bar chart by adding Category and Sub Cat Display to Rows, and Sales to Columns. Order both Category & Sub Cat Display descending. Add Category to Colour and adjust. Label the marks, align the headings to the left, remove gridlines, hide the axis and column headings. If you show the parameter you can ‘type in’ a Category and see how the view looks.
On a new sheet, build out the line chart by adding Order Date to Columns set to the continuous (green pill) month format, and Sales on Rows. Label the max & min marks and remove all gridlines.
Create a new field which will be used to colour the line
Colour : Line
[pSelectedCategory]
Add this to the Colour shelf.
Show the pSelectedCategory parameter on the sheet and as you type in each Category value the colour legend will change. Adjust the colour for each value you type in, and for the empty value.
The line chart needs to change based on the selections made by the user, ie the values set in the parameters. A calculated field is required
FILTER
([pSelectedCategory]=”) OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=”) OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=[Sub-Category])
This is a boolean field. Add to the Filter shelf and set to True.
Finally, just to make things a bit more complete (and help me check the line chart was matching the selections), I created some additional fields to present on the Tooltip
TOOLTIP : Category
IF [pSelectedCategory]=” THEN ‘All’ ELSE [pSelectedCategory] END
TOOLTIP: SubCat
IF [pSelectedSubCat]=” THEN ‘All’ ELSE [pSelectedSubCat] END
I added these to the Tooltip shelf and amended the text as below
The final step is to place the two charts on a dashboard and then create 2 parameter actions, one which passes Category into the pSelectedCategory field on select. Clearing the selection needed to reset the value to ‘blank’.
The other parameter action needs to do similar, but set the pSelectedSubCat parameter from the Sub Cat Display field.
Candra set the challenge this week to use the new map layers to build a map display which, on click of a country, filtered the display to that country and additionally displayed a donut chart indicating the percentage of urban dwellers in that country.
If map layers are very new to you, then the webinar by Adam McCann referenced in the challenge, has some VERY useful pointers for this challenge (the workbook for that can be downloaded from here, as I found some things needed closer inspection).
I think this challenge is going to be best described by walking through the steps.
Building the 1st map layer
Double-Click on Country/Region to load a map, and change the mark type to (filled) Map. Add Region to the Colour shelf and assign the appropriate colours. Adjust the map background via the Map -> Map Layers menu and set the Style to dark, and remove all the selections against the Map Layers list
The intention is when a country is selected the map will ‘drill into’ /filter that country, and display additional information. We will drive this by a parameter, which will get set via a parameter action, but for now we’ll manually set the value.
Create a new string parameter, that is default to nothing/empty string/ ”, and then show this on your sheet.
pSelectedCountry
We need the 1st layer of the map to display, when there is no value in the parameter. We need a calculated field to help drive this.
All Countries
IF [pSelectedCountry]=” THEN [Country/Region] END
By default this will create a field of type ‘string’ but we need it to be a geographic data type. so change this as below.
Add this field to the Detail shelf of the map, and remove the Country/Region field that was automatically added when we first built the map.
Enter the name of a country, eg China, into the parameter. The map should essentially go blank (black screen).
Building the 2nd map layer
We need the country to display, if it’s entered into the parameter. For this, we need another calculated field
Selected Country
IF [pSelectedCountry]=[Country/Region] THEN [Country/Region] END
Once again change this to a geographic role data type of type County/Region.
Next click and drag this field onto the map, and drop it onto the Add Marks Layer option that displays. This will create a new Selected Country marks card, although nothing will obviously change on the map display itself.
Move the Selected Country pill to be on the Detail shelf instead, and add Region onto the Colour shelf. Change the mark type from circle to Map.
Now if you enter a county into the parameter, eg China, the display should ‘filter’ & ‘zoom in’ on China.
So what we have is the 1st layer only showing when no countries have been selected, and vice versa, the 2nd layer only showing when a country has been selected.
We need to now add further layers for the donut chart, which only want to show the country has been selected as well.
Building the 3rd map layer
A donut chart, in the past, is traditionally created by building a pie chart, then using a dual axis to add a circle, sized smaller that the pie chart, on top (see this Tableau KB for info). Rather than a dual axis, we’re going to use map layers – 1 layer for the pie chart, and then another layer for the central circle.
Keeping a country selected (so we can see what we’re building), drag Selected Country onto the map again to create another map layer. Change the mark type to Pie and increase the Size to as large as possible. Move Selected Country to Detail.
In the data set we have a field called Population Urban which stores the ‘percentage’ value of urban dwellers eg 0.17 is 17%. To create the angles for the pie chart, we need to know
Population Non-Urban
1-[Population Urban]
Drag Measure Values onto the Angle shelf. This will automatically add Measure Names to the Filter shelf. Edit the filter to just select the Population Urban and Population Non-Urban measures. Drag the Measure Names field that was also automatically added to the Detail shelf, to the Colour shelf. Adjust colours accordingly, and set the border of the pie chart to white (under the Colour shelf options).
Verify that if you set the parameter to empty again, the whole world map displays, and you can’t see any pie charts.
Building the 4th map layer
Now we need to make the donut hole. Once again, ensure a country is selected, so your pie chart is visible, then drag Selected Country onto the map again, and drop to add another map layer.
This time, move the Selected Country field onto the Detail shelf, add Region to the Colour shelf, and adjust the size of the circle, so its smaller than the pie. Set the border of the circle to be white again too,
Add Population Urban onto the Label shelf, and format to a percentage with 0 dp. The best way to do this, is to format the Population Urban measure in the data pane (right click->default properties -> number format).
At this point you’ll notice the number is huge… we need to add Year to the Filter shelf, and select 2012.
Align the label to be middle centre, and adjust the font to be much bigger text. Add ‘urban dwellers’ underneath.
Once again, verify you get the expected behaviour as you change the values in the parameter from nothing to Russia or China etc.
The final step on this sheet is to add text to the Tooltips. Unlike when working with dual axis, you don’t have an All marks card, so you’ll need to add the required fields (Country/Region, Region, Population Total (formatted to Millions with 0dp), Population Urban to the Tooltip shelf on each of the relevant layers.
Setting the parameter interactively
Create a dashboard sheet, and add the map sheet you’ve built. Then create a dashboard action which sets the pSelectedCountry parameter, impacting the All Countries field, and that when the selection is cleared, the value is reset to ”.
This week the challenge focuses on 3 techniques for enhancing a tabular display
Drill Down / Expand to show the breakdown by Year
Custom Header to control sorting
Page Control
I managed this challenge, but it wasn’t without help – I did my research using my friend, Google, and found tutorials to help, so this blog is briefer as you’ll need to read the blogs I reference too 🙂
Drill Down / Expand
There was a similar challenge last year which I blogged about here… or rather I referenced Rosario Guana’s excellent blog here.
The slight difference between this and the current challenge is that on expand/drill down, the Season is being shown in a separate column, rather than within the same one as above. The need for ‘data duplication’ which is referenced in Rosario’s blog isn’t required in this case. The key fields I used to resolve this part of the challenge are
pSelected Player ID
An integer parameter, defaulted to 0
pLevel
An integer parameter, defaulted to 2
Max Level
IIF([pLevel]=1,2,1)
If pLevel is 1 then we’ve already ‘drilled down’, so the max level on display is 2, otherwise we’ve yet to drill down, so the max level on display is 1.
DD Level
IIF([Max Level]=2 AND [Player Id]=[pSelected Player ID],2,1)
If we’ve already drilled down somewhere and you’re on the player we drilled down to, then the drill down level is 2, otherwise it’s 1.
Player ID Arrow
IF [pSelected Player ID] = [Player Id] AND [Max Level]= 2 THEN ‘▼’ ELSE ‘►’ END
If we’ve already drilled down somewhere and you’re on the player we drilled down to, then we want to display the down arrow. I use this site to get the geometric shapes required.
Season Display
IF [pSelected Player ID] = [Player Id] AND [Max Level]=2 THEN STR([Season]) ELSE ” END
If we’ve already drilled down somewhere and you’re on the player we drilled down to, then we want to display the Season otherwise it needs to be blank.
Player ID Display
Player ID
This is just a duplicate field.
The fields are then arranged along with the measures as below
Player ID is hidden (it will used for the sort later). DD Level is added to the Detail shelf, so it can be referenced by the parameter actions.
NOTE – I had to ask as it wasn’t obvious to me, but the Carries measure is essentially the number of records or count of the table, so Avg YPC becomes SUM([Yards])/[Carries].
To complete the drill down, when added to the dashboard, the following parameter actions are required
Selected Player
passes the value from the Player ID field into the pSelected Player ID parameter
Set Level
Passes the DD Level field into the pLevel parameter
For the Measure Rows field referenced, I used the Position field as a field in the data set that contained at least 3 values :
IF [Position] = ‘FB’ THEN -1 ELSEIF [Position] = ‘H’ THEN 0 ELSEIF [Position] = ‘P’ THEN 1 END
I created parameters
pHeader MeasureNames (which is the equivalent of MN Parameter referenced in the blog).
and pHeader Position which is the equivalent of the Category Parameter mentioned
The parameter actions were then set as
Header – Set Sort Measure
passes Measure Names to the pHeader MeasureNames parameter
and
Header – Set Sort Direction
passes the Position field into the pHeader Position parameter
When defining the other variables referenced in the blog, I had an issue with the Measure1 TF field; my version looks like
IF [pHeader MeasureNames ] = ‘Measure1’ AND [pHeader Position] = [Position] THEN TRUE ELSE FALSE END
For the sorting, I created
Sort Measure Value
CASE [pHeader MeasureNames ] WHEN ‘Measure1’ THEN [Carries] WHEN ‘Measure2’ THEN SUM([Yards]) WHEN ‘Measure3’ THEN [Avg YPC] WHEN ‘Measure4’ THEN SUM([TDs]) END
and then Sort
IF [pHeader Position] = ‘P’ //up arrow selected, sort ascending THEN -1 * [Sort Measure Value] ELSE [Sort Measure Value] END
Note – I chose my up and down arrows to behave different from how they are described in the blog. To me clicking ‘up’ suggests a sort ‘upwards’ ie ascending from small to large.
The Sort field is then used to apply the sort to the Player ID field on the table
The exception, was at Step 5 Build the page navigator, I simply created a new sheet called ‘Curr Page’. This referenced 2 calculated fields
Current Page No
[pPage No]
where pPage No is the equivalent of the page number parameter referenced in the blog.
Total Pages
FLOOR({COUNTD([Player Id])}/[pRows Per Page])+1
where pRows Per Page is the equivalent of the rows to show parameter referenced in the blog.
The only other slight addition required to this challenge, was to reset the page control to page 1 if the sorting was changed. This was done by creating a new calculated field
First Page No
1
adding this field to the Detail shelf on the header/sort controls sheet, then adding a parameter action
Reset to Page 1
which passes the First Page No to the pPage No parameter
Fingers crossed, I think I’ve covered the main points for this challenge and highlighted where the blogs might differ. My published viz is here.
This has given some great ideas of how to ‘pimp’ a table. I highly recommend you check out some of Tessellation’s other blog posts on tables :
For week 30, #WorkoutWednesday alumni Emma Whyte returned re-posting this challenge which was originally set in Week 41 of 2017 (see here). The idea behind this was to see how the challenge could be achieved using features that have been released since that challenge – in this case set actions.
I’ve been doing the #WorkoutWednesday challenges since they were first introduced, so I completed the original challenge, which is posted here.
Despite it being over 2 1/2 years ago, I had a strong recollection as to what was required to achieve this. So the challenge I set myself, was to recreate without looking at my own solution.
Building out the data
This is one of those challenges where we can build the data out into a table to check the functionality before building the actual viz. I always like to do this where possible, as I find it a good reference to make sure I’m getting the logic & the calculated fields I need right.
Start by adding State & City to Rows and add Sales & Profit via Measure Names on Columns .
As the challenge is to use Set Actions, then naturally, we’re going to need a Set. The Set we need is based on State with the idea being that when there is a State(s) in the set, then the City will display instead.
Selected State
Right click on State and Create -> Set. Select an option in the dialog, eg Alabama say
We will need to show the marks based on State or City depending on whether a State has been selected or not. We need a single field that we will use in the viz that displays the dimension we need to show
Display Value
IF [Selected State] THEN [City] ELSE [State] END
Add this onto the Rows and you’ll see how this is working
We can test the functionality of putting values into and out of the set without the need for the dashboard action at this point, by right-clicking on Selected State and selecting Show Set – the list of set values to select will display (a bit like a filter list).
We need a way to figure out what rows to show – how to identify whether there’s anything selected in the set.
Count States Selected
{FIXED : COUNTD(IF [Selected State] THEN [State] END)}
By being an LOD, this will set the count of the items in the set across all the rows in the data. Add to the sheet so you can see how this works
So we want to show information when either there isn’t anything in the set, or for the rows associated to the Selected States only
Records to Show
[Count States Selected] = 0 OR [Selected State]
Add this to Rows and test out… with no State in the set, all the rows are True
but with a State selected, only the rows associated to that State are True
But we seem to have too many marks showing when there’s nothing in the set….?
That’s fine.. just take City out of the view now, and if you deselect all States you should get the 48 rows we’re going to start with listed, and all are Records to Show = True. The Sales & Profit values will also now be aggregated to the appropriate level.
Building the Viz
Ensure your Selected States set is empty, and build out the scatter plot
Profit on Columns
Sales on Rows
Display Value on Detail & Label
Records to Show on Filter set to True
Mark Type = Shape set to x
Verify the functionality by clicking a State in the list, and the view should change to show the City.
We need to colour the marks based on Profit
-ve Profit?
SUM([Profit])<0
Add this to the Colour shelf and adjust colours accordingly.
Finally we need to look at how the title/subtitle changes based on which level we’re at.
Title
IF [Count States Selected] = 0 THEN ‘Sales vs. Profit by State’ ELSE ‘Sales vs. Profit for ‘ + [State] END
Subtitle
IF [Count States Selected] = 0 THEN ‘Select a state to drill down to city level’ ELSE ‘Double-click a city to drill up to state level’ END
Add these onto the Detail shelf, then they’ll be available to reference in the Title of the sheet.
And then adjust the Tooltip, and we’re pretty much ready to go.
Adding the Set Action
Create a dashboard and add the scatter plot sheet to it.
Add a dashboard action to Change Set Values which runs on the Select action, and assigns values to the Selected State. On clearing the selection, values are removed from the set.
And that should pretty much be it. My published version is here. I thoroughly enjoyed the ‘throwback’ to previous challenges, and would like to see this theme continue on occasion.
The final guest #WorkoutWednesday challenge for August was set by zen master, ambassador and fellow #WorkoutWednesday enthusiast Rosairo Gauna.
The challenge is here and built on a challenge set earlier in the year by Luke Stanke which involved Set Actions. Rosario was challenging us to use Parameter Actions instead to navigate the hierarchy (they weren’t around when Luke set his challenge), as well as not duplicate the data set.
I have to say, this really stumped me. Huge kudos to Rosario for managing to figure out how to use Parameter Actions in this way.
I had to reference Rosario’s own blog post to get to the bottom of this, so I won’t be documenting this myself.
This is going to be one of those challenges that demonstrates a technique that if I ever want to use again, I’ll be referencing this post (well Rosario’s post via this post), rather than remembering all the steps/calculations… a bit like I do when I have a need to build a trellis chart or if I ever had to build a radial chart again – I have my ‘go to’ reminder posts.
Donna + DataViz 