Tag: Formatting Time

Average Patient Wait Times

Donna ColesLeave a comment

Erica set this challenge this week to provide the ability to compare the average wait time for patients in a specific hour on a specific day, against the ‘overall average’ for the same day of week, month and hour.

As with most challenges, I’m going to first work out what calculations I need in a tabular form, and then I’ll build the charts.

  • Building the Calculations
  • Building the Wait Time Chart
  • Building the Patient Count Chart

Building the Calculations

The Date field contains the datetime the patient entered the hospital. The first step is to create a field that stores the day only

Date Day

DATE(DATETRUNC(‘day’,[Date]))

From this I could then create a parameter

Choose an Event Date

date parameter, defaulted to 6th April 2020, and custom formatted to display in dddd, d mmmm yyyy format. Values displayed were a list added from the Date Day field.

I also created several other fields based off of the Date field:

Month of Date

DATENAME(‘month’,[Date])

if the Date is Friday, 10 April 2020 16:53, then this field returns April.

Day of Date

DATENAME(‘weekday’,[Date])

if the Date is Friday, 10 April 2020 16:53, then this field returns Friday.

Hour of Date

DATEPART(‘hour’, [Date])

if the Date is Friday, 10 April 2020 16:53, then this field returns 16. I added this to the Dimensions (top half) of the data pane.

The viz being displayed only cares about data related to the day of the week and the month of the date the user selects, so we can also create

Records to Keep

DATENAME(‘weekday’, [Choose an Event Date]) = [Day Of Date]
AND
DATENAME(‘month’, [Choose an Event Date]) = [Month of Date]

On a new sheet, add this to the Filter shelf and set to True. When Choose an Event Date is Monday, 06 April 2020, then Records To Keep will ensure only rows in the data set relating to Mondays in April will display. Let’s build this.

Add Hour of Date, Day of Date, Month of Date to Rows, and then also add Date as a discrete exact date (blue pill).

You can see that all the records are for Monday and April. And by grouping by Hour of Date, you can see how many ‘admissions’ were made during each hour time frame. Remove Day of Date and Month of Date – these are superfluous and I just added so we could verify the data is as expected.

The measures we need are patient count and patient wait time. I created

Count Patients

COUNTD([Patient Id])

(although the count of the dataset will yield the same results).

Add Count Patients and Patient Waittime into the table.

This is the information that’s going to help us get the overall average for each hour (the line chart data). But we also need to get a handle on the data associated to the date the user wants to compare against (the bar chart).

Count Patients Selected Date

COUNTD(IF [Choose an Event Date] = [Date Day] THEN [Patient Id] END)

Wait Time Selected Date

IF [Choose an Event Date] = [Date Day] THEN [Patient Waittime] END

Add these onto the table – you’ll need to scroll down a bit to see values in these columns

So what we’re aiming for, using the hour from 23:00-00:00 as an example: There are 3 admissions on Mondays in April during this hour, 2 of which happen to be on the date we’re interested in. So the overall average is the sum of the Patient Waittime of these 3 records, divided by 3 (650.5 / 3 = 216.83), and the average for the actual date (6th April) in that hour is the sum of the Wait Time Selected Date for the 2 records, divided by 2 (509.5 / 2 = 254.75).

If we remove Date from the table now, we can see the values we need, that I’ve referenced above.

So let’s now create the average fields we need

Avg Wait Time

SUM([Patient Waittime])/[Count Patients]

Avg Wait Time Selected Date

SUM([Wait Time Selected Date]) / [Count Patients Selected Date]

Pop these into the table

This gives is the key measures we need to plot, but we need some additional fields to display on the tooltips.

Avg Wait Time hh:mm

[Avg Wait Time]/24/60

This converts the value we have in minutes as a proportion of the number of minutes in a day. Apply a custom number format of h”h” mm”mins”

Do the same for

Avg Wait Time Selected Date hh:mm

[Avg Wait Time Selected Date]/24/60

Apply a custom number format of h”h” mm”mins”

Add these to the table.

For the tooltip we need to show the hour start/end.

TOOLTIP: Hour of Date From

[Hour of Date]

custom format this to 00.00

TOOLTIP: Hour of Date To

IF [Hour of Date] =23 THEN 0
ELSE [Hour of Date]+1
END

