Lorna’s challenge this week focused on how to present time data in a different way. Often visualisations involving time, use line charts to show haw a variable has changed over time. And while these are perfectly valid and serve a very necessary purpose, there are times when other insights can be gained by thinking more creatively. This whitepaper by Andy Cotgreave from Tableau, discusses other ideas too, if you want to explore further.

Anyway, back to the challenge. While not mentioned in the requirements, the Workout Wednesday ‘latest’ page, gave a clue to Lorna’s approach…

..sheet swapping. So this was already in my mind as I started to understand the requirements further. Some did complete the challenge within a single sheet, but my solution guide will involve sheet swapping.

Joining the data

Unlike most challenges, this one involved combining Superstore data with some order time data that Lorna had published. So the first thing necessary was to join the two data sets together, which was achieved with a simple inner join on Order ID

Building the Small Multiple layout

The overall display was a 3 x 4 table of months vs quarters. Each row was identified as a date quarter, which was simply QUARTER([Order Date]). For the Columns I needed to group the months of the years into the values of 1,2 or 3 which required an additional calculation:

Month Column

IF DATEPART(‘month’,[Order Date])%3 = 0 THEN 3
ELSE DATEPART(‘month’,[Order Date])%3
END

% (modulo) gives the remainder of a number when divided by x, so January which is month 1, divided by 3 results in 1; February (2) divided by 3 is 2, which March (3) divided by 3 is 0. But I want March in column 3, hence the logic above.

Plotting Month Column vs QUARTER([Order Date]) and placing the month name on Text you can see the basic layout we’re aiming for:

Determining Hour of Order

The y-axis on the chart is the hour of the order. The Time of Order is sourced from the additional spreadsheet data source we joined to above, and is stored in a string field in the format hh:mm:ss

I chose to get the hour portion of this field by using a bit of string manipulation:

Hour of Order

INT(TRIM( SPLIT( [Time of Order], “:”, 1 ) ))

This is looking at the Time of Order field, finding the 1st occurrence of a colon (:), and splitting off everything up to it. Right-clicking on the Time of Order field and selecting Transform->Custom Split, allows you to define this without writing the calculation directly

However, this will create a string field storing ’05’, or ’16’ etc in a field automatically named [Time of Order – Split 1].

TRIM( SPLIT( [Time of Order], “:”, 1 ) )

However, we need a integer field, so editing and wrapping the above automatically generated calculation with INT(), will change the data type, and ’05’ will become 5. The field is also renamed.

Orders by Day chart

Having known I was going to tackle this using Sheet Swapping, the first chart I decided to tackle was the version by Day.

For each month (each small multiple), a chart of Hour of Order by Day of Month is being displayed, and it’s a dual axis chart, where one axis is being used to present the orange circles when each order was placed, and the other the grey bar showing the range of hours in a day the orders were placed over.

Based on the small multiple display we showed earlier, we need to add Hour of Order to rows and DAY([Order Date]) set as a discrete blue pill to columns.

We now need to

• reverse the Hour of Order axis
• change the axis label to Hours
• fix the axis to display every 5 hours
• change the mark type to circle

Filter to latest year

Rather than hardcoding to the latest year of 2019, the latest year can be determined by using the Top 1 functionality of the Filter dialog. This means if the data source did change to include 2020, the chart would automatically show the data related to 2020 instead.

Colour & Size by number of Orders

A unique order is identified by Order ID, and it is possible that an order can contain more than 1 row of data, so Number of Records can’t be used. COUNTD([Order ID]) gives the require result. You can either create a dedicated calculated field to store this calculation, or you add Order ID to the Colour shelf, then change to use the Count (Distinct) aggregation

Change the colour range to use the Orange scheme. Then add COUNTD([Order ID]) to the Size shelf as well, either by reproducing the same steps above, or simply duplicating the field already on the Colour shelf by holding down Ctrl as well as clicking on CNTD([Order ID)] on the Colour shelf, and dragging onto the Size shelf.

Adding Month([Order Date]) to the Detail shelf, and the Tooltip can then be created:

<DAY(Order Date)>, <MONTH(Order Date)>

At <Hour of Order> there were <CNTD(Order ID)> orders

