For #WOW2024 Week 6, Sean challenged us to create a viz depicting the top and bottom entries based on a variable set by the user. He instructed that no LoDs (level of detail calculations) should be used, and then also added a bonus to complete the challenge without using sets either (and so hinting that sets would solve the problem). I built both, and will provide the solution for both.

Setting up the common fields

Regardless of the solution, various calculated fields and parameters are required.

pTop

integer parameter defaulted to 12, which is used to drive how many entities to display in our top & bottom cohorts.

For the user to select a month, I first created a new field

Month of Order Date

DATE(DATETRUNC(‘month’, [Order Date]))

and I changed the default sort order of this field to descending (right click the field > default properties > sort > choose descending in the disalog box).

I then created a parameter

pSelectedMonth

date field that selects from a list that is populated when the workbook opens from the Month of Order Date field. Default to July 2021 and format the display to a custom format of mmmm yyyy – having defined the sort order to be descending, the most recent month will be at the top (this wasn’t in the requirements, but is just a useful trick to know).

Now we have a handle on the month we want to report over, we can create

Selected Month Sales

ZN(IF [Month of Order Date] = [pSelectedMonth] THEN [Sales] END)

Return the sales only if the month is that selected, otherwise return 0 (this is what the ZN function will do)

Format this to $ with 0dp.

Previous Month Sales

ZN(IF [Month of Order Date] = DATEADD(‘month’, -1, [pSelectedMonth]) THEN [Sales] END)

Return the sales only if the month matches the previous month to that selected, otherwise return 0.

Format this to $ with 0dp.

and with these, we can then create

Difference in Sales

SUM([Selected Month Sales]) – SUM([Previous Month Sales])

Format this to $ with 0dp too.

And we can also create

Colour – Diff > 0

[Difference in Sales] > 0

Pop all these into a table sorted by Difference in Sales ascending to see what it all looks like.

Solution 1 – Using sets

Right click on the State/Province field and create > set

Top States

using the Top condition to get the Top n States where n is the value from the pTop parameter, based on the Difference in Sales field.

and then create another set

Bottom States

using the Top condition to get the Bottom n States where n is the value from the pTop parameter, based on the Difference in Sales field.

Then right click on one of these states and create combined set

Top & Bottom

shows all members in both the Top States and the Bottom States sets

Add Top & Bottom to the Filter shelf. By default only the records ‘in’ either of the sets will display. Modify the pTop parameter to see the list change.

To build the viz

• add State/Province to Rows
• Add Difference in Sales to Columns and sort ascending
• Add Top & Bottom to Filter
• Add Colour – Dff > 0 to Colour and adjust accordingly.
• Add Selected Month Sales to Tooltip and adjust
• Show the pTop and pSelectedMonth parameters

To display the name of the month selected as a column header, create

Selected Month

[pSelectedMonth]

and add to Columns as a discrete (blue) pill at the month-year level. Hide field labels for rows and columns, remove all row/column dividers, zero lines, axis rules & tick marks.

Solution 2 – not using sets

This solution involves using the Rank table calculation

Create a new field

Asc

RANK_UNIQUE([Difference in Sales],’asc’)

and another called

Desc

RANK_UNIQUE([Difference in Sales],’desc’)

Revert back to the data table we built and remove the Top & Bottom set filter. Now add Asc and Desc to the output.

There are now two fields indexing each row; one which starts at 1 and increments, and the other that decrements so the last row is 1. We can now use these to filter just to the records we want

Records to Include

[Asc]<= [pTop] OR [Desc]<=[pTop]

Add this to the Filter shelf and set to True, and we can now see our top 12 and bottom 12 listed

Build the viz exactly as described above, but this time, add the Records to Include field onto the Filter shelf instead.

Pop these onto a dashboard and job done!

My published viz is here : Set Solution | Rank Solution

Happy vizzin’!

Donna