It was Sean’s turn to set the challenge this week which included a lots of little details to provide a wealth of different ‘views’ on the same data within a single chart, specifically the change of measure and the change of date granularity.

Building a viz showing the timeline at different levels of granularity based on user selection, is something I’ve done several times in the past, both for other WOW challenges and also in real work situations. So I immediately headed down my ‘usual route’, creating a parameter to identify the level of the date to report at, and a calculated field to display the appropriate level based on the parameter. However, then I noticed that in Sean’s viz, if I changed the date level, the date axis display also changed, sometimes showing <Month Year> format, sometimes <Year Q> etc. Having the axis change like this isn’t something that would work with my ‘usual’ solution. I did ponder this for some time, and the only thought I had was multiple sheets – one for each date part. Sean hadn’t stated how many sheets were needed for the challenge, and it made me wonder if the omission was perhaps deliberate…

Building the Month chart

To start we need to define a parameter to identify the level of date to select

pDateGranularity

string parameter defaulted to ‘month’ with a list of options, which have an alternative display name

We also need to identify the measure we want to show. Before we can define this, we need

Profit Ratio

SUM([Profit])/SUM([Sales])

and

#Orders

COUNTD([Order ID])

We also need to be able to capture the selected measure in a parameter

pMeasure

string parameter, defaulted to PROFIT (note case). A list isn’t needed as the value will be set via an action on the dashboard

so we can then build

Measure to Display

