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