It was Lorna’s turn to set the challenge this week, based on a real world scenario she’s encountered with a colleague. The premise was to be able to show the progress, by country, against a maximum of 6 goals selected by the user. Placeholders for the 6 options had to remain visible at all times (unless the user selected more than 6, in which case a message should appear).

Building the core viz

As Lorna alludes to in the requirements, we will use sets to capture the goals the user can select.

SDG Name Set

Right click on SDG Name > Create > Set. Select up to 4 options.

On a new sheet, add SDG Name Set and SDG Name to Columns, Country to Rows and add Country to the filter shelf, and limit to Australia, Finland, UK & USA. Right click on the SDG Name Set field in the data pane and select Show Set

We need the SDG Name value to only display for the values selected

SDG Name Header Label

IF [SDG Name Set] THEN [SDG Name] ELSE ” END

Add this to Columns before the SDG Name field.

Now we need to always display 6 columns of data, ie in this case, all the SDG Name values In the set and the first SDG Name values not in the set. We will use the INDEX() function to help us label the columns position.

INDEX

INDEX()

Right click this field and Convert to discrete, then add it to the Columns after SDG Name.

Edit the table calculation (click on the triangle symbol on the blue INDEX pill), and adjust so it is computing by Specific Dimensions. This should be all the fields except Country. The columns should be labelled sequentially from 1 to 17.

Add INDEX to the Filter shelf as well. Initially just select 1. Then adjust the table calculation of this field to match above, and once done, edit filter and select values 1 through to 6. This should leave you with 6 columns

Now we have the structure, we can start building the core contents of the table. For this we’ll be using what I refer to as ‘fake axis’.

Double click in the Rows shelf and manually type in MIN(0.2), then double click again and manually type in MIN(0.6). This results in the creation of a MIN(0.2) and a MIN(0.6) marks card on the left hand side.

The MIN(0.2) marks card is going to be used to show the information about how the goal is trending (ie the arrow symbol), while the MIN(0.6) marks card will be used to show the status of the goal (the text displayed). We need new fields for this, so the values only display for the selected goals.

Trend

IF [SDG Name Set] THEN [SDG Trend] END

Goal

IF [SDG Name Set] THEN [SDG Value] END

Click on the MIN(0.2) marks card. Change the mark type to shape. Add Trend to the Shape card. Right click on the Trend pill and change the field from a Dimension to an Attribute.

Doing this stops the field from impacting the table calculation, and you should get back to having 6 columns displayed.

Adjust the shapes using the Arrows shape palette. For the Null value, I set it to use a transparent shape, a custom shape added to my shape palette. See this blog for more information on doing this.

Also add Trend to the Colour shelf. Once again, adjust the pill to be Attribute and then adjust the colours.

Now click on the MIN(0.6) marks card. Adjust the Mark Type to be Text. Add Goal to the Text shelf and change to be and Attribute. Add Goal to the Colour shelf, change to be Attribute and adjust colours to suit. I also adjusted the font to be bold & size 10pt.

Set the chart to be dual axis and synchronise the axis. Edit the axis (Right click on the left hand axis) and fix the axis from 0 to 1.

Hide the axis, the IN/OUT SDG Name Set pill, the SDG Name and INDEX pills (right click the pills and uncheck Show header).

Right click on the Country row label and the SDG Name Header Label column label in the viz and hide field labels for rows/columns.

Right click within the viz to format. Set the background colour of the pane to light grey.

Remove all gridlines, axis rules, zero lines. Set the column and row dividers to thick white lines

Click the Tooltip button on the All marks card, and uncheck show tooltips. Set the viz to Fit width.

Building the Goal legend

On a new sheet, add SDG Value to Columns. Change the mark type to Circle then add SDG Value to Label and Colour. Manually re-order the columns and adjust the colours as required.

Double click in the Columns shelf and type in MIN(0.0). Edit the axis and fix from -0.1 to 0.5 – this will shift the symbols and text to the left.

Adjust the size of the circle shape to suit, and set the font of the label to match mark colour. I also set it to be bold.

Remove all gridlines, axis ruler, zero lines. Set the background of the pane to be grey and the row/column dividers to be thick white lines. Hide the axis and the column headers, and uncheck show tooltips.

Double click into the Rows shelf, and type the text ‘Goal’ (including the quotation marks). Hide the ‘Goal’ label that then displays.

Building the Trend legend

Repeat similar steps for above but add the SDG Trend field to the Colour, Label and Shape shelf. Adjust the shape & colours to those used before.

Handling more than 6 selections

For this requirement, we need to determine the number of items in the set.

Count Set Members

{COUNTD(IF [SDG Name Set] THEN [SDG Name] END)}

and then use this to create some boolean fields

More than 6 Selected

[Count Set Members] > 6

Less than 6 Selected

[Count Set Members] <= 6

Ensuring that less than 6 items are selected, add the Less than 6 Selected field to the Filter shelf of the main table viz and set to True.

If you select more than 6 goals, the viz should disappear.

On a new sheet, double click into the space beneath the marks card where the pills usually sit, and type ‘Dummy’ (with quotes). Change the mark type to shape and set to use the transparent custom shape. Move the Dummy pill to the label shelf, then edit the label and change the text to the error message.

Align the text middle centre and fit to entire view. Uncheck show tooltips. Add More than 6 Selected to the filter shelf and select true (if true isn’t an option, go back to the main viz, and select more options sp the viz disappears, then come back to this sheet and try again).

All the sheets can now be added to the dashboard. Ensure the core table viz and the error sheets are added to a vertical container without the title showings – the charts should expand and collapse as the selections are made – this can be a bit tricky to get right.

My published viz is here.

Happy vizzin’!

Donna