Can you build a trellis of Profit Ratio Gauges?

Donna Coles3 Comments

It’s Community Month at #WOW2024 HQ, and for the first challenge, Jack Hineman (a long time participant of WOW) asked us to create gauge charts using map layers. Not only that, he wanted them displayed in a trellis format, with a specific requirement to ensure the number of columns was always >= number of rows displayed. Errr…..

This was tough! I tried to start the challenge one evening and after pouring through Ken Flerlages’s blog post that was referenced, reading the hints, and looking at Jessica Moon’s Tableau Public page referenced, I was none the wiser. The blog post did not mention map layers at all in building a gauge, and was purely mentioned as inspiration for the design of the gauge.

I happen to have some time off work, so reattempted the challenge the next day. After several hours, I got there, somehow! I did another google : “Gauge charts in Tableau” and hit upon this blog, which gave me a few pointers, although most of the time it really was a lot of trial and error based on Jack’s hints, and to be honest I surprised myself that I actually hit all the requirements, except one, without the need to look at the solution.

The one thing I had to look at was the calculations required to get the trellis to always have more columns than rows. My ‘go to’ formula didn’t work. More on that later.

As for my solution… well it’s a solution…. how elegant/efficient it is – who knows. It was something built very much in stages as I tried to get my head around what was being asked. As I rebuild as part of the process I go through when writing this blog, I may find ways of improving what I did to start with. I will do my best to explain what I think is going on, what my thought process was, but apologise in advance if you get to the end of all this, and still don’t have a ‘scooby’ 😦

Strap yourself in! This is going to be a long one!!!!

Modelling the data

Connect to the provided data source and create relationship calculations to relate Manufacturers to Gauge_Definition with 1=1 and Gauge_Definition to Gauge_Points with 1=1 as well.

Understanding the data

Let’s start by looking at the data provided. Jack provided 3 data sets:

Manufacturers

A simplified instance of Superstore just listing Manufacturers with Sales & Profit data.

Gauge Definition

A data set of 1 row essentially containing some ‘constants’ to be referenced within the challenge

  • Gauge Success Amount = 0.15
    • Profit Ratios >= to 0.15 (15%) are deemed successful. This is essentially the Goal indicator value.
  • Gauge Concern Amount = 0
    • Profit Ratios >=0 (but < 0.15) are deemed a concern
  • Gauge Start Profit Ratio = -0.3
    • The left hand point on the semi-circular gauge should indicate a -30% profit ratio
  • Gauge End Profit Ration = 0.3
    • The right hand point on the semi-circular gauge should indicate a 30% profit ratio
  • Gauge Success Pct of Gauge = 0.75
    • 15% Profit Ratio represents 75% of the gauge displayed (ie 0% of gauge = -30% Profit Ratio and 100% of gauge = 30% profit ratio)
  • Gauge Concern Pct of Gauge = 0.5
    • 0% Profit Ratio represents 50% of the gauge displayed.

Gauge Points

This is essentially a template/scaffold to help build the gauge and the various features on the gauge. It defines all the points that need to be plotted and in most cases then connected to create the various ‘shapes’ displayed eg the gauge semi circle for the actual profit ratio and the legend indicator; the small angled rectangle that represents the goal ‘reference line’; the positions for the 3 labels.

To start getting an understanding, let’s just focus on Point Type = Actual and Point Segment ID = Background

This is the data to build the complete grey semi circle of the main gauge. 50 rows represent points on the inner arc of the semi circle (Point Arc = In). They all have a Point Radius = 0.43 (ie the distance from the 0,0 centre position of a circle to the bottom edge of the gauge is 0.43). The other 50 rows represent points on the outer arc of the semi circle (Point Arc = Out). They all have a Point Radius = 0.58 (ie the distance from the 0,0 centre position of a circle to the top edge of the gauge is 0.58). Point Angle Rads defines the angle in radians (rather than degrees) from the circle centre to edge of the circle. The Point ID defines the order to ‘join the dots’ when the points are made into a polygon.

Here’s a diagram to help try and explain the maths we’re going to need to use based on the data we have

For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. We know the angle, and we know the radius, so we can use trigonometry to work that out, and then use the MAKEPOINT() function in Tableau to covert that into a spatial/geometric field to use on a map.

Let’s do this in Tableau.

Create field

X

[Point Radius] *SIN([Point Angle Rads])

Y

[Point Radius] * COS([Point Angle Rads])

Note – based on my diagram above, X = a and would be derived from the Cosine of the angle, while Y = o and be based on the Sine of the angle. However, Jack gave hints based on the above calcs which hold true if you adjust the diagram and assume the angle is positioned between the y-axis and the radius, rather than the x-axis and the radius.

Now create the point

Geo

MAKEPOINT([X],[Y])

On a sheet, add Point Type to Filter and set to Actual, and add Point Segment ID to Filter and set to Background.

Double click on Geo to automatically add Longitude and Latitude fields to the sheet.

We have the basics of a semi-circle… not in the right direction, but it’s something… Add Point ID to Detail, then click the Swap Axis button and hey presto…

It appears a bit more ‘ovel’ than circular as the axis aren’t aligned, so don’t worry about this – set the display to Entire View will help. Then change the mark type to Line, move Point ID to Path and then change mark type to Polygon.

We now have a filled semi circle. We’re not going to use this sheet, but hopefully, this has helped a bit with some fundamental understanding.

When building we’re going to be using map layers (and at this point, we can’t add a layer to this sheet). We’ll also be defining calculations based on which feature of the viz we’re focussed on, as we can’t apply filters to the sheet (if you remove the ones applied, it will look a little crazy!). But if you change the Point Segment ID filter to Goal, you’ll get the shape of the goal indicator ‘reference line’.

You might want to play around with the filters to examine the behaviour.

Are you still with me…? Take a break, grab a cuppa, we haven’t even started building yet, but I’ll still be here when you get back 🙂

Setting up the map layers

Using Jack’s hints, create a field to help ‘initialise’ the map layers

Zero

MAKEPOINT(0,0)

Double click this to create a basic ‘map’ with a single point.

Then drag another instance of Zero onto the canvas and drop on the Add a Marks Layer section that appears

You’ll now have 2 marks layers, which means whatever we now do, we can always add more.

Building the Gauge Background & Legend layer

Note – in building I ended up with more mark layers than Jack suggested. I’ve subsequently seen other versions but am sticking to what I managed for now.

The first layer I’m going to build is the ‘grey’ semi circle of the main gauge and the coloured legend ‘inner’ semi circle.

I want to identify those points only.

Geo: BG-Legend Layer

IF [Point Type] <> ‘Label’ AND [Point Segment ID] IN (‘Background’, ‘Concern’, ‘Failure’, ‘Success’) THEN [Geo] END

On the Zero marks card, drop this field directly on top of the COLLECT(Zero) to replace it. Add Point ID to Detail, then flip the axis using the switch axis button. Add Point Segment ID to Colour and adjust the colours of the Background, Failure, Concern and Success values to suit.

Change the mark type to polygon, and move Point ID to Path. Name this layer BG & Legend

Building the Actual Layer

Create a new field

Profit Ratio

{FIXED [Manufacturer]: SUM([Profit])/SUM([Sales])}

and format to % with 1 dp.

We need to understand where on the gauge, the Profit Ratio for each Manufacturer falls. We know that the gauge starts at -30% Profit Ratio (ie 0% of the gauge is equivalent to -30% Profit Ratio) and the gauge ends at +30% Profit Ratio (ie 100% of the gauge is equivalent to +30% Profit Ratio). Therefore if the Manufacturer’s Profit Ratio >= 30% it fills 100% of the gauge, anything less needs to be partially through. We can calculate this (using one of Jack’s hints) with

PR % of Gauge

([Profit Ratio] + [Gauge End Profit Ratio]) / ([Gauge End Profit Ratio]- [Gauge Start Profit Ratio])

To then determine the angle in degrees (and again using Jack’s hints), we want to find the proportion of 180 degrees that the PR % of Gauge represents, and then take off 90 degrees based on the gauge rotation.

