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
[…] Read https://donnacoles.home.blog/2023/06/01/filtering-and-highlighting/ […]
LikeLike