custom format this to 00.00.

We can now start building the charts.

Building the Wait Time Chart

On a new sheet, add Records To Keep = True to the Filter shelf. Add Hour of Date as continuous dimension (green pill) to Columns and Avg Wait Time Selected Date to Rows. Change the mark type to Bar.

Then add Avg Wait Time to Rows and change the mark type of this measure to Line. Make the chart dual axis, synchronise the axis and adjust the colours of the Measure Names legend.

Add TOOLTIP: Hour of Date From and TOOLTIP: Hour of Date To and Avg Wait Time Selected Date hh:mm to the Tooltip of the bar marks card, and adjust the tooltip accordingly.

Add TOOLTIP: Hour of Date From, TOOLTIP: Hour of Date To, Avg Wait Time hh:mm, Day of Date and Month of Date to the Tooltip of the line marks card, and adjust the tooltip accordingly.

Remove the right hand axis. Remove the title of the bottom axis. Change the title on the left hand axis. Hide the ‘9 nulls’ indicator (right click). Remove column gridlines, all zero lines, all row and column dividers. Keep axis rulers. Format the numbers on the x-axis.

Change the data type of the Hour Of Date field to be a decimal, then edit the x-axis and fix to display from -0.9 to 23.9.

Amend the title of the chart to match the title on the dashboard.

Building the Patient Count Chart

On a new sheet, add Records To Keep = True to Filter, Hour of Date as continuous dimension to Columns and Count Patients Selected Date to Rows. Change Mark Type to Bar.

Right click on the y-axis and edit the axis. Remove the title, and check the Reversed checkbox to invert the axis. Edit the x-axis and fix from -0.9 to 23.9 as before.

Add TOOLTIP: Hour of Date From and TOOLTIP: Hour of Date To to the Tooltip and adjust accordingly.

Set the colour of the bars to match the same blue of the bars in the wait time chart, then adjust the opacity to 50%.

Remove all gridlines and zero lines. Retain axis ruler for both row and columns. Retain row divider against the pane only. Hide the x-axis.

We need to display a label for ‘Number of Patients’ on this chart. We will position it based on the highest value being displayed (rather than hardcoding a constant value). For this we create a new calculated field

Ref Line

WINDOW_MAX([Count Patients Selected Date]) +1

This returns the maximum value of the Count Patients Selected Date field, adds 1 and then ‘spreads’ that value across all the ‘rows’ of data.

Add this field to the Detail shelf. Then right click on the y-axis and Add Reference Line.

Add the reference line based on the average of the Ref Line field, and label it Number of Patients. Don’t show the tooltip, or display any line.

Once added, then format the reference line, and adjust the size and position of the text.

The sheets can now be added to a dashboard, placing them above each other. They should both be set to Fit Entire View. The width of the y-axis on the Patient Count chart will need to be manually adjusted so it is in line with the Wait time chart, and ensures the Hour columns are aligned..

My published viz is here.

Happy vizzin’!

Donna

Tableau Website Analytics

Donna ColesLeave a comment

This week’s #WOW2021 challenge by Ann Jackson is focussed on dashboard design/layout, reference lines and formatting time, so that will be the focus of this blog too. I’m hoping it might be a fairly short post this week 🙂

  • Filtering the data
  • Creating the key measures, formatting time, adding average lines
  • Dashboard layout

Filtering the data

The data Ann provided is based on the Google Analytics data the team have harnessed related to the activity on the #WOW website. It’s a static data set, which contains data from 29 Dec 2019 up to 12 April 2021, but Ann states her solution only includes data up to 10 April 2021. I therefore added the Date field to the Filter shelf on the sheet and set it to end on 10 April 2021

Knowing I’d be building multiple sheets for this challenge, I set this filter to be a global filter by setting it to apply to worksheets – > all using this data source

Creating the key measures, formatting time, adding average lines

For this challenge, we’ll be creating a sheet for each BAN, and a sheet for each trend line depicting a measure by week, so 8 sheets in total.

For the Sessions measure, both the BAN and the trend chart are straightforward.

The BAN just shows SUM([Sessions]) on the Text shelf, appropriately formatted. The other BAN sheets are pretty much the same, but just show the appropriate measure in the appropriate colour.