PR Angle (Degrees)

([PR % of Gauge] * 180)-90

We can then convert this to radians

PR Angle Rads

RADIANS([PR Angle (Degrees)])

This gives us information to help determine a singular point on the gauge we need to ‘draw’ up to. But I want to ‘draw’ a polygon that goes from the left end (-30% mark) to this point, and for this, I need to know all the other points up to that point.

The way I came up with isn’t what Jack did. My result means I don’t get an exactly accurate marker, but it is so close to it’s position, and is ‘good enough’ for the viz type and how it’s being displayed.

To understand what I did, build out a tabular sheet that is filtered to Manufacturer = Hon and Point Type = Actual, and shows Manufacturer, Point Segment ID and Point ID on Rows with Point Angle Rads, Profit Ratio and PR Angle Rads as measures.

The Profit Ratio for the Manufacturer =Hon is 6.4% which is at an angle of 0.34 radians around the semi circle.

You can also see that while we have values for the Point Angle Radians field associated to the Point Segment ID = Background, we don’t have any for the Point Segment ID = Actual, as this is what we’re trying to find out.

Given that I know that all the Point Angle Radians values associated to the Point Segment ID = Background build a complete semi circle, I figured, to display up to my ‘actual’ Profit Ratio, I just want to get all the points associated to background which are less than the PR Angle Rads value.

PR Point Angle Radians

IF [Point Angle Rads] <= [PR Angle Rads] THEN [Point Angle Rads] END

Pop this into the table, and when you scroll down, you’ll see I’ve only got values in my new field up to where the PR Angle Rads is less

Using this field, I’ll create some new X & Y fields which I can make make into a spatial field

X (actual)

[Point Radius] *SIN([PR Point Angle Radians])

Y (actual)

[Point Radius] * COS([PR Point Angle Radians])

Geo – Actual

IF [Point Type] = ‘Actual’ AND [Point Segment ID] = ‘Background’ THEN
MAKEPOINT([X (actual)], [Y (actual)])
END

On the Zero(2) marks card, Replace the COLLECT(Zero) pill with the Geo – Actual pill by dragging the latter and dropping it directly on the former. Add Point ID to Detail.

It’s drawing the points for a complete semi-circle, as every Manufacturer is being included. To help get the rest of the display right, for the various permutations, add Manufacturer to filter and filter to Hon, Bush, Logitech and Xerox. Add Manufacturer to Columns too. You should now see the marks stop at various positions around the arc. The filters will be adjusted later when we tackle the trellis.

Change the mark type to polygon and move Point ID to Path. Rename the layer to Actual.

For the colouring, we need to determine the RAG status of each Profit Ratio – where does the PR % of Gauge sit in comparison the constants we know

Actual RAG

IF [PR % of Gauge] <= [Gauge Concern Pct of Gauge] THEN ‘Failure’
ELSEIF [PR % of Gauge] <= [Gauge Success Pct of Gauge] THEN ‘Concern’
ELSE ‘Success’ END

Add this to the Colour shelf and adjust accordingly.

Building the Goal Indicator Layer

Create a new spatial field

Geo: Goal Layer

IF [Point Type] = ‘Actual’ AND [Point Segment ID] = ‘Goal’ THEN [Geo] END

and then drag this onto the canvas and drop on the Add A Marks Layer option.

Change the mark Type to Polygon and add Point ID to path. Add Point Segment ID to Colour and adjust the colour of the Goal value to suit. Rename the layer to Goal.

Building the Label layer

Create a new spatial field

Geo: Labels

IF [Point Type] = ‘Label’ THEN [Geo] END

and then drag this onto the canvas and drop on the Add A Marks Layer option. Add Point Segment ID to Detail. 3 marks should now displayed in the positions we need them

This bit took a bit of time to get right, as I had 3 requirements I wanted to satisfy: 1 – display a text or a numeric field as a label depending on what label I wanted to display (I attempted to have a single ‘label’ field converting numbers to strings with the relevant formatting, but the Profit Ratio % just wouldn’t show how I wanted when converted to string); 2 – adjust the colour of (some) of the labels depending on the RAG status of the profit ratio value; 3 – adjust the size of the Profit Ratio label based on how many gauges were displayed.

I needed several label fields

Label: Goal

IF [Point Segment ID] = ‘Goal’ THEN [Gauge Success Amt] END

formatted to % with 0 dp.

Label: Manufacturer-Fail

IF [Point Segment ID] = ‘Manufacturer’ AND [PR % of Gauge]<=[Gauge Concern Pct of Gauge] THEN [Manufacturer] END

Label: Manufacturer-Concern

IF [Point Segment ID] = ‘Manufacturer’ AND ([PR % of Gauge]<=[Gauge Success Pct of Gauge] AND [PR % of Gauge]>[Gauge Concern Pct of Gauge]) THEN [Manufacturer] END

Label: Manufacturer-Success

IF [Point Segment ID] = ‘Manufacturer’ AND [PR % of Gauge]>[Gauge Success Pct of Gauge] THEN [Manufacturer] END