CASE [pMeasure]
WHEN ‘SALES’ THEN SUM([Sales])
WHEN ‘PROFIT’ THEN SUM([Profit])
WHEN ‘PROFIT RATIO’ THEN [Profit Ratio]
WHEN ‘ORDERS’ THEN [#Orders]
END

On a new sheet, add Order Date to Filters and set as a relative date filter to the last 4 years. Add Order Date to Columns as a continuous (green) pill set at the month level, and add Measure to Display to Rows.

Add the Order Date filter to ‘context’ (right click > Add to context) and set it to apply to worksheets > all using this data source. The context is necessary as when it comes to the option to select a date to highlight, only the dates within the timeframe selected should be visible. Show the pMeasure and pDateGranularity parameters.

Edit the Measure to Display axis to change the title to reference the pMeasure parameter instead (right click and edit axis).

Test the values change as expected by changing the text in the pMeasure parameter to SALES or ORDERS or PROFIT RATIO.

To identify a month to highlight, I created

Order Date – month

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

and formatted this to a custom format of yyyy-mm

I then created a set off of this field (right click > create > set) and selected a single date 2021-11

Order Date – month Set

In order to make use of the set and its values, the set needs to be on the view. Add Order Date – month Set to the filter shelf. If it doesn’t happen by default, change the option on the pill to Show In/Out of set and then select both In and Out as the filter options (ie all possible values so nothing is actually being filtered).

Then click on the Order Date – month Set pill on the filter shelf and select show set. The list of values in the set should display.

Change the input type of the Set control to be a single value drop down, where the (All) option doesn’t show (via customise) and the option All values in Context is selected

We need to capture the value associated with the date selected

Month Measure to highlight

IF ATTR([Order Date – month Set]) THEN [Measure to Display] END

Add this to the Rows shelf, then make the chart dual axis and synchronise the axis. Adjust the colours of the Measure Names and then make the mark type of the Month Measure to Highlight marks card, a circle, and increase the size.

Hide the right hand axis, remove the title of the bottom axis and remove all row & column dividers. Right click on the nulls indicator and hide indicator.

Call this sheet month chart or similar.

Building the Quarter chart

Go through similar steps to build a chart to display the information at a quarterly level. The field on the Columns shelf should be Order Date set at the continuous (green) quarter level.

You will need a field

Order Date – quarter

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

and format this to a custom format of yyyy-“Q”q

Use this to create a set off of this field (right click > create > set) and Order Date – quarter Set

You’ll also need

Quarter Measure to highlight

IF ATTR([Order Date – quarter Set]) THEN [Measure to Display] END

Building the week chart

Repeat again.

The field on the Columns shelf should be Order Date set at the continuous (green) week level.

You will need a field

Order Date – week

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

and format this to a custom format of yyyy-“W”ww

Use this to create a set off of this field (right click > create > set) and Order Date – week Set

You’ll also need

Week Measure to highlight

IF ATTR([Order Date – week Set]) THEN [Measure to Display] END

Creating the BANs

To start with, we need to create some calculated fields to store the total values for each measure and the ‘highlighted’ vallues

Total Sales

{FIXED: SUM([Sales])}

format to $ with 0 dp

Total Profit

{FIXED: SUM([Profit])}

format to $ with 0dp

Total Profit Ratio

{FIXED: [Profit Ratio]}

format to % with 0 dp

Total Orders

{FIXED:COUNTD([Order ID])}

format to number with 0 dp

and we’ll also need values for the highlighted date

Highlighted Sales

IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN ([Sales])
END

format to $ with 0dp

Highlighted Profit

IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN ([Profit])
END

format to $ with 0 dp

Highlighed Profit Ratio

SUM([Highlighted Profit])/SUM([Highlighted Sales])

format to % with 0 dp

Highlighted Orders

COUNTD(IF ([pDateGranularity]=’quarter’ AND ([Order Date – quarter Set])) OR
([pDateGranularity] = ‘week’ AND ([Order Date – week Set])) OR
([pDateGranularity] = ‘month’ AND ([Order Date – month Set]))
THEN [Order ID]
END)

format to number with 0 dp.

On a new sheet, double click into columns and manually type MIN(0.0). Repeat this 3 more times, so there are 4 instances of MIN(0.0) on the Columns shelf.

On the All marks card, add Measure Names to the Label shelf.

Now right click on Measure Names in the left hand data pane and select Aliases. Alias each of the MIN(0.0) measures to SALES, PROFIT, PROFIT RATIO, ORDERS

The labels on the viz should change

Create a new field

Sales Selected

[pMeasure] = ‘SALES’

On the 1st MIN(0.0) marks card, change the mark type to shape and add Sales Selected to the shape shelf. Use a transparent shape (see here for details) for the False value, and use the provided sparkline image (which you need to add to your shape palette) for the True value (change the pMeasure parameter to SALES for the True option to show). Increase the Size of the shape to around the 3/4 mark.

Add Total Sales and Highlighted Sales to the Label shelf, and then adjust the font position, size and colour accordingly. This is what my label dialog looked like – Measure Names is centred, while the other two fields are right aligned, and the overall alignment is right too.

Adjust the MIN(0.0) axis to be fixed from -0.2 to 1 to give the mark and the text enough space to breathe.

Now repeat the exercise for the other 3 marks cards. You will need the fields

Profit Selected

[pMeasure] = ‘PROFIT’

Profit Ratio Selected

[pMeasure] = ‘PROFIT RATIO’

Orders Selected

[pMeasure] = ‘ORDERS’

and you’ll need to change the value in the pMeasure parameter to get the relevant shape to show or not for each measure

Finally, create fields

True

TRUE

False

FALSE

and add these both to the Detail shelf of the All marks card. We’ll use these to stop the BANs from being ‘highlighted’ on click on the dashboard.

Then remove all row/column dividers and gridlines & zero lines

Building the dashboard

Create the dashboard, and arrange all the objects so the controls/filters are listed in a row at the top, and the 3 charts are arranged side by side underneath.

If you’ve got dates selected for your quarter & week, then in the data pane, right click on the relevant set, edit set and uncheck any option. This should then give you (None) in the dropdowns.

We will use dynamic zone visibility to control which charts & highlight date controls to display. For this we need fields

Show Monthly

[pDateGranularity] = ‘month’

Show Quarterly

[pDateGranularity] = ‘quarter’

Show Weekly

[pDateGranularity] = ‘week’

On the dashboard, select the monthly chart, and then from the Layout tab, select Control visibility using value and choose the Show Monthly field. Set the control visibility for the highlight a month object to also use the Show Monthly field.

Set the control visibility for the Quarterly chart and highlight a quarter object to use the Show Quarterly field, and set the control visibility for the Weekly chart and highlight a week object to use the Show Weekly field.

Create a dashboard parameter action to set the pMeasure parameter on click of the BANs sheet, passing in the Measure Names to the parameter

Set measure

Create a dashboard filter action to stop the BANs from being highlighted ‘on click’

Deselect BANs

And that should be it. My published viz is here.

Happy vizzin’!

Donna