6 Replies Latest reply on Aug 22, 2018 4:31 PM by elias.daniels.0

# 24Hrs (30 mins interval) Time Axis. Time Utilisation

Hi guys,

Can you please share a light. I've two issues.

First, I'm trying to create a full 24 hrs time axis, even if there is no record.

For example I want to show in the axis from 0:00 to 23:59 in 30 mins intervals. The current axis (string) I created based on datetime from the existing records. If there is more than one record within the bracket, I will count it as 1.

Secondly, as you can see from the Grand Totals, I'm adding up all records by time bracket. Is there a way I can get ta summary %. For example, in the first column under 8:00 am, instead of 1, I will like to have 1/3 (33.33%). Maybe by counting Case Date? The total under 11:30 Am would be 3/3 = 100%.

This would vary depending the number of dates I choose.

Elias

• ###### 1. Re: 24Hrs (30 mins interval) Time Axis. Time Utilisation

Hi Elias,

You'll want to create a simple table that contains all the possible 30 minute increments. You can then JOIN this table onto your data set to create your desired result. The reason for this is because Tableau can only create visualizations for rows that have underlying data. I.e. since there is no underlying data for 1:30 AM, it cannot be visualized.

Some exceptions exist, such as through using BINS, but I can't think of a way to use the BIN technique here.

Regardless, it's easy to create the table and it never needs to be updated. I created one for you which you can extract from the attached workbook.

You can use the calculation you already created to JOIN the tables together (keeping all records from the minute increment table--in this case a RIGHT JOIN).

Here's the output:

Here's a screenshot of the table

The Grand Total shows 0.33 because they are totaled using the AVERAGE of a zn(CountD(Case Date)). The ZN shows NULLs as 0s and allows us to take an average of all three case dates.

Hope that helps,

Ray

3 of 3 people found this helpful
• ###### 2. Re: 24Hrs (30 mins interval) Time Axis. Time Utilisation

Hi Ray,

Can you please save it as a Packaged workbook.

Elias

• ###### 3. Re: 24Hrs (30 mins interval) Time Axis. Time Utilisation

I extracted the data and reuploaded to the original post.

Ray

• ###### 4. Re: 24Hrs (30 mins interval) Time Axis. Time Utilisation

Hi Ray,

I can open the file, but cannot access the table you have created. Still asking me for login details to review data sources.

Unfortunately I cannot create the right join:

I have created my own simple excel 24Hrs file, but is not working.

Can you please share you tables?

Thanks mate.

Cheers,

Elias.

• ###### 5. Re: 24Hrs (30 mins interval) Time Axis. Time Utilisation

Yes, I've attached the table.

Most likely you couldn't do the RIGHT JOIN because the minute buckets were a DATE data type, and the calculation was a STRING data type, and the data types must match to do the join.
I took an additional step in the table to ensure the minute buckets were STRING data types--if you add an apostrophe in front of the time, it'll stay as a string and not convert to a date in excel. ( '12:00 AM instead of 12:00 AM -- note the apostrophe at the beginning).

Ray

1 of 1 people found this helpful
• ###### 6. Re: 24Hrs (30 mins interval) Time Axis. Time Utilisation

Thanks Ray for your quick turn around!

Excellent solution.

Indeed, I had a data type mismatch. Totally overlooked it.