Label: PR-Fail

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]<=[Gauge Concern Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Label: PR-Concern

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]>[Gauge Concern Pct of Gauge] AND [PR % of Gauge]<= [Gauge Success Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Label: PR-Success

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]>[Gauge Success Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Add all these fields to the Label shelf, and adjust the label so that all are positioned on the same line, with no spaces, and add a carriage return beneath the text. Colour each field accordingly (DO NOT ADJUST THE FONT SIZE).

Change the mark type to text and align the label top centre. Rename the mark type to Labels and Disable Selection

To adjust the size of the labels, create a new parameter which we’ll need for the trellis.

pShowTop

integer parameter, defaulted to 7 which is a range from 5 to 81 with a step size of 1.

Show the parameter.

Create a new field

Label: Size

IF [Point Segment ID] = ‘BAN’ THEN [pShowTop]
ELSEIF [Point Segment ID] = ‘Manufacturer’ THEN 60
ELSE 80 END

We want the size of the BAN label to decrease as the number of manufacturers displayed increases.

Add this filed to the Size shelf as a continuous dimension (green pill, not aggregated).

Edit the Size legend so that sizes vary by range, the range is reversed, and the range starts from 1 to 81. Adjust the mark size range slider to a suitable start and spread.

As you change the value of the pShowTop parameter, the Profit Ratio BAN should adjust in size.

Format Sales and Profit to be $ with 0dp and to display as () when negative, then on all marks cards, add Manufacturer to Detail and Sales, Profit, Profit Ratio and Actual RAG to Tooltip, and adjust Tooltip on all the layers to suit.

Finally remove all gridlines/zero lines/axis ticks and hide the longitude & latitude axis. Hide the null indicator.

Building the Trellis

So now we’ve got the core viz nailed, we need to address the layout, which is to show a gauge for each of the top n manufacturers based on Sales. This means we need to have the gauges indexed/ranked from 1 to n based on total Sales, and then arrange in a grid so that top left is the manufacturer with the highest sales, and bottom right is the manufacturer with the lowest sales. For this we need to assign a row and column number against each manufacturer.

There are multiple blog posts about creating trellis charts. My go to post has always been this one by Chris Love, especially when the requirement is for the trellis to be dynamic (the number of rows/columns can vary) depending on the number of items to be displayed.

However in this instance, using the calculations referenced in the blog doesn’t meet the requirement of ensuring there are more columns than rows. This was an area that got me stumped. As a result, I finished the viz with a version that utilises my ‘go to’ calculations (published here), before then looking at Jack’s solution to get the calculations, which are

Cols Count

//If Rounded value = Value without Round then no remainder, use that number
if SIZE()/round(SQRT(SIZE()),0) = int(SIZE()/round(SQRT(SIZE()),0)) THEN SIZE()/round(SQRT(SIZE()),0)
//Otherwise add 1 to the number of columns
ELSE int(SIZE()/round(SQRT(SIZE()),0)) + 1
END

Size() is reflective of the number of items (in this case Manufacturers) to be displayed… as I write this down, for this specific instance, you could probably replace SIZE() with the parameter pShowTop.

Rowsv2

//For Each Manufacturer, what Row should it be in the Trellis?
//Rank the Manufacturer, Find the Integer portion when dividing by # of Columns
int((INDEX()-1)/ [Cols Count])

Colsv2

//For Each Manufacturer, what Column should it be in the Trellis?
//Rank the Manufacturer, Find the Remainder when dividing by # of Columns
int((INDEX()-1) % [Cols Count])

Make both Rowsv2 and Colsv2 discrete.

Add Rowsv2 to Rows and Colsv2 to Columns. Remove Manufacturer from Columns. It’ll look a bit odd, but be patient.

Edit the Manufacturer pill on the Filter shelf. On the General tab, select None, to remove everything from the filter, then on the Top tab adjust to be based on the top pShowTop by Sales

Now edit the table calculation associated to the Rowsv2 pill, so that it computes by specific dimensions and every field except Point ID is selected. Ensure Manufacturer and Point Segment ID are listed at the top in that order. Set the level to be Manufacturer and apply a custom sort based on Sum of Sales descending

As this is a nested table calculation, select the drop down arrow at the top and apply the same settings to the nested Cols Count field.

Then do exactly the same again for the Colsv2 table calculation settings. If all has been applied successfully, then you should get a grid

where you can then adjust the pShowTop parameter

Hide the Rowsv2 and ColsV2 fields from displaying (uncheck show header). Show the Longitude axis, and fix it to start at 0 but end ‘automatic’. Then hide the axis again (this ensures the arc lands on the row divider).

Then add the viz to a dashboard. My published version based on the trellis using Jack’s calculations is published here.

If you’ve made it to the end – well done! It was a bit of a marathon to do the challenge and another to write this blog. I’m sure it’s been quite an effort to read too, but hopefully you’ve learnt something, and I’ll certainly be referencing this again when a map layer and/or gauge based scenario occurs again.

Happy vizzin’!

Donna

Can you build this segmented bar chart?

Donna Coles1 Comment

My #WOW2024 challenge this week, was to recreate this bar chart which displayed the total value in conjunction with the values split by another dimension, in this instance year. It was inspired by Sam Parsons’ film franchises viz.

The viz is built on a single sheet, and only uses 3 fields from the data set, the Order Date the Sub-Category and the Sales value.

Let’s build…

On a new sheet, add Sub-Category to Rows and Sales to Columns and sort by Sales descending. Add Order Date to Colour to build a basic stacked bar chart.

We need to be able to show two bars directly ‘side by side’, but with different properties, so we need to end up with 2 marks cards. The ‘heights’ of the bars need to differ, so we’re going to create a ‘fake axis’ on Rows to help.

Double click into the Rows shelf and manually type MIN(0.5)

Change the mark type to bar. Each bar is now positioned based on it’s specific value (ie not stacked).

To get the bars to be positioned where we want them, apply a Running Total Quick Table Calculation to the Sales pill in the Columns shelf. Then edit the table calculation so that is computes by Year of Order Date only.

Add Sales to the Size shelf, and edit the size to be Fixed and aligned Right. You should now have a stacked bar effect.

Add Sales to Tooltip, then add a Percentage of Total quick table calculation. Edit the calculation so it is computing by Year of Order Date.

Adjust the Tooltip to reference the relevant details (note in the above gif, and my original published version, at some point the labelling went awry and the label was showing the cumulative sales value, and not the value for that year).

So now we’ve managed to build a segmented bar chart in a ‘different way’ by using an axis and the size field. We build on this to get the total bar. As this bar needs to be wider than the segmented bar, double click into the Rows shelf and type MIN(1.5), then from the MIN(1.5) marks card, remove the Order Date field from Colour. We now have 2 bars – 1 segmented and 1 not.

Make the chart dual axis and synchronise the axis, then right click on the right hand axis and move marks to back.

On the Min(0.5) marks card, move Order Date from Colour to Detail. Then on the All marks card, click on the Colour shelf and adjust the colour to suit (I set to #dec7b6) and add a white border.

Adjust the Tooltips on both marks cards so the MIN(1.5) is just showing the Sub-Category and Sales value, while the MIN(0.5) card is showing the Order Date year and % of sales value as well.

Narrow the width of the rows. Hide the right hand and the bottom axis (uncheck show header).

We want to make each pair of bars more distinctive, by providing more space between them. Edit the axis to fix the axis from -0.3 to 1.5.

Now hide the axis, and the Sub-Category column heading (right click label and hide field label for rows). Remove all row/column divider lines, zero lines and gridlines. Set the worksheet background colour (I used #fefaf1). Adjust the colour/style of the Sub-Category labels (I used bold, brown) and align top right.

And ta-dah! this should be your finished viz. My published version is here.

We’re going to building on this in a later challenge (I felt putting it all together in a single challenge might be a bit much), so look out for Part 2 in a few weeks time.

Happy vizzin’!

Donna

Playing with Stacks

Donna ColesLeave a comment

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.

Happy vizzin’!

Donna

Can you filter a small multiples chart?

Donna ColesLeave a comment

Erica set the challenge this week, and I’m not gonna lie, I found this tough. On the face of it, it looks like something I felt I should be ok at, but nuances cropped up as I was building that meant I often had to change tact and try something different.

My intention was to build each version – Beginner, Intermediate then Advanced, adding to my solution each time, but decisions I made early on, then caused me grief later. For example, I did choose to utilise a lot of table calcs, but that meant when it came to applying the sorting mechanism that I wanted, I couldn’t reference the sort field I needed, as it contained a table calc. So I had to unpick the logic and build with LODs instead which took me a while to get right. Getting single lines to display in each ‘state’ cell also proved tricky at times, and that was even before I’d got to the requirement to pad out the ‘missing values’ with 0s. I also seemed to find that some things only seemed to work if I added pills and applied settings in a particular order. All in all, quite a challenge, and while I did get there in the end, I did have to peek at the solution at times to figure out if I was going nuts, but I found trying to scale back Erica’s solution to the beginner/intermediate version also suffered the same issues I was experiencing. I built with Desktop v2024.1 and there were times I was wondering if something had “broken” in that version, although having finally reached the end, I’ve yet to test that theory.

So, I’m blogging this guide with several caveats – Going from beginner to intermediate is ‘okay’, but when it gets to advanced I had to start again(ish). Some of the calcs I provide will just be ‘as is’. I will do my best to explain what’s going on, but there are times that I just don’t get it, and it’s just been trial and error that got me the results I needed – sorry!

So with all that in mind, let’s get building.

Initial steps

After connecting to the provided hyper file, I found I did have to create the modified sales value

Sales Modified

IF SUM([Sales])<5000 THEN SUM([Sales])*10 ELSE SUM([Sales]) END

I also decided to add a data source filter (right click data source > edit data source filters) to restrict the data to just Country/Region = USA.

The later versions of superstore have Canadian Provinces included too, and I don’t think these were listed in Erica’s solution. It just felt easier all round to exclude these records from source.

Beginner challenge

When building a trellis chart, we need to determine which Row and which Column our specific dimensions (in this case State/Province) will sit in. As the requirements already stated it was to be a 3 column grid, our calculations for this didn’t need to be so complex.

Cols

(INDEX()-1)%3

INDEX() returns an incremental number starting at 1 for whatever dimension or set of dimensions we’re counting over. In this case we’re counting the number of State/Provinces. %3 returns the remainder when the index is divided by 3, so we get values of 0, 1 and 2.

Change this field to be discrete (right click -> convert to discrete)

Add State/Province to Rows then add Cols to Rows. Edit the table calculation so the field is computing by State/Province. You can see that the first 3 rows will be positioned in columns 0, 1,2 respectively and so on.

Create a new field

Rows

INT((INDEX()-1) / 3)

This takes the index value (minus 1), divides by 3 and ’rounds’ to a whole number. Make this discrete too and add to Rows, setting the table calculation as described above. Now we can see the first 3 rows will all actually be in the same row (row 0), then next 3 rows in row 1 and so on.

Shift the pills around so Cols is on Columns, Rows is on Rows and State/Province is on Detail. Add Sales Modified to Rows.

Create a new field

Quarter Date

DATE(DATETRUNC(‘quarter’, [Order Date]))

and add this to Columns setting it as a continuous exact date (green pill). We’ve got a bit of unexpected ‘spaghetti’ going on…

To fix it, do the following ..

Add Quarter Date to Detail as a discrete exact date (blue pill). Change Quarter Date on Columns to be a continuous attribute (green pill – first change to attribute, then change to continuous). Edit the table calculation settings for both the Rows and the Cols fields to be computing by both State/Province and Quarter Date at the level of State/Province.

I had to reference previous challenges and blog posts I’d written to manage this… maybe there is something simpler, as this is pretty taxing for the ‘beginner’ part of the challenge.

Add another instance of ATTR(Quarter Date) to Columns and make dual axis and synchronise the axis. This will create an axis at the top of the chart as well as the bottom.

Format the ATTR(Quarter Date) pill so the axis format is custom formatted to “Q”q “‘”yy

Edit all the axis (top/bottom and left) and update the title. Adjust the Tooltip. Hide the Cols and Rows fields (right click and uncheck show header). Change the Colour of the line to grey.

This should be the Beginner solution. I have published this here.

Intermediate challenge

For this part of the challenge, we need to set up lots of new calculations, so let’s do this first. As usual, I’ll manage this in a tabular format. So on a new sheet, add State/Province and Quarter Date as a discrete exact date (blue pill) to Rows. Add Sales Modified to Text.

We need to get the threshold value for each State/Province, which is the average of the numbers all listed above, multiplied by 2. We’ll use LODs for this

Threshold

{FIXED [State/Province]: AVG({FIXED [State/Province], [Quarter Date]: ([Sales Modified])})} * 2

working inside out… get the value of the Sales Modified value for each State/Province and Quarter Date (which is the same as the values you see listed above) and then average this at the State/Province level and multiple the final result by 2. Add this to the table. This is the field we’ll be using for the horizontal reference line.

Next we need to identify the rows where the Sales Modified value exceeds the threshold, and then return the Sales Modified values for only these rows. I’ll do this in 2 stages

Is Above Threshold?

INT([Sales Modified] > SUM([Threshold]))

This returns a 1 or 0 depending on whether the statement is True or False. Using actual numeric values rather than boolean helps later on. Set this field to be discrete and add to the table.

Above Threshold Sales

IF [Is Above Threshold?]=1 THEN [Sales Modified] END

Add this to the table too. For the rows where we have 1’s, a value is displayed. This is the field we’ll be using for the red circles.

Finally we need to determine some fields to help us define a reference band. These need to be dates as they’ll be applied to the date axis, but the band doesn’t stretch to the previous/next quarter, and is only present if the last value is over the threshold.

Again using LODs let’s get the final date in the quarter

Max Quarter Per State

{FIXED [State/Province]: MAX([Quarter Date])}

Add this to the table as a discrete exact date (blue pill).

Now we need to know if the value associated with the final quarter is above the threshold or not

Final Quarter Above Threshold

INT(MIN([Quarter Date]) = MIN([Max Quarter per State]) AND [Is Above Threshold?]=1)

Again this will return a 1 or 0. Change to discrete and pop that into the table too. We can see Colorado is the first state listed where this is true.

Now we want to ‘spread’ that value across every row associated to the state

State Has Final Quarter Above Threshold

{FIXED [State/Province]: MAX(
{FIXED [State/Province], [Quarter Date]: ([Final Quarter Above Threshold])})
}

For each State/Province and Quarter Date, get the Final Quarter Above Threshold value and then get the maximum value of this for each State/Province. This is where having the values as 1’s and 0s helps.

Make discrete and add this to the table. Every row for Colorado has this set to 1

Now we can work out some dates

Ref Band Min

DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN
DATEADD(‘month’, -1, [Max Quarter per State])
END)

If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month less than the final quarter for that state.

Similarly

Ref Band Max

DATE(IF [State Has Final Quarter Above Threshold] = 1 THEN
DATEADD(‘month’, 1, [Max Quarter per State])
END)

If the State/Province is over the threshold for it’s final quarter, then get a date and set it to be 1 month more than the final quarter for that state.

Add both of these of the table as discrete exact dates (blue pills).

Now we have all the building blocks to build the next bit of the challenge.

Start by duplicating the Beginner viz.

Add Above Threshold Sales to Rows and make Dual axis and synchronise the axis.. Remove the 2nd instance of the Quarter Date from Columns, so we now have marks cards relating to the 2 measures rather than the 2 dates. Remove Measure Names from the All marks card.

Change the mark type on the Above Threshold Sales marks card to circle and change the colour to red. Adjust size to suit.

Add Threshold to the Detail shelf of the All marks card. Right click on the Sales axis and add reference line. Set it to be per pane and use the Threshold field. Display the value as the Label. Don’t show a Tooltip. Display a dotted black line.

Update the Tooltip to now have a reference to the threshold value too.

Add Ref Band Max and Ref Band Min to the Detail shelf of the All marks card. Set them both to be continuous attributes (green pills).

Right click on the Quarter Date axis and add reference line. Set it to be a band per pane that goes from the Ref Band Min to the Ref Band Max. Don’t display labels or tooltips or a line. Fill with a pale shade ot red/pink.

Add back in the additional Quarter Date dual axis as described in the Beginner section to get the dates listed the top again.

Hide the right hand axis (uncheck show header) and hide the NULL indicator.

This completes the Intermediate challenge. My version is published here.

Advanced challenge

Go back to the tabular sheet we were using to check the calculations needed for the Intermediate challenge, as we’ll build on this.

Firstly the sorting. We need to sort the states based on whether the final quarter was above the threshold or not, and then by the number of times the state was above the threshold. Let’s get the count to start with

Above Threshold Count Per State

{FIXED [State/Province]: SUM(
{FIXED [State/Province], [Quarter Date]: ([Is Above Threshold?])})
}

For each State/Province and Quarter Date, get the Is Above Threshold? value and then sum these up for each State/Province. This is where again having the values as 1’s and 0s helps.

Make this discrete and add to the table

then create a field we’ll use for the sort. This is going to be a numeric field

Sort

IF [State Has Final Quarter Above Threshold] = 1 THEN
100000 + [Above Threshold Count Per State]
ELSE
[Above Threshold Count Per State]
END

We’re just using a very large arbitrary number to force those states where the final quarter is over the threshold to be higher in the list. Make this discrete and add to the table too.

We can now apply a Sort to the State/Province field to sort by the Sort field descending

This results in Colorado moving to the top of the list followed by Minnesota, which also has 3 quarters above the threshold, including the last quarter, but alphabetically falls after Colorado so is listed 2nd.

To filter the data, I created another field, just for ease

Filter

IIF([State Has Final Quarter Above Threshold]=1,’Urgent’,’Non-Urgent’)

Add this to the Filter shelf and set to Urgent. The states should now be restricted to just those where the final quarter is above threshold.

For the final requirement of this challenge, we’ll build out another table on another sheet to demonstrate, as we need to work with a different instance of the quarter date.

On a new sheet, add State/Province to Rows and add Order Date at the Quarter (month year) level as a discrete field (blue pill) to Rows. Add Sales Modified to Text

For Alabama, we don’t have a 2020 Q3 or a 2023 Q3. Click on the Quarter(Order Date) pill and select Show Missing Values. These quarters appear but with no Sales Modified value.

We’ve had to use this different way to define the date quarter as if we tried to ‘show missing values’ against the Quarter Date field that is set to ‘exact date’, we send up getting every day that is missing, not just the dates relating to the quarter. Also, the ATTR(Quarter Date) field we’ve used on the previous vizzes, doesn’t allow the Show Missing Dates option.

Anyway, we need to get 0s in to these dates.

Show Modified with 0

ZN(LOOKUP([Sales Modified],0))

Apart from the Rows/Cols calcs needed for the trellis, this is the only table calculation I ended up using. It’s basically looking up it’s own row (LOOKUP([field],0)) and if it can’t find a value (as it’s missing) it’s returning 0 (the ZN() function). Add that to the table.

Ok so now we have the components needed, let’s build the viz. We can use the Intermediate version as a starting point, but will need to reapply some of the features.

Duplicate the Intermediate viz.

Remove the 2nd instance of the ATTR(Quarter Date) pill on Columns. Drag the Sales Modified with 0 pill and drop it directly over the Sales Modified pill so it replaces it. Hopefully the chart should still look the same.

Right click the Order Date field from the left hand data pane, and drag it directly onto the ATTR(Quarter Date) pill. Release the mouse and select the continuous quarter/year option from the dialog that displays.

Things will start to look a bit odd… you’ve lost your lines… Remove the Quarter Date pill from the Detail shelf on the All marks card. Fix the Rows and Cols table calc fields by just updating them to compute by State/Province only. Adjust the table calc of the Sales Modified With 0 field to compute by Quarter of Order Date and State/Province in that order.

Things still look crazy…. but just one more step… Show Missing Values on the Quarter(Order Date) pill.

Now every cell should be associated to a single State/Province with no broken lines, and Wyoming, right at the bottom, should show more than a single dot. This was A LOT of trial and error to fathom all this out.

Add back in the reference band following the instructions above (you should find the reference line for the threshold value will just then appear) and re-update the Tooltip.

Add another instance of Order Date at the quarter/year continuous level (green pill) to Columns and make dual axis and synchronise the axis. Edit the axis titles, and format them to the “Q”q “‘”yy custom format.

Apply the sort to the State/Province field on the Detail shelf so it is sorting by Sort Descending. Add Filter to the Filter shelf and select Urgent.

Add this to a dashboard, and that should be the completed Advanced challenge which I’ve published here.

There really was some black magic going on here at times. Tough one this week!

Happy vizzin’!

Donna

Can you visualise when it gets busy?

Donna ColesLeave a comment

It was Yoshi’s first challenge as a #WOW coach this week, and it provided us with an opportunity to develop our data densification and date calculation skills. I will admit, this certainly was a challenge that made me have to think a lot and reference other content where I’d done similar things before.

Modelling the data

Yoshi provided us with an adapted data set of patient waiting times, based on the Healthcare dataset from the Real World Fake Data (#RWFD) site. This provides 1 row per patient with a wait start time and end time. The requirement was to understand how many patients were waiting in each 30 min time slot in a 24hr period, so if a patient waited over 30 mins, or the start & wait time spanned a 30 min time slot, then the data needed to be densified appropriately. Yoshi therefore provided an additional data set to use for this densification, and I have to be honest, it wasn’t quite what I was expecting.

So the first challenge was to understand how to relate the two sets of data together, and this took some testing before I got it right. Yoshi provided hints about using a DATEDIFF calculation to find the time difference between the wait start time (rounded to 30 mins) and the wait end time (rounded to 30 mins).

To work out the calculation required, I actually first connected to the Hospital ER data set and then spent time working out the various parts of the formula required. I wanted to work out what the waiting start time was ’rounded down’ to the previous 30 min time slot, and what the waiting end time was ’rounded up’ to the next 30 min time slot.

Date Start Round

//Rounding down the wait start time

IIF(DATEPART(‘minute’, [Date Start])<30 , DATETRUNC(‘hour’, [Date Start]) , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date Start])))

