Can you build a dynamic date drilling chart?

Week 19 of #WOW2020 saw Ann provide this challenge, which she declared would be easier than Luke’s from the previous week.

The aim was to start with a chart by week, but on selection drill in to show a by day view, with the slight twist that all days between the first and last selected dates should display, even if the specific week in between wasn’t explicitly selected.

I knew this was going to involve set actions (although there was a clue on the ‘latest challenges‘ page – I tend to pick up the challenges through Twitter, so often bypass this page, but it’s worth a look for extra clues :-))

So on the face of it, the challenge seemed as if it should be ok, but it’s usually only when I start building that things can start to unravel.

One area that Ann hadn’t been explicit about in the requirements, was the behaviour in certain scenarios. I had to revise some of my initial attempts/calculations in order to match Ann’s workbook. This week I’m not going to go into all the wrong turns, but this is the behaviour I observed in Ann’s workbook, and so ultimately tried to replicate.

  1. When at the week level, if multiple points are selected, the days then displayed should start from the first day of the first week selected (which will be a Sunday as we’re working with how the US standardises a week start), and finish on the last day of the last week selected (which will be a Saturday).
  2. When at the week level, if a single point is selected then the 7 days in that week only should be displayed.
  3. When at the day level, if multiple points are selected, the days then displayed start from the first day of the week the first day is in and end on the last day of the week the last selected day is in (so you may get more days than actually selected). eg if the 1st day selected is a Tuesday and the last day selected is a Friday, the ‘drill in’, will start on the previous Sunday, and end on the following Saturday. This isn’t necessarily what you may expect to happen.
  4. Based on the above, when at the day level, a single day is selected, the ‘drill in’ will show the 7 days in the same week. Once you’re down to displaying the 7 days in the same week, clicking on any single day or selecting a couple of days, won’t appear to do anything as the same results are displayed.

Being aware of the above, will explain why some the calculations I end up with look the way they do.

Ok, let’s get on with the build…..

Ann stated that she’d hardcoded to the ‘last 52 weeks’, but essentially it looked like she was displaying data for all of 2019. This wasn’t really something the challenge was testing, so I simply started by adding a data source filter for the year 2019 (right click on data source -> Edit data source filters). I also had to set the date properties of my data source to set a week to start on a Sunday as I’m UK based, so my week’s are defaulted to start on Mondays (again right click on data source -> Date Properties). As a result of my year filter, I didn’t seem to have the same starting/ending dates as Ann, but as already stated, this wasn’t the main aim of the challenge, so I didn’t stress about it.

I decided quite early on that I was going to also use a Parameter Action to decide the level I was at (ie had I drilled in or not). I wasn’t entirely sure when I started how many ‘levels’ I might need, so chose to use an integer parameter for this. I ended up only needing 2 levels, so a boolean could have worked equally as well, or, as I type, I think I could have used this parameter to store the ‘date level’ (day or week) I want to display my dates at, which would have made some of my calculations easier to read. I’m not going to do this though.

Drill Down

Integer parameter set to 0 by default.

Now I want to define a date field that I’m going to use on the axis, that will vary depending on the ‘level’ we’re at.

Date to Plot

IF [Drill Down]>0 THEN DATETRUNC(‘day’, [Order Date]) ELSE
DATETRUNC(‘week’, [Order Date])
END

Note – I used >0 as I wasn’t sure if I’d have levels 0, 1 & 2… as it turns out I just used 0 & 1 in the end.

Add Date to Plot to Columns as a Continuous, Exact Date and Sales to Rows and we’ve got our starting point

Change the Drill Down parameter to 1 and the chart will change to display at the day level

Drilling Down on selection

On selection, we want to add the selected dates into a set, so first up, we need to define that set.

Right click on Date to Plot and Create -> Set

Selected Dates

Select some random dates so we can test with. These will get set properly later based on the Set Action we define on the dashboard.

Based on the dates in the set, we need to determine a min and a max date we can then use to restrict the dates being plotted on the chart.

Min Date

{FIXED:MIN(IF [Selected Dates] OR [Drill Down]=0 THEN DATETRUNC(‘week’,[Date to Plot]) END) }

This either gets the 1st day of the week based on the earliest date in the set, or the 1st day of the week of the whole data set (when we’re at the ‘starting’ level with Drill Down =0).

We also need

Max Date