The trend line displays Week([Date]) by SUM([Sessions]), with the average line added by dragging Average Line from the Analytics pane onto the chart and then formatting.

If the numbers don’t quite match up, it may be because your week is set to start on a different day. By default as I’m UK based, dates are set to start their week on a Monday. For this challenge to match Ann’s solution, your week needs to start on a Sunday. You can set this by right clicking on the data source itself and changing via the Date Properties option

To determine the average session duration, we need to build a calculated field, that then needs to be formatted to show minutes and seconds.

Average Session Duration

(SUM([Session Duration]) / SUM([Sessions]))/86400

Session Duration / Sessions will return a value in seconds. To be able to format this in the way required, we need to get the number of seconds as a proportion of a day. There are 86400 seconds in a day (60 * 60 * 24), so we divide by this.

We can then use a custom format on this field and use the nn:ss notation. NOTE not mm:ss. If you needed to format this as hours:minutes:seconds, the format would be hh:mm:ss, but mm:ss does not provide you with the right values. This video demonstrates all this, if you’re interested.

When it comes to building the trend line for this measure, the average line, can’t be added as simply in the way it could for the Sessions trend chart (at least I couldn’t get it to work that way…).

We need to build a calculated field that will show the same overall average value alongside the weekly averages. This value needs to match what’s displayed in the BAN chart.

Overall Session Duration mm:ss

{FIXED : [Avg Session Duration]}

This is returning the Avg Session Duration based on all the rows in the data. As the Date field has been added as a global filter, it is acting like a data source filter, so the dates we don’t want have been excluded from the data set that the FIXED LoD is being applied against. If the Date filter was a simple ‘quick filter’, this calculation wouldn’t work, as the data for the 11th & 12th April would also be included in the calculation.

Format this to nn:ss as well. Add this field to the Detail shelf of the trend chart, then right click on the Average Session Duration axis and Add Reference Line, and reference the Overall Session Duration mm:ss field.

For the bounce rate, we simply need

Bounce Rate

SUM([Bounces])/SUM([Sessions])

which is formatted to a percentage of 1 dp.

When building the trend line, I added the average line from the analytics pane, but that gave me a different value from my BAN. So I built

Overall Bounce Rate

{FIXED : [Bounce Rate]}

This was formatted to % 1dp, and added as a reference line as described above.

Finally the last measure, we need

Avg Time on Page

(SUM([Time on Page])/(SUM([Pageviews])- SUM([Exits])))/86400

formatted to nn:ss

and

Overall Time on Page mm:ss

{FIXED : [Avg Time on Page]}

and again formatted to nn:ss.

The charts for these are built exactly like the Average Session Duration.

Dashboard Layout

The easiest way to describe the layout I built is to show it 🙂 Note the Item Hierarchy on the left hand side of the image below.

I have a vertical container as the Main page.

The first row in this container is the title in a Text object.

The second row is a Blank object and is the yellow line. The background of the blank object is set to the relevant yellow, the outer padding is set to 0 all round, and then the height is set to 4. This gives the appearance of a thick coloured line.

The third row is another vertical container, and I’ve done this, so I can ultimately use the option to Distribute Contents Evenly on the container to ensure the horizontal container ‘rows’, which I’ll be adding into this container, are evenly spaced.

So ‘within’ the 3rd row, the 4th-7th rows are managed using a horizontal container, which in turn contains a blank object (the coloured vertical line), the BAN sheet and the trend sheet. Around each horizontal container I set the outer padding to 10 all round to give some spacing. The blank object in each ‘row’ is given the relevant background colour and set to a width of 15.

Finally, I finished off with an additional horizontal container at the bottom which is where I added my standard #WOW footer. Note this horizontal container is essentially the 4th row of the original Main vertical container though.

Hopefully I’ve provided enough for you to build the challenge yourself / resolve any issues you might have. If there’s anything I’ve missed, do please comment to let me know.

My published viz is here. Please note, that for some reason (and I don’t know why), Tableau Public does not seem to display my time axis properly (just shows 0). As the workbook renders on Public, the values on the axis show, but once fully loaded, they change. If you download the workbook, the values are fine. I published from Tableau Desktop 2021.1.0. I’m putting this down to an issue with Tableau Public.

Happy vizzin’! Stay Safe!

Donna