Can you use the Brush Filter Extension?

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s