If the minute of Date Start is between 0 and 29, then return the date at the hour mark, otherwise (if the minute of Date Start is between 30-59) then find the date at the hour mark, and add on 30 minutes. So if Date Start is 23/04/2019 13:23:00 then return 23/04/2019 13:00:00, but if Date Start is 23/04/2019 13:47, then return 23/04/2019 13:30:00.

Date End Round

//Rounding up the wait end time

IIF(DATEPART(‘minute’, [Date End])<30 , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date End])) , DATEADD(‘hour’, 1,DATETRUNC(‘hour’, [Date End])))

If the minute of Date End is between 0 and 29, then find the date at the hour mark, and add on 30 minutes, otherwise (if the minute of Date End is between 30-59) then find the date at the hour mark, and add on 1 hour. So if Date End is 23/04/2019 13:23:00 then return 23/04/2019 13:30:00, but if Date End is 23/04/2019 13:47, then return 23/04/2019 14:00:00.

With this, I was then able to relate the Hospital ER data set to the dummy by 30 min data set by adding a relationship calculation to the Hospital ER data set of

DATEDIFF(‘minute’,
IIF(DATEPART(‘minute’, [Date Start])<30 , DATETRUNC(‘hour’, [Date Start]) , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date Start]))),
IIF( DATEPART(‘minute’, [Date End])<30 , DATEADD(‘minute’, 30, DATETRUNC(‘hour’, [Date End])) , DATEADD(‘hour’, 1,DATETRUNC(‘hour’, [Date End])))
)

