Tag: Hex Maps

Can you compare state sales – Part 2?

Donna Coles2 Comments

This week’s #WOW challenge is an extension of this challenge which I blogged about here. The premise is to mimic a real world scenario – a dashboard has been built and used, but now, a few months after use, additional functionality is required. I am going to be building on top of the solution I delivered for week 11. If you participated in that week, your solution may not have matched mine, so this guide may not be as coherent.

Identifying the selected states

In part 1, we captured the single state selected within a parameter pSelectedState via a parameter dashboard action. We now need to be able to capture multiple states. We’re still going to use the same parameter and a parameter action, but we need to adapt what will be stored within in it.

Instead of a single state being stored in the parameter, we’re now going use this parameter to build up a delimited list of the states selected. We can then interrogate that string to determine the first and the second state selected, and identify if any additional states have been selected.

When using delimited strings, you need to choose a character to separate the field values that isn’t going to appear within the field value itself; ie choose a character that won’t appear within a State name. In this instance I’m choosing | (pipe), and I’m going to build up the parameter as follows

ActionpSelectedState
No states selected<empty string>
1 state selected eg Arizona|Arizona|
2 states selected eg Arizona then Utah|Arizona||Utah|
3 states selected eg Arizona, then Utah, then Colorado|Arizona||Utah||Colorado|
Existing state is selected again (trigger to reset the view)<empty string>

Based on the logic above, we need to modify the existing field

State for Param

IF CONTAINS([pSelectedState], [State]) THEN ” //selected state is already in the parameter, so reset back to ”
ELSE [pSelectedState] + ‘|’ + [State] + ‘|’ //append current state selected to the existing parameter string
END

To see how this is working, go the the dashboard and display the pSelectedState parameter. Click on states and see how the parameter is changing.

As we interact with the hex maps, we can see the list of states building up in the parameter. We’ve obviously lost some of the other functionality now, but that’s ok, we have more things to adapt.

We can now manipulate this string to identify the first two states selected

First Selected State

SPLIT([pSelectedState],’|’,2)

returns the string that comes before the 2nd instance of the ‘|’ (and after the 1st)

Second Selected State

SPLIT([pSelectedStateString],’|’,4)

returns the string that comes before the 4th instance of the ‘|’ (and after the 3rd).

We can then use these fields to set the colour for the map

State Colour

IF [State] = [First Selected State] THEN ‘First’
ELSEIF [State] = [Second Selected State] THEN ‘Second’
ELSE ‘Other’
END

Replace the Is Selected State field that is currently on the Colour shelf and the Shape shelf of the Column (2) marks card, with this new State Colour field. Ensure you have at least 2 states listed in the pSelectedState parameter, so you have options for First, Second, Other all listed in both the colour and shape legends.

Adjust the Shape legend and set the First and Second values to use the same hexagon shape used initially, but set the Other option to use a transparent shape.

Adjust the colours. Increase the opacity on this mark to 100% Set the colours as you wish – I used:

  • First – teal : #66b3c2
  • Second – light teal : #bce4d8
  • Other – pale grey : #d3d3d3

Click around on the dashboard. You should find as you click the first state it is coloured dark teal. Click a second, it is coloured light teal. Click either selected state again, and the map resets. Click more than 2 states, and only the first 2 are coloured, whilst the parameter continues to capture all the states being clicked.

Alerting if more than two states are selected

We will need a field to identify if more than 2 states have been selected.

More than 2 Values Selected?

SPLIT([pSelectedStateString], ‘|’, 6) <> ”

This is looking for a 3rd instance of a value within the delimited string, and returns true if there is one.

As this is a boolean field, we can use it to control the visibility of the message text box we want to display.

We also need to ensure that when the user clicks on the message, it disappears, and the hex map is re-set, ie the pSelectedState parameter gets set back to empty string. Another field will help us with this

State Reset

”

On a new sheet, add State Reset to Text. Then edit the text to the relevant message, and align centrally. Change the tooltip to say ‘click here to reset’. Set the background colour of the sheet to a pale grey. Name the sheet ‘Alert’ or similar.

On the dashboard, delete the contents of the pSelectedState parameter and remove it from the dashboard. Add the Alert sheet as a floating object. Set to fit entire view and remove the title. Add a border to the object.

Select the object, then in the Layout pane, set the Control visibility using value option to the More than 2 Values Selected field.

Then add a dashboard parameter action to reset the parameter when the message is clicked

Reset States

On select of the Alert object, set the pSelectedState parameter to the value stored in the State Reset field.

Click around, selecting more that 2 states and test the behaviour. Now we can move on the adapting the other charts.

Adapting the calculations

