For this week’s challenge, Sean Miller decided to continue with the theme of dashboard extensions, introducing the brush filter extension. The whole of the top section is the extension, so there’s actually only a single chart that needs to be built for this challenge. The main areas of focus for this blog are
- The basic chart
- Colouring the chart
- Identifying the date range
- Adding the extension
The basic chart
The chart needs to plot the number of rat sightings vs the Created Date by Borough. The number of sightings is based on the automatically generated field that is related to the name of your data source. To simplify, I created a new field based on this:
# of Sightings
COUNT([2021_02_24_WW08_Rat_Sightings.csv])
and then created the chart as follows
- Borough on Columns
- Month(Created Date) on Columns as green continuous date pill, formatted to mmm yy
- # of Sightings on Rows
- Borough on Filter, excluding the value Unspecified
The Boroughs were then manually sorted into the required order.
Colouring the chart
Each Borough needs to be coloured based on the difference between the start & end points. So we need to identify what these are for each Borough. I used table calculations for this
Start Count
WINDOW_MAX(IF FIRST()=0 THEN [# of Sightings] END)
If we’re at the first point in the partition, then record the number of sightings. All the other values in that partition will be NULL. The WINDOW_MAX statement then takes that value and ‘spreads’ it across all the data points/rows in that partition, as shown below. In this instance the partition is the Borough.
When Start Count is added to the view, the table calculation is set to compute by the Created Date.
We create a similar field for the end point, this time using the LAST() table calculation
End Count
WINDOW_MAX(IF LAST()=0 THEN [# of Sightings] END)
With these two calculated fields, we can now create
Change
IF [End Count]-[Start Count]>0 THEN ‘INCREASE’
ELSEIF [End Count]-[Start Count]< 0 THEN ‘DECREASE’
ELSE ‘NO CHANGE’
END
To be honest, I outputted the words, as I thought I’d be using these in a legend somewhere, but I didn’t. Outputting as values 1, 0 , -1 or similar will have been more efficient.
This field can now be added to the Colour shelf with the table calculation setting applied to compute by Created Date
Identifying the Date Range
As part of the chart title, we need to identify the date range, ie the earliest month and the latest month within the whole window (whole chart). Again we can use table calculations for this
Start Month
WINDOW_MAX(IF FIRST()=0 THEN MIN([Created Date]) END)
If we’re at the first point in the partition (this time the partition is the whole table), then get the Created Date (this is wrapped within a MIN, as the field needs to be an aggregate; using MAX would have worked just as well). The WINDOW_MAX statement once again ‘spreads’ this value across all the rows. This field is formatted to mmmm yyyy
Similarly we have
End Month
WINDOW_MAX(IF LAST()=0 THEN MIN([Created Date]) END)
also formatted to mmmm yyyy.
Both these fields can then be added to the Detail shelf, but this time the table calculation is set as table across. Once on the Detail shelf, the fields can be referenced in the chart title.
Adding the Brush Filter Extension
This challenge makes use of the Brush Filter extension, which you need to download from this link and save the .trex file somewhere appropriate on your machine.
Create a dashboard and add the chart you’ve already built, then add the Extension object to the dashboard. When prompted select My Extensions and browse to the Brush Filter .trex file you’ve saved.
You’ll then be prompted to configure the extension as below, selecting the name of the sheet containing your chart, the Created Date field that will be filtered, and the relevant measure to display. You can then adjust the colours of the ‘filter’ control, and scroll down to save.
As well as a new sightings by month profile chart being displayed, this will have the effect of adding the Created Date to the Filter shelf on your worksheet. The ‘grey’ box over the chart, can then be adjusted by dragging in the ends, or moving the whole box, to apply the required filter.
And apart from formatting adjustments to the chart, that’s all there is to it for today. My published viz is here.
Happy Vizzin’! Stay Safe!
Donna