(which returns the difference in minutes between the ’rounded down’ Date Start and the ’rounded up’ Date End – you can’t reference calculated fields in the relationship, so you have to recreate)

and set this to be >= to Range End from the dummy by 30 min data set

Let’s see what this has done to the data.

On a sheet add Patient ID. Date Start (as discrete exact date – blue pill), Date End (as discrete exact date – blue pill), Date Start Round (as discrete exact date – blue pill), Date End Round (as discrete exact date – blue pill) and Index (as discrete dimension – blue pill) to Rows and add Patient Waittime to Text.

Looking at the data for Patient Id 899-89-7946 : they started waiting at 06:18 and ended at 07:17. This meant they spanned three 30 min time slots: 06:00-06:30, 06:30-07:00 and 07:00-07:30, and consequently there are 3 rows displayed, and the ’rounded’ start & end dates go from 06:00 to 07:30.

Identifying the axis to plot the 24hr time period against

Having ‘densified’ the data, we now need to get a date against each row related to the 30 min time slot it represents. ie, for the example above we need to capture a date with the 06:00 time slot, the 06:30 time slot and the 07:00 time slot.

But, as the chart we want to display is depicted over a 24hr timeframe, we need to align all the dates to the exact same day, while retaining the time period associated to each record. This technique to shift the dates to the same date, is described in this Tableau Knowledge Base article.

Date Start Baseline