To sense check what is going on, on a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter. Add State Label to Rows, and show the pSelectedState parameter.

When only 1 state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ . When 2 states have been selected we just want the state names against the 2 relevant rows. Otherwise we want NULL to show. We need to adapt the State Label field as follows

State Label

IF [First Selected State] <> ” AND [Second Selected State] = ” THEN
IF [State] = [First Selected State] THEN [State] ELSE ‘Other States (Avg)’ END
ELSEIF [First Selected State] <> ” AND [Second Selected State] <> ” THEN
IF [First Selected State] = [State] OR [Second Selected State] = [State] THEN [State] END
END

Now we need to display a different value for the sales measure depending on whether we have 1 or 2 states selected. So we need to adapt the existing Sales To Display field

Sales To Display

IF CONTAINS([pSelectedState], MIN([State Label])) THEN SUM([Sales])
ELSE SUM([Sales])/COUNTD([State])
END

If the parameter string contains the State Label value, then use the sales, otherwise average the sales over the number of states that make up the sales. Format this to $ with 0dp.

Add this to the table, and remove State from the display

Add State Label to the Filter shelf and exclude NULL. Now remove the second state from the parameter and check the result

Adapting the bar chart

On the existing bar chart sheet, verify the pSelectedState parameter is displayed. Replace Is Selected State on the Colour shelf with the State Colour field and remove Is Selected State from the Rows shelf.

Add another state to compare against. Exclude the NULL values that display (add State Label to Filter and exclude Nulls)

We always want to make sure that the first state selected is listed first.

Sort- State Label

IF [State Label] = [First Selected State] THEN 1
ELSEIF [State Label] = [Second Selected State] THEN 2
ELSE 3
END

Add this field on to Rows as a discrete (blue) pill, between the existing two pills and then hide it.

Adapting the line chart

On the existing line chart sheet, verify the pSelectedState parameter is displayed

Add State Label to Filter and exclude null. Replace Is Selected State on the Colour shelf with the State Colour field.

Displaying the additional charts on click.

The display of the bar and line chart are based on the dynamic zone visibility functionality, and the field Show Viz (if you have downloaded my original solution workbook to build onto, you may need to unhide fields in the data pane for this field to be visible).

Amend Show Viz to

[First Selected State] <> ”

Amending the Chart Title sheet

On the Title sheet, replace the Is Selected State on the Filter shelf with Show Viz = TRUE.

Remove State Label from the Text shelf and add First Selected State and Second Selected State to Text. Additionally create a calculated field

Label:All State

IF [Second Selected State] = ” THEN ‘Other States (Avg)’ ELSE ” END

Add this to Text too.

Adjust the text as below and apply matching colour coding to the fonts. Align the text centrally.

Finally update the title and instructions on the dashboard.

My published viz is here.

Happy vizzin’!

Donna

Can You Compare State Sales? Part 1

Donna Coles2 Comments

This week’s #WOW challenge was set by me and born out of a client requirement to compare store locations via selections from a map. I adapted it to use our favourite Superstore dataset (v2022.4).

The core requirement is to be able to make a selection on the map and see how sales compare to the average of all the other states. There is more to this challenge, but it was too much for one week, so I’ve broken it into 2 parts. This is part 1. In a few weeks time, I’ll be building on this solution for a part 2.

Building the basic hex map

The requirements provide a link to here to get the relevant files needed to complete this challenge and build the hex map – this includes the 2022.4 version of Superstore, the Hex map template, the hexagon shape file and a transparent shape file (more on that one later).

Using the hex map template sheet provided, relate the Orders Superstore data to the hex map sheet, relating State/Province to State.

Then on a new sheet add Column to Column, Row to Row and State to Detail. Edit the Row axis, and reverse the scale.

Change the shape of the mark to be a hexagon (use the provided shape if need be and add to a custom shape palette), and increase the size of the marks. Add Sales to Colour and change to use the Grey sequential colour palette, and adjust the opacity to 80%. Add Country/Region to the Filter shelf and select United States. This will remove Alaska and Hawaii that don’t have any sales and aren’t in the Superstore data set.

Add Abbreviation to the Label shelf and align centrally, Adjust the font size if need be.

Identifying the selected state

We need to be able to capture the state that has been selected ‘on click’. This will be driven by a dashboard action.

When I first built this concept for a client, the natural first step was to utilise sets and set actions; that is capture the selected state in a set, and then colour the map, build the other charts and logic based on the existence in the set. However this method does caused some issues when I tried to prevent the highlighting later, so I chose to use a parameter and parameter actions instead.

Firstly we need a parameter that will be used to capture the state selected ‘on click’

pSelectedState

string parameter defaulted to <empty string>