IF [Drill Down]=0 THEN
{FIXED:MAX(DATEADD(‘day’,-1,[Date to Plot]))}
ELSE
DATEADD(‘week’,1,{FIXED:MAX(IF [Selected Dates] THEN DATETRUNC(‘week’,[Date to Plot]) END)})-1
END

If we’re at the starting level (Drill Down = 0) then we want the last day within the latest week in the data set (this field is used in the title display, so necessary to get this to display right), otherwise we need to get the last day of the week associated to the maximum date in the set.

Now we need to be able to restrict the dates displayed in the chart based on these

Dates to Include

[Order Date]>= [Min Date] AND [Order Date]<= [Max Date]

Add this to the Filter shelf and set to True.

Your display shouldn’t change, as we’re still at ‘level 0’, even though our set has random dates selected. If you now manually change Drill Down to 1, you should see a change

Setting up the dashboard actions

Ok, now we’ve got the basic idea, let’s get it all working properly with dashboard actions before we sort out all the other bits n bobs.

First up, let’s manually reset everything by setting the Drill Down parameter to 0 and emptying all the values selected in the set.

Add the sheet onto the dashboard, and create the set action, which is set to target the Selected Dates set and to Keep set values if you click on a blank area of the chart after selection.

However, this on it’s own won’t change the display. We need to set the Drill Down parameter to 1 too.

For this we need another field

Set Drill Down Level

1

Add this to the Detail shelf of the chart.

Then back on the dashboard, add a Parameter Action that targets the Drill Down parameter using the value stored in the Set Drill Down Level field

Now if you select the dates in the dashboard, you should get the desired behaviour, and if you select again, you should filter the days selected further (as per the behaviour described at the top of the page).

Resetting the display

The Reset button is actually another sheet.

I created a ‘fake’ bar chart by adding MIN(1) to Rows, and double clicking in the space below the Detail and Tooltip shelves on the Marks card, and typing the text ‘CLEAR SELECTION’. This creates a ‘pill’ without having it defined explicitly as a calculated field, and I added this to the Text shelf, and centred/formatted appropriately. I then set the axis to be fixed from 0 to 1 and hid it.

I set the colour to #a26dc2 and set the text to ‘match mark colour.

We only want this sheet to display, if we’ve ‘drilled down’, so I need

Show Reset

[Drill Down]=1

which is added to the Filter shelf and set to True.

I also need a parameter action off this sheet, to reset the Drill Down to 0 on selection. For this I need another field

Reset

0

which is added to the Detail shelf.

Depending on what you’ve already been playing around with, there’s a chance this sheet may already be empty. Get the dashboard into a state where you’ve drilled down to the day level, then add this sheet, and add another Parameter Action.

Reset is set to run off the ‘button’ sheet only, to target the Drill Down parameter by using the value in the Reset field.

You should now be able to test all this out and get the desired behaviour.

Adding Animations

To get the chart to transition between selections, we need to use the Animations functionality.

On the Format menu, select Animations and adjust the settings as you choose.

Play around and you should have the main features of this challenge now working

Dynamic Title

The title needs to change based on whether you’re at the weekly or daily level. It also needs to show total and average sales. So for all this I need

LABEL: Level

IF [Drill Down]>0 THEN ‘Day’ ELSE ‘Week’ END

LABEL: Subtitle Level

IF [Drill Down]>0 THEN ‘Daily’ ELSE ‘Weekly’ END

LABEL: Instruction

IF [Drill Down] = 0 THEN ‘SELECT WEEKS TO DRILL DOWN TO DAILY VIEW’
ELSE ‘CLEAR SELECTION USING BUTTON’
END

Total Sales

WINDOW_SUM(SUM([Sales]))

Avg Sales

WINDOW_AVG(SUM([Sales]))

All these fields are added to the Detail shelf of the main chart along with Min Date and Max Date, and then the title is edited and formatted accordingly to reference these.

Tooltips

In a similar manner, the tooltips also need adjusting, they just need to refer to the LABEL: Level field

Colour of the Line

Although not explicitly mentioned, the line colour seemed to change from a lighter shade at the weekly level to more intense at the daily level. To achieve this I created

COLOUR

[Drill Down]

and added to the Colour shelf. This gives a colour legend displaying either 1 or 0 depending at what state you are in the interaction. Set colour values accordingly.

Average Line

Add this to the chart simply by selecting Average Line from the Analytics pane and dragging then dropping onto the Table shelf that appears. Format appropriately.

And apart from other basic formatting to remove gridlines/ axis titles etc, that should be it.

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