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

Action	pUnSelectedStates
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