We can then create

Is Selected State

[State] = [pSelectedState]

As we want to retain the colour by sales on the existing map, we need to make a dual axis. Show the pSelectedState parameter and type in ‘Florida’.

Add another instance of Column to Columns. Remove the Abbreviation label from the second marks card, and replace the Sales field on the Colour shelf with the Is Selected State field. Increase the opacity on this mark to 100% Set the colours as follows :

  • True- teal : #66b3c2
  • False- pale grey : #d3d3d3

Additionally, add Is Selected State to the Shape shelf of the second marks card. Set the True value to use the same hexagon shape, but set the False option to use a transparent shape (a transparent shape file is provided in the g-drive, and needs to be added as a custom shape).

This should make all the other states look like they disappear.

Make the chart dual axis, and synchronise the axis.

Format the Sales to be $ with 0dp, then add to the Tooltip shelf of the second marks card and adjust the tooltip. Remove all gridlines/row & column dividers and hide the axes.

Adding the Interactivity

Create a dashboard sized 1000 x 600.

Add a vertical container and add a text field to create the title. Below the title, add a horizontal container. Add the hexmap sheet into the horizontal container, then add a blank object to the right of it. Remove any legends etc that automatically get added. Set the width of the hexmap object to be fixed to 600 px

and set the height of the horizontal container the hex map sits within to 445px

Show the colour legend and set it to floating, and position bottom left.

Add a parameter action to set the selected state on click

Set Selected State

On selection of the Hexmap sheet, pass the State field into the pSelectedState parameter. Set the value to <empty string> when the selection is cleared.

If we click around on the dashboard page, we can see the colours being set, but everything is fading out on selection. To prevent this, create 2 calculated fields

True

TRUE

False

FALSE

Add these to the Detail shelf of the All Marks card of the hex map sheet. Then back on the dashboard, add a filter dashboard action

Deselect Map Marks

On select of the hex map object on the dashboard, target the hex map sheet itself, setting selected fields such that True = False. Show all values where selection cleared.

Now if we click on the dashboard, the shapes shouldn’t fade into the background. However we can’t ‘unselect’ the state and get back to its original state. This is because the filter action we added to stop the marks from fading actually unselected the mark, so when we click again, we’re not undoing any selection.

To resolve this, we need another calculated field.

State for Param

IF [State] = [pSelectedState] THEN ”
ELSE [State] END

If the state being clicked is the one already captured in the parameter, then set the field to <empty string>, else set the fields to the state being clicked.

Add this to the Detail shelf of the All marks card on the hexmap sheet, and then update the Set Selected State parameter action to pass the State For Param field into the parameter instead of the original State field..

Now when the map is first loaded, the State For Param field contains the name of the state, so that is passed into the parameter on click. As the parameter now has a value, the State For Param field changes for the selected state to be <empty string>, so if the same state is clicked, <empty string> is then passed into the parameter and the view resets.

Building the calculations for the other charts

On a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter.

When a state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ .

State Label

IF [Is Selected State] THEN [State] ELSE ‘Other States (Avg)’ END

Now we need to display a different value for the sales measure depending on whether its the selected state or not.

Sales To Display

IF ATTR([Is Selected State]) THEN SUM([Sales])
ELSE SUM([Sales])/COUNTD([State])
END

If the state is the selected one, then use the sales, otherwise average the total sales over the number of states that make up the sales. Format this to $ with 0dp.

Note – in hindsight, this could have just been SUM([Sales])/COUNTD([State]) even for the single state, as since the count of state will be 1, this would just equate to the SUM([Sales]) itself.

Add this to the table, and remove State from the display

Building the bar chart

Duplicate this sheet (as we want to retain the filters), then move Sales To Display to Columns, and add Category to Rows in front of State Label. Add Is Selected State to the Colour shelf.

To ensure the Selected State is always listed first, even if alphabetically it comes after ‘Other States (Avg)’, add Is Selected State to the Rows shelf between Category and State Label. Manually sort it so True is always listed before False, then hide the column (uncheck show header).

Reduce the Size of the bars, remove gridlines and column dividers. Lighten the row dividers. Adjust font sizes. Hide the column labels (hide field labels for rows) and hide the axis.

Remove Sales from the Text shelf, and check Show mark labels instead. Update the Tooltip.

Building the line chart

On a new sheet add Country/Region to Filter and set to United States. Add Order Date to Columns and set to the continuous month level (eg May 2021). Add Sales To Display to Rows, State Label to Detail and Is Selected State to Colour. Manually move the values in the colour legend so that True is listed first. Adjust the tooltip

Remove the axis titles, adjust the axis fonts. Remove row/column dividers and zero lines and axis rulers.