DATEADD(‘day’, DATEDIFF(‘day’, [Date Start Round], #1990-01-01#), [Date Start Round])

Add this into the table, you can see what this data all looks like – note, I’m just choosing a arbitrary date of 01 Jan 1990, so my baseline dates are all on this date, but the time portions reflect that of the Date Start Round field.

With the day all aligned, we now want to get a time reflective of each 30 min timeslot. The logic took a bit of trial and error to get right, and there may well be a much better method than what I came up with. It became tricky as I had to handle what happens if the time is after 11pm (23:00) as I needed to make sure I didn’t end up returning dates on a different day (ie 2nd Jan 1990). I’ll describe my logic first.

If the Index is 0 then we just want the date time we’ve already adjusted – ie Date Start Baseline

If the Index is 1, then we need to shift the Date Start Baseline by 30 minutes. However, if Date Start Baseline is already 23:30, then we need to hardcode the date to be 01 Jan 1990 00:00:00, as ‘adding’ 30 mins will result in 02 Jan 1990 00:00:00.

If the Index is 2, then we need to shift the Date Start Baseline by 1 hour. However, if Date Start Baseline is already 23:30, then we need to hardcode the date to be 01 Jan 1990 00:30:00, as ‘adding’ 1 hour will result in 02 Jan 1990 00:30:00. Similarly, if Date Start Baseline is already 23:00, then we need to hardcode the date to 01 Jan 1990 00:00:00, otherwise we’ll end up with 02 Jan 1990 00:00:00.

As the relationship didn’t result in any instances of Index > 2, I stopped my logic there. This is all encapsulated within the calculation

Date to Plot

CASE [Index]
WHEN 0 THEN [Date Start Baseline ]
WHEN 1 THEN IIF(DATEPART(‘hour’, [Date Start Baseline ])=23 AND DATEPART(‘minute’, [Date Start Baseline ])=30, #1990-01-01 00:00:00#, DATEADD(‘minute’, 30, [Date Start Baseline ]))
WHEN 2 THEN IIF(DATEPART(‘hour’, [Date Start Baseline ])=23 AND DATEPART(‘minute’, [Date Start Baseline ])=30, #1990-01-01 00:30:00#, IIF(DATEPART(‘hour’, [Date Start Baseline ])=23 AND DATEPART(‘minute’, [Date Start Baseline ])=0, #1990-01-01 00:00:00#, DATEADD(‘hour’, 1, [Date Start Baseline ])))
END

Phew!

Add this to the table as a discrete exact date (blue pill), and you can see what’s happening.

For Patient Id 899-89-7946, we have the 3 timeslots we wanted: 06:00 (representing 06:00-06:30), 06:30 (representing 06:30 -07:00) and 07:00 (representing 07:00-07:30).

On a new sheet, add Date To Plot as a discrete exact date (blue pill) and then create

Count Patients

COUNT([Patient Id])

and add to Text and you have a list of the 48 30 min time slots that exist within a 24hr period, with the patient counts.

We need to be able to filter this based on a quarter, so create

Date Start Quarter

DATE(DATETRUNC(‘quarter’,[Date Start]))

and custom format this to yyyy-“Q”q

Add to the Filter shelf selecting individual dates and filter to 2019-Q2, and the results will adjust, and you should be able to reconcile against the solution.

Building the 30 minute time slot bar chart

On a new sheet, add Date Start Quarter to the Filter shelf and set to 2019-Q2. Then add Date to Plot as a continuous exact date (green pill) to Columns and Count Patients to Rows. Change the mark type to a bar and show mark labels.

Custom format Date To Plot to hh:nn.

We need to identify a selected bar in a different colour. We’ll use a parameter to capture the selected bar.

pSelectTimePeriod

date parameter defaulted to 01 Jan 1990 14:00 with a display format of hh:nn

Then create a calculated field

Is Selected Time Period

[Date to Plot] = [pSelectTimePeriod]

and add to the Colour shelf, adjusting colours to suit and setting the font on the labels to match mark colour.

A ‘bonus’ requirement is to make each bar ’30 mins’ wide. For this we need

Size – 30 Mins

//number of seconds in 30 mins divided by number of seconds in a day (24hrs)
(30 * 60) / 86400

Add this to the Size shelf, change it to be a dimension (so no longer aggregated to SUM), then click on the Size shelf, and set it to be fixed width, left aligned.

Via the Colour shelf, change the border of the mark to white.

Next, we need to get the tooltip to reflect the ‘current’ time slot, as well as the next time slot. For this I created

Next Time Period

IIF(LAST()=0, #1990-01-01 00:00:00#, LOOKUP(MIN([Date to Plot]),1))

This is a table calculation. If it’s the last record (ie 01 Jan 1990 23:30), then set the time slot to be 1st Jan 1990 00:00, otherwise return the next time slot (lookup the next (+1) record). Custom format this to hh:nn and add to the Tooltip shelf. Set the table calculation to compute by Date to Plot and Is Selected Time Period.

Update the Tooltip as required.

The final part of this bar chart is another ‘bonus’ requirement – to add 2 hr time interval ‘bandings’. For this I created another calculated field

2hr Time Period to Plot

IIF(DATEPART(‘hour’, MIN([Date to Plot]))%4 = 0 AND DATEPART(‘minute’, MIN([Date to Plot]))=0,WINDOW_MAX([Count Patients])*1.1,NULL)

If the hour part of the date is divisible by 4 (ie 0, 4, 8,12,16, 20), and we’re at the hour mark (minute of date is 0), then get the value of highest patient count displayed in the chart, and uplift it by 10%, otherwise return nothing.

Add this to Rows. On the 2nd marks card created, remove Is Selected Time Period from Colour and Next Time Period from Tooltip. Adjust the table calculation of the 2hr Time Period to Plot to compute by Date to Plot only. Remove labels from displaying.

The bars are currently showing all at the same height (as required) but at a width of 30 minutes. We want this to be 2 hrs instead, so create

Size – 2 Hrs

//number of seconds in 2hrs / number of seconds in a day (24 hrs)
(60*60*2)/86400

Add this to the Size shelf instead (fixing it to left again). Adjust the colour to a pale grey, and delete all the text from the Tooltip.

Hide the nulls indicator. Make the chart dual axis and synchronise the axis. Right click on the 2hr Time Period Plot axis (the right hand axis) and move marks to back.

Tidy up the chart by removing row & column dividers, hiding the right hand axis, removing the titles from the other axes. Update the title accordingly.

Building the Patient Detail bar chart

On a new sheet, add Is Selected Time Period to Filter and set to True. Then go back to the other bar chart, and set the Date Start Quarter Filter to Apply to Worksheets -> Selected Worksheets and select the new one being built.

Add Department Referral, Patient Id, Date Start (as discrete exact date – blue pill) and Date End (as discrete exact date – blue pill) to Rows and Patient Waittime to Columns. Manually drag the None Department Referral option to the top of the chart. Add Patient Waittime to Colour and adjust to use the grey colour palette. Remove column dividers.

The title of this chart needs to display the number of patients in the selection, as well as the timeframe of the 30 min period. We already have the start of this captured in the parameter pSelectTimePeriod. We can use parameters to capture the other values too.

pNumberPatients

integer parameter defaulted to 0

pNextTimePeriod

date parameter, defaulted to 01 Jan 1990 14:30:00, with a custom display format of hh:nn

Update the title of the Patient Detail bar chart to reference these parameters (don’t worry about the count not being right at this point).

Capturing the selections

Add the sheets onto a dashboard. Then create the following dashboard parameter actions.

Set Selected Start

On select of the 30 Min bar chart, set the pSelectTimePeriod parameter passing in the value from the Date To Plot field.

Set Selected End

On select of the 30 Min bar chart, set the pNextTimePeriod parameter passing in the value from the Next Time Period field.

Set Count of Patients

On select of the 30 Min bar chart, set the pNumberPatients parameter passing in the value from the Count Patients field, aggregated at the SUM level.

With these all set, you should find you can click on a bar in the 30 minute chart and filter the list of patients below, with the title reflecting the bar chosen.

The basic viz itself wasn’t overly tricky, once you get over the hurdle of the calculations needed for the relationship and identifying the relevant 30 min time periods.

My published viz is here. Enjoy!

Happy vizzin’!

Donna

Can you create these KPI Cards?

Donna ColesLeave a comment

Lorna set a table calculation filled challenge this week to recreate some KPI cards using an aggregated and amended version of the Superstore data set.

Lorna purposefully used a string Period field to define the timeframe to encourage the use of table calculations. No doubt, there are ways you could use string functions/regex to extract the relevant year and month number to come up with a different solution, but I’m going to head down the intended route.

And as with any table calculation based challenge, I find it best to always build out all the calculations I need into a tabular display to start with before building the viz. So let’s get started…

Building out the Calculations

We need to have a handle on more of the data than just that associated to the Period we’re interested in, so we can’t use a simple Quick Filter on the Period field to restrict the data, otherwise we can’t ‘access’ data for the previous period. So to manage which Period we want to focus on, I created a parameter

pSelectedPeriod

string field that uses a list where the values are added from the Period field. Default to P12 Y2022/23

On a new sheet, add Period to Rows and then add the Sales , Profit and Quantity measures to Text. Show the pSelectedPeriod parameter

What we’re going to do is get the values for each measure that is associated to the pSelectedPeriod, and display that value over every row.

Curr Sales

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Sales] END))

If the Period matches that in the parameter, then get the Sales value and then use Window_MAX to ‘spread’ that value over every row

Add this to the table, and edit the table calculation to ensure the value is being computed by Period.

Repeat the same for Profit and Quantity

Curr Profit

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Profit] END))

Curr Qty

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Quantity] END))