Range of Order Hours

I chose to use a different method from Lorna’s solution to present the range of hours in day, utilising a Gantt view instead which is a technique I’ve used many times in the past.

For this I need to identify 3 things : the maximum hour in each day an order was placed, the minimum hour in each day an order was placed, and the difference between the two.

Max Hour Per Day of Month

{FIXED [Order Date]: MAX([Hour of Order])}

Min Hour Per Day of Month

{FIXED [Order Date]: MIN([Hour of Order])}

Time Range Days

[Max Hour Per Day of Month]-[Min Hour Per Day of Month]

On my secondary axis, I then plotted Max Hour Per Day of Month as a Gantt mark type which was then Sized by -1*MIN([Time Range Days])

Setting it to be Dual Axis, synchronising the axis, ensuring the Gantt marks are ‘moved to the back‘, then hiding the 2nd axis, and you have the final chart layout.

Add Trend Line

Click the Analytics tab on the right hand side, and drag Trend Line onto the chart, dropping onto the Linear : Hour of Order lozenge (hard to screen shot this). Then format the trend line to be a dotted, grey line.

Then it’s just a case of tidying up everything; You may need to adjust the size of the marks, remove the text from the tooltip for the Gantt mark, format the font sizes, remove grid lines etc, and get rid of the unwanted headers.

Name this sheet By Day or similar.

Orders by Weekday chart

Start by duplicating the by Day chart you’ve just created, and change the DAY([Order Date]) field in the columns to WEEKDAY([Order Date]).

You’ll notice the Gantt chart is now not working properly, as it’s not spanning the range in all cases (you may need to adjust the size of the marks to see this properly).

This is because the Gantt is based at the day level and now we’re at the weekday level. We need to create some equivalent fields to work at the weekday level :

Max Hour per Weekday per month

{FIXED DATEPART(‘weekday’,[Order Date]), MONTH([Order Date]), YEAR([Order Date]): MAX([Hour of Order])}

Min Hour per Weekday per month

{FIXED DATEPART(‘weekday’,[Order Date]), MONTH([Order Date]), YEAR([Order Date]): MIN([Hour of Order])}

These fields are finding the min/max hour for each weekday, month, year combination.

Time Range Weekdays

[Max Hour PerWeekday of Month]-[Min Hour PerWeekday of Month]

It’s then a case of plotting the Gantt using the Max Hour per Weekday per month field and Size-ing by MIN([Time Range Weekdays]) * -1, following the information detailed above if need be.

Format the Weekday axis

If everything’s been changed correctly, then the Weekday version of the chart should be pretty much there, the only thing that needs tweaking is the axis which is displaying the full name of the days of the week. Right click the axis, and select format and under the Dates section, you can change to ‘Abbreviation’.

Right-click again and choose Rotate Label to get the dates displaying the right way round

and you’ve got your Weekday view – name the sheet by Weekday or similar

Sheet Swapping

On a dashboard, add a container (either vertical or horizontal, it doesn’t matter), and add both the charts to it, hiding the titles of both. Ensure both are set to Fit Entire View, but don’t worry about sizing them any further.

Create a parameter, Time Selector, which is a string listing 2 options: Days & Weeks

Then create another calculated field

Time Selected is Days

[Time Selector] = ‘Days’

which will return true if the parameter is ‘Days’ and false otherwise (ie Weeks is selected).

On the By Day sheet, choose to show the parameter control, and select the option to be Days.

Then add the Time Selected is Days field to the Filter shelf. The only option available for selection will be True. Tick this.

Then switch to the By Weekday sheet and again show the Time Selector parameter. Select the Weeks option.

Then once again, add the Time Selected is Days field to the Filter shelf. The only option available this time, will be False. Tick this.

If you switch back to the By Day sheet, you’ll find that it is now blank. Change the parameter to Days, and the chart will show, but the By Weekday sheet will now be blank.

If you go back to the dashboard, only one of the charts should now be showing. Show the parameter control, and when you change it, you should find the charts being replaced.

It should now just be a case of finalising the look of the dashboard – adding the title, formatting the parameter input and re-positioning it.

My published version of the viz is here.

Happy vizzin!

Donna