Putting it all together

On the dashboard, add a vertical container between the hexmap and the blank object. Add the line chart and bar chart on top of each other. Remove the title for the bar chart, and update the title of the line chart to reference the pSelectedState parameter.

Remove the blank object to the right of the bar/line charts.

We need to control when the bar and line charts display, so we’ll use dynamic zone visibility for this, and for this we need another boolean field

Show Viz

{FIXED: MAX(IF [Is Selected State] THEN TRUE ELSE FALSE END)}

Is Selected State is a boolean field which essentially is 1 for True and 0 for False. If there is a state selected, the maximum value across all the data records, will be 1, so the field returns true, otherwise its 0, so false.

Use this to control visibility using value for the line and bar chart objects.

Make any further adjustments to the layout required -the size of the hex shapes may need tweaking for example. Then interact with the viz to check all is working as expected.

My published viz is here.

Happy vizzin’!

Donna

Let’s Build a Map!

Donna ColesLeave a comment

Inspired by a viz from Klaus Schulte, Sean Miller set this week’s challenge to recreate a hex map with state shapes using Superstore.

Building the data model

I referenced this Tableau blog post and downloaded the HexmapPlots excel file included. I then used a relationship to ‘join’ the Sample – Superstore excel file I was using with the HexmapPlots file, joining on State/Province = State

Since I had the other blog post already open, I then followed the steps included to start building the map.

Building the hex map

Add Column to Columns and aggregate to AVG, and add Row to Rows and also aggregate to AVG. Add State/Province to Detail. Edit the Rows axis and set to be Reversed.

Note – it’s possible you may have extra States showing. As I’m writing I’ve realised I’m rebuilding against an extracted data source that has a filter I originally applied as a global filter, which has now been included in the extract. So you may need to add State/Province to the Filter shelf, and set to exclude NULL and District of Colombia. This filter will need to be applied to all sheets you build.

Change the mark type to Shape and select a Hex shape. I already have a palette full of Hex shapes, but the blog post provides a shape to use and add as a custom shape if you haven’t got one. Increase the size of the marks.

Create a new field

Profit Ratio

SUM([Profit]) / Sum([Sales])

format this to % with 1 dp, and then add to the Colour shelf.

Now add a second instance of Row to the Rows shelf and set to AVG again. Set the mark type of this marks card to Map. Remove the Profit Ratio field from Colour on this card too. Assuming your Map location is set to USA, you should have State outlines depicted (Map -> Edit Location).

Set the chart to be dual axis and synchronise the axis. The State shape axis should now be inverted. Independently adjust the sizes of the Hex shape and the state shapes, so the states sit inside the hexagons.

Right click on the right axis and move marks to back. The adjust the Colour of the hex shapes so its around 85% transparent.

Now adjust the Tooltip on the hex marks to match the requirement.

To fill-out the available area, I also chose to fix both the axis (right click axis -> edit axis). The Column x-axis I set to range from 1 – 12, and the Row y-axis, I set to range from -0.9 – 9. Then hide all axis, and remove all row/column gridlines, divider lines, axis lines and zero lines. Hide the 10 unknown indicator, and set the background colour of the whole worksheet to a pale grey.

Building the Line Chart

This is super simple, Tableau 101 🙂

Add Order Date to Columns and set to be a continuous month (green pill showing month-year). Add Sales to Rows. Change the colour of the line to grey. Hide the x-axis and remove all gridlines, dividers, axis lines and zero lines. Set the background colour of the worksheet to None (ie transparent). Update the tooltip.

Building the Scatter Plot

Add Sales to Columns, and Profit to Rows. Add State/Province to Detail shelf and add Profit Ratio to Colour. Change the mark type to circle. Remove all gridlines, row column dividers and axis rulers. Only the zero lines should remain. Adjust the tooltip and set the worksheet background to None (transparent).

Putting it all together

Create a dashboard and set the size as stated. Set the background of the dashboard to pale grey (Dashboard – > Format).

Add the Hex map, and hide the title. Click on the Profit Ratio legend object and set to be floating. Then remove the right hand vertical container. Move the Profit Ratio legend to a suitable location.

Then add a text box as a floating object and use it to create the title. Add both the trend line and the scatter plot charts as floating objects without titles. Just position them as required. You can always use gridlines (Dashboard -> Show Grid) to help you line things up.

Finally add the interactivity.

Add a highlight dashboard action which highlights the hex map and the scatter plot when either of the other is selected ‘on hover’, and just targets the State/Province field.

Then add a Filter action which on hover of the Trend chart, targets the remaining charts.

And hopefully that’s it. My published viz is here.

Happy vizzin’!

Donna