9 Replies Latest reply on Feb 8, 2019 8:59 AM by Don Wise

# Calculation requires chronological order

I have been tasked with creating a report that shows the average time taken between a surgery ending and the next one beginning by hospital. In order to do the calculation I must find the average time between the previous case end time and the next case start time for same physician, hospital, operating room, date. Below is an example set of data.

So for the first physician, the average time is 45 minutes since it took 1 hour to start case 2 (10:30AM-9:30AM,) then 30 minutes to start case 3(11:30AM-11:00AM.) Is it possible to do this type of calculation in Tableau, since the calculation requires that the records be in correct chronological order? I'm pretty sure this is my first time posting on this forum, so please let me know if I can better explain my issue.

PhysicianHospitalOperating RoomCase #
Start Time
End Time

Dr.A

Hospital ARoom 1111/25/2018 7:30AM 11/25/2018 9:30AM
Dr.AHospital ARoom 12
 11/25/2018 10:30AM
 11/25/2018 11:00AM
Dr.AHospital ARoom 13
 11/25/2018 11:30AM
 11/25/2018 12:15PM
Dr.BHospital BRoom 21
 11/25/2018 10:30AM
 11/25/2018 11:00AM
Dr.BHospital BRoom 22
 11/25/2018 12:15PM
11/25/2018 1:15PM
Dr.BHospital BRoom 23
 11/25/2018 1:45PM
11/25/2018 4:00PM

Thanks for the help!

• ###### 1. Re: Calculation requires chronological order

Hi Diane

at TC18 in October there was session on data prep that had an example that is exactly your problem

the first link is a you tube of the session

this is the accompanying pdf

file:///C:/Users/Market/Documents/Tableau%20conference%20presentations/Data/18BI-066_Data%20Shaping_0.pdf

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculation requires chronological order

Hi Diane,

I gave it a go with your example data and thought I got somewhere with it initially with Physician A, however I haven't see this type of resulting behavior before for Physician B with a Table Calc that appears to be partitioning correctly, so perhaps someone else can weigh-in.

2018.3 workbook attached.  Thx, Don

• ###### 3. Re: Calculation requires chronological order

First you need to create a self join to get the desired output. For case no you have to join on next case number i.e case number +1.

Then you need to find the time difference in minutes between end time of 1st case number and start time of next case no for each hospital, physician, and operating room.

Then average time can be easily calculated with Lod.

Hope this helps.

2 of 2 people found this helpful
• ###### 4. Re: Calculation requires chronological order

Don Wise You forgot to change the partition of nested calculation.

• ###### 5. Re: Calculation requires chronological order

Ahhh thanks!

• ###### 6. Re: Calculation requires chronological order

Don, thanks for putting this together. I'm finally able to get back around to this project. I tried opening your workbook, but unfortunately my version of Tableau is older and I'm not able to update at this time. Thanks again for the help! I'm working to recreate.

• ###### 7. Re: Calculation requires chronological order

Hi Diane,

What version are you on and I'll try to upload an exported version of that.

I believe we can go down to 10.2 as lowest versioning.  Thx, Don

• ###### 8. Re: Calculation requires chronological order

Thanks, Don! I am on 10.5.1. I appreciate it!

• ###### 9. Re: Calculation requires chronological order

Hi Diane,

Here, you go..hope it helps!  Thx, Don