It was Sean’s final #WOW2022 challenge of the year, and he set this task to provide alternative options for visualising time series across dimensions with high cardinality.

I had a play with Sean’s viz before I started tackling the challenge and noticed the following behaviour in addition to the requirements listed, which may or may not have been intentional.

When the cardinality of the dimension to display was more than 1 higher than the top n parameter, and the Show Others? option was set to Group By, the sales value to display was based on average sales rather than sum (as indicated in the requirements), and all the values not in the top n, were grouped under an ‘Other (Avg)’ label. But if the cardinality of the dimension was only 1 more than the top n parameter (so there was essentially only 1 value within ‘other’), then this value would display as itself (ie not labelled ‘other’) and the sales values would be summed rather than averaged (eg if the lines were to be split based on Ship Mode, and the top n was set to top 3 and Show Others? set to Group By, all four ship modes would display with the sum of sales rather than average).

This observation meant some of the calculations were slightly more complex than what I thought they would need to be initially, as I had to build in logic based on the number of values within a dimension.

So with that understood, let’s build the calcs…

Defining the calculations

Firstly we need some parameters

pDimensionToDisplay

String parameter defaulted to Subcategory, with the list of possible dimensions to split the chart by

pTop

integer parameter defaulted to 5

pShowOthers

I used an integer parameter with values 0 and 1 which I ‘aliased’ to the relevant values, defaulted to ‘Group Others’

I then needed to determine which dimension to be used based on the selected from the pDimensionToDisplay parameter.

DimensionSelected

CASE [pDimensionToDisplay]
WHEN ‘Subcategory’ THEN [Sub-Category]
WHEN ‘State’ THEN [State/Province]
WHEN ‘Ship Mode’ THEN [Ship Mode]
WHEN ‘Segment’ THEN [Segment]
ELSE ‘All’
END

From this, I could then use a Set to determine which of the values would be in the top n. Create a set of off DimensionSelected (right click -> Create -> Set)

Dimension Selected Set

select the Top tab, and create set based on the pTop parameter of Sales

To check this is working as expected, on a sheet, add DimensionSelected and Dimension Selected Set to Rows, add Sales to Text and sort by Sales descending. Show the pTop and pDimensionToDisplay parameters. Change the parameters and observe the results behave as expected.

Now we need to determine how many values are not in the set, ie, how many of the DimensionSelected values display as ‘Out’ in the above image.

Count Non-Set Items

{FIXED:COUNTD(IF NOT([Dimension Selected Set]) THEN [DimensionSelected] END)}

If the entry is not in the set, then return the entry, and the count the number of distinct entries we have. Using the FIXED level of detail calculation, wraps the value across every row.

Now this is understood, we need to work out whether we want to group the ‘non-set’ values under ‘other’

Dimension To Display

IF [pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1 THEN ‘Other (Avg)’
ELSE [DimensionSelected]
END

If we’re opting to ‘group’ the values, and the entry isn’t in the set, and we’ve got more than 1 entry that isn’t in the set, then we can label as ‘Other (Avg)’, otherwise, we just want the dimension value.

We use similar logic to determine whether to display the SUM or AVG Sales.

Sales to Display

IF [pShowOthers]=0 AND SUM([Count Non-Set Items]) > 1 THEN AVG([Sales])
ELSE SUM([Sales]) END

Format this to $ with 0 dp.

We can then remove the DimensionSelected from our view, and test the behaviour, switching the pShowOthers parameter

Building the core viz

On a new sheet, add Order Date set to continuous quarters (green pill) to Columns and Sales To Display to Rows and Dimension To Display to Label. Show all the parameters.

Create a new field

Colour

[pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1

add add to Colour shelf, and set colours accordingly.

Add Sub-Category, Ship Mode, State and Segment to the Filter shelf. Show them all, and then test the behaviour is as expected.

Extending the date axis

The date axis in the solution goes beyond 2022 Q4, and means the labels have a bit more ‘breathing space’. To extend the axis, click on Order Date pill in the Columns, and select Extend Date Range -> 6 months.

Then double click into the space next to the Sales To Display pill on the Rows and type MIN(0). This will create a second axis, and the date axis should now display to 2023 Q2

On the MIN(0) marks card, remove the Colour and Dimension To Display pills. Reduce the Size to as small as possible, and set the Colour opacity to 0%. Set the chart to dual axis, and synchronise the axis.

Finally tidy up the chart – hide the right hand axis, remove the title from the date axis, change the title on the left axis. Remove all row and column divider lines, but ensure the axis rulers are displayed. Title the viz referencing the pDimensionToDisplay parameter.

Building the dashboard

When putting the dashboard together, you need to ensure that the filters, parameters and main viz are all contained within a vertical container to ensure the viz ‘fills up’ the space when the controls section is collapsed.

The controls section itself is also a vertical container, which consists of 2 horizontal containers, one of which contains all the filters, and one which contains the parameters.

The layout tab shows how I managed this (I also like to rename the objects to keep better control).

The green Base vertical container is the first container. This essentially contains 3 ‘rows’ – the title, the User Controls vertical container and the viz itself.

The User Controls vertical container, then contains 2 rows itself – the Filters horizontal container (which has a pale grey background colour) and the Fine Tune horizontal container (which has a slightly darker grey background colour).

The User Controls vertical container is then set to ‘hide/show’ by selecting the whole container (click on the object in the item hierarchy on the left), and then selecting Add Show/Hide Button from the context menu. Adjust the button settings as required (I just altered the text displayed on hover) and then position accordingly.

It can take a bit of trial and error to get this right, and to get your containers working as expected. My published viz is here.

Happy vizzin’!

Donna