If you change the parameter, you should see all the values in the last 3 columns changing to reflect the value from the first 3 columns of the relevant row.

Now we need to get the value from the previous period, ie the data from the previous row

Prev Sales

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Sales]),-1) END)

The LOOKUP function is taking the Sales value from the previous 1 row (-1), and then WINDOW_MAX is once again ‘spreading’ this value across every row. We also need

Prev Profit

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Profit]),-1) END)

Prev Qty

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Quantity]),-1) END)

Add these to the table, and again adjust the table calc settings for every field to compute by Period.

Now we have the current and previous values for each measure, we can work out the % difference

Diff Sales %

([Curr Sales]-[Prev Sales])/[Prev Sales]

Format this to a % with 1 dp

Diff Profit %

([Curr Profit]-[Prev Profit])/[Prev Profit]

Diff Qty %

([Curr Qty]-[Prev Qty])/[Prev Qty]

Add all these onto the sheet, and remember the table calc settings (for these, there are nested table calcs, so make sure both are set properly).

We now need an arrow indicator to display up or down depending on the % value, and this needs to display as a different colour, so we need two fields per measure.

Diff Sales Indicator +ve

IF [Diff Sales %] >= 0 THEN ‘â–²’ END

Diff Sales Indicator -ve

IF [Diff Sales %] < 0 THEN ‘â–¼’ END

I use this site to copy the text symbols from

Diff Profit Indicator +ve

IF [Diff Profit %] >= 0 THEN ‘â–²’ END

Diff Profit Indicator -ve

IF [Diff Profit %] < 0 THEN ‘â–¼’ END

Diff Qty Indicator +ve

IF [Diff Qty %] >= 0 THEN ‘â–²’ END

Diff Qty Indicator -ve

IF [Diff Qty %] < 0 THEN ‘â–¼’ END

Add these to the sheet if you wish too (apply the table calc settings), but you’ll only get a value for one or the other field depending on whether the difference was +ve or -ve. Below I’ve just added the two Sales indicators

The Tooltip displays the value of the two Periods being compared. One of these is in the parameter, but we need to capture the other

Previous Period

WINDOW_MAX(IF [pSelectedPeriod] = MIN([Period]) THEN LOOKUP(MIN([Period]),-1) END)

So now we have all the values we need for the KPIs captured against every row in the dataset. So now we want to just show a single row. It could be the first, it could be the last… based on Lorna’s hint, let’s filter to just show the row related to the pSelectedPeriod value.

Filter Selected Period

[pSelectedPeriod] = LOOKUP(MIN([Period]),0)

Using the offset of 0 with the LOOKUP, returns the value for the row you’re on, so adding this to the FIlter shelf and selecting True, filters the display to the row where the Period matches the parameter. NOTE if you adjust the table calc settings of this field after adding to the filter shelf, you’ll need to reselect the option to filter to True.

As this is a table calculation, the ‘filter’ is applied later in the order of operations, so information about the other rows in the table can be referenced. Filtering just by Period as a quick filter, is essentially a dimension filter and that happens earlier on in the process, meaning the data about the other rows would be inaccessible.

So we have all the fields, now build the cards.

Building the KPI Cards

On a new sheet, double click into Columns and type in MIN(0). Repeat this 2 more times. This gives us 3 axis to build each of the 3 cards.

On the All marks card, add Period to the Detail shelf. Show the pSelectedPeriod parameter. Add the Filter Selected Period to the Filter shelf and set to true (adjusting the table calc and resetting the filter value as required).

Change the mark type of the All marks card to shape and select a transparent shape (see here for more details).

On the first MIN(0) marks card, add Curr Sales, Prev Sales, Diff Sales %, Diff Sales Indicator +ve and Diff Sales Indicator -ve to the Label shelf. Remember to apply the table calc settings for all of the fields!

Adjust the text within the label, so it is formatted and positioned as required and then align middle centre.

On the middle MIN(0) marks card, do similar by adding the equivalent profit fields onto the Label shelf, and then repeat again for the bottom MIN(0) marks card, adding the quantity fields to the Label shelf.

On the All marks card, add Previous Period, Curr Sales, Curr Profit, Curr Qty Prev Sales, Prev Profit, Prov Qty, Diff Sales %, Diff Profit % and Diff Qty % to the Tooltip shelf (remember to set those table calc settings!) Then adjust the Tooltip to display the text as required. I used the ruler to shift the starting position, along with tabs (the tab keyboard button) to ‘try’ to get everything to align, and it works for most circumstances….

Finally format the KPI card

  • Set the worksheet background colour to light grey
  • Remove all gridlines, zero line, axis lines
  • Set the column divider to be a thick white line
  • Set the row divider to be a thick white line
  • Hide the axis

Add the sheet onto a dashboard, and you should be done.

My published viz is here.

Happy vizzin’!

Donna

Can you create an area chart tile map?

Donna ColesLeave a comment

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

Centre Date

DATE(DATEADD(‘year’,
FLOOR((YEAR({FIXED:MAX([Date])}) – YEAR({FIXED:MIN([Date])}))/2),
{FIXED:MIN([Date])}
))

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

YEAR({FIXED:MAX([Date])}) – YEAR({FIXED:MIN([Date])})

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.

And that should be it! My published viz is here.

Happy vizzin’!

Donna

Can you include and exclude options with a custom control?

Donna ColesLeave a comment

It was my turn this week to set the challenge which I based on an example of a viz that I’d seen at work.

I built the viz using 3 sheets and using techniques similar to that described in a previous challenge documented here where I used parameter actions. It’s likely set actions can be used too, although I haven’t tested this out.

Building the bar chart

Format Sales to $ with 0dp, then add State/Province to Rows and Sales to Columns and sort descending. Add State/Province to Filter and filter by Top 5 based on Sales.

Double click on the Columns shelf and manually type MIN(0) to create a second axis. Make the chart dual axis and synchronise the axis. Change the mark type of the Sales marks card to Bar and the mark type of the MIN(0) axis to Gantt. Add Measure Names to Rows.

Remove Measure Names from the Colour shelf of both marks cards. Then add State/Province and Sales the Label shelf of the MIN(0) marks card. Arrange the text to match.

Reduce the size of the MIN(0) gantt bar to as small as possible, and reduce the opacity of the mark (via the Colour shelf) to 0%.

Update the Tooltip on the Sales marks card to match, but delete all text from the Tooltip of the MIN(0) marks card.

Add State/Province to the Colour shelf of the Sales marks card, and adjust accordingly. I chose to set the opacity to 50% based on the colours I chose.

Hide both axis, and both row heading (uncheck show header). Remove all gridlines, row/column dividers, axis ruler etc.

Name the sheet Bar or similar. We’ll come back to this later.

Building the trend line

On a new sheet, add Order Date to Columns set to a continuous monthly level (green pill) and Sales to Rows. Add State/Province to Colour (and reduce opacity if need be).

Go back to the bar chart sheet, and apply the State/Province filter to the trend line sheet too (right click on the pill in the filter shelf > apply to worksheets > selected worksheets).

Add a Running Total quick table calculation to the Sales pill (right click > quick table calculation > running total).

Add State/Province to the Label shelf, and set the font of the label to match mark colour. Adjust the text in the Tooltip. Format the Running Total Sales pill on the Rows shelf to be in $k with 0 dp. Delete the titles of both axis, and reduce the font size on the axis. Remove axis rulers/tick marks, zero lines, row/column dividers. Name the sheet Trend or similar.

Now, we need to be able to capture which States are being excluded. We need a parameter for this.

pUnselectedStates

string parameter defaulted to empty string/nothing

Show the parameter and manually type ‘Texas’ into the field. Based on what is in this parameter, will determine whether the state is unselected or not.

State is unselected

CONTAINS([pUnSelectedStates], [State/Province])

This returns True if the associated State/Province is listed in the parameter (so shouldn’t be visible) and False if it’s not listed (so should display).

Add State is unselected to the Filter shelf and set to False. The line for Texas will now disappear

Experiment by typing multiple states into the parameter eg Texas California

Building the selection control

On a new sheet add State/Province to Rows and sort by the Sales descending

Set the State/Province filter from another sheet to also apply to this sheet.

Double click into Columns and type MIN(0). Add State/Province to Colour (and apply opacity if required – I set this to 75%). Set the mark type to Shape and add State is unselected to shape. Use the Ratings shape palette to set the options.

We want the Tooltip to say something different, depending on whether the State/Province is selected or not.

Tooltip Include/Exclude

IF [State is unselected] THEN ‘include’ ELSE ‘exclude’ END

Add this to the Tooltip shelf, then update the text

We want to align this sheet with the bar chart, so we need to add another instance of MIN(0) to Columns. Make the chart dual axis and synchronise the axis. Remove Measure Names from the Colour shelf of both marks cards. Add Measure Names to the Rows shelf.

On the first MIN(0) marks card, remove all the fields from the card, reduce the size to as small as possible, and the opacity to 0%. Set the Shape to be a transparent shape (see this post for more details).

Now, we need a way to populate the pUnSelectedStates parameter with values from the selector sheet itself. Given the set of ‘unselected’ states can ‘build’ up, we can’t just pass in a single state from the State/Province field itself. We need to capture a delimited list of the states as they are unselected & reselected. We will use the | character as the delimiter, and will modify the string as follows

ActionpUnSelectedStates
All states selected<empty string>
1 state unselected eg Texas|Texas|
2 states unselected eg Texas then California|Texas||California|
1 state then reselected eg Texas then California unselected, Texas reselected|California|
1 state reselected again eg Texas then California unselected, Texas reselected, then California reselected<empty string>

This is captured within this logic

States for Param

IF CONTAINS([pUnSelectedStates], [State/Province]) THEN
//state already in parameter, so remove it
REPLACE([pUnSelectedStates],’|’ + [State/Province]+ ‘|’,”)
ELSE
//add state to string
[pUnSelectedStates] + ‘|’ + [State/Province] + ‘|’
END

If the State is already stored in the parameter, then remove it, by replacing the text with ” (empty string), otherwise append the state to the existing text in the parameter.

Add this field to the Detail shelf of the first MIN(0) marks card.

Hide all the axis and the row headings. Remove all row/column dividers, gridlines, zero lines, axis rulers etc. Reset the parameter back to an empty string.

Name the sheet Selector or similar.

Identifying if the bars are selected or not

Before we build the dashboard, we can now tweak the bar chart, so the colour of the bars reflects whether the State/Province is selected or not.

Back onto the bar chart sheet, add State is unselected to the Detail shelf of the Sales marks card. Then use the icon next to the pill and change it from Detail to Colour to add a secondary pill to the Colour shelf

Swap the pills around, so the State is unselected pill is above the State/Province pill, and the colour legend should list entries as False, <State>. Readjust the colours if need be.

Now enter ‘Texas’ into the pUnselectedStates parameter. The legend will update so that True,Texas is now listed. Adjust the colour of this option to white, and set a light grey border (via the Colour shelf), so the outline of the bar is visible.

Go through each listed State/Province adding it to the parameter and the adjusting the True, < State> colour legend to white each time.

Once done, set the pUnselectedStates parameter back to empty string.

Building the dashboard and adding the interactivity

Using containers, add the objects to a dashboard. I used a horizontal container to store the 3 charts, but the selector and the bar chart were also then contained within their own horizontal container. All charts were set to fit entire view. This allows for a border to be set around both objects. If everything is set right, you should be able to get the selector circles aligned with the bars.

I also used background colours and padding to get everything displayed as required.

Add a dashboard parameter action to capture the user interactivity

Unselect State

on select of the Selector sheet, update the pUnselectedStates parameter by passing the value of the States for Param field. Keep current value when unselected.

Finally to prevent the selected option from remaining ‘highlighted’ and all the other options becoming ‘faded’ out’, we can use the True/False trick described here.

My published viz is here.

Happy vizzin’!

Donna

Top & Bottom Variance

Donna ColesLeave a comment

For #WOW2024 Week 6, Sean challenged us to create a viz depicting the top and bottom entries based on a variable set by the user. He instructed that no LoDs (level of detail calculations) should be used, and then also added a bonus to complete the challenge without using sets either (and so hinting that sets would solve the problem). I built both, and will provide the solution for both.

Setting up the common fields

Regardless of the solution, various calculated fields and parameters are required.

pTop

integer parameter defaulted to 12, which is used to drive how many entities to display in our top & bottom cohorts.

For the user to select a month, I first created a new field

Month of Order Date

DATE(DATETRUNC(‘month’, [Order Date]))

and I changed the default sort order of this field to descending (right click the field > default properties > sort > choose descending in the disalog box).

I then created a parameter

pSelectedMonth

date field that selects from a list that is populated when the workbook opens from the Month of Order Date field. Default to July 2021 and format the display to a custom format of mmmm yyyy – having defined the sort order to be descending, the most recent month will be at the top (this wasn’t in the requirements, but is just a useful trick to know).

Now we have a handle on the month we want to report over, we can create

Selected Month Sales

ZN(IF [Month of Order Date] = [pSelectedMonth] THEN [Sales] END)

Return the sales only if the month is that selected, otherwise return 0 (this is what the ZN function will do)

Format this to $ with 0dp.

Previous Month Sales

ZN(IF [Month of Order Date] = DATEADD(‘month’, -1, [pSelectedMonth]) THEN [Sales] END)

Return the sales only if the month matches the previous month to that selected, otherwise return 0.

Format this to $ with 0dp.

and with these, we can then create

Difference in Sales

SUM([Selected Month Sales]) – SUM([Previous Month Sales])

Format this to $ with 0dp too.

And we can also create

Colour – Diff > 0

[Difference in Sales] > 0

Pop all these into a table sorted by Difference in Sales ascending to see what it all looks like.

Solution 1 – Using sets

Right click on the State/Province field and create > set

Top States

using the Top condition to get the Top n States where n is the value from the pTop parameter, based on the Difference in Sales field.

and then create another set

Bottom States

using the Top condition to get the Bottom n States where n is the value from the pTop parameter, based on the Difference in Sales field.

Then right click on one of these states and create combined set

Top & Bottom

shows all members in both the Top States and the Bottom States sets

Add Top & Bottom to the Filter shelf. By default only the records ‘in’ either of the sets will display. Modify the pTop parameter to see the list change.

To build the viz

  • add State/Province to Rows
  • Add Difference in Sales to Columns and sort ascending
  • Add Top & Bottom to Filter
  • Add Colour – Dff > 0 to Colour and adjust accordingly.
  • Add Selected Month Sales to Tooltip and adjust
  • Show the pTop and pSelectedMonth parameters

To display the name of the month selected as a column header, create

Selected Month

[pSelectedMonth]

and add to Columns as a discrete (blue) pill at the month-year level. Hide field labels for rows and columns, remove all row/column dividers, zero lines, axis rules & tick marks.

Solution 2 – not using sets

This solution involves using the Rank table calculation

Create a new field

Asc

RANK_UNIQUE([Difference in Sales],’asc’)

and another called

Desc

RANK_UNIQUE([Difference in Sales],’desc’)

Revert back to the data table we built and remove the Top & Bottom set filter. Now add Asc and Desc to the output.

There are now two fields indexing each row; one which starts at 1 and increments, and the other that decrements so the last row is 1. We can now use these to filter just to the records we want

Records to Include

[Asc]<= [pTop] OR [Desc]<=[pTop]

Add this to the Filter shelf and set to True, and we can now see our top 12 and bottom 12 listed

Build the viz exactly as described above, but this time, add the Records to Include field onto the Filter shelf instead.

Pop these onto a dashboard and job done!

My published viz is here : Set Solution | Rank Solution

Happy vizzin’!

Donna

Excel at Tableau Interactivity with Dynamic Zone Visibility

Donna ColesLeave a comment

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 Measure Values 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 objects you want to hide rather 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.

Happy vizzin’!

Donna