6 Replies Latest reply on Jul 7, 2017 9:46 AM by Aaron Valleroy

# Block Time analysis

I am hoping someone can help me identify a solution.  I am trying to build a calculation that will sum case time duration's (data tab) between a block start and block end date range  (SIS Block tab) for each individual surgeon.  Currently the data is setup as two separate data sources (two tabs within the same excel workbook).  Any help would be much appreciated.

• ###### 1. Re: Block Time analysis

Hi, Aaron

What's your expectation of the end result? Not quite get what's issue and what you want to achieve.

ZZ

• ###### 2. Re: Block Time analysis

Trying to develop a dashboard that I can look at each individual surgeons blocked time usage (daily, monthly and yearly).

In order to achieve this I need to find a way to have tableau sum (case duration times) within the given Surgeon's date and block start and end time.  I only want to capture the minutes within the Surgeons daily block start and end.  Then divide that by available minutes which is block start and block end time for that day.

I hope that is a little more clear.  The difficulty is only capturing the cases completed during that surgeons block start and end time.

• ###### 3. Re: Block Time analysis

The attached uses method 1 ( Periods Intersecting Calendar Periods ) of  The Cross Join Collection.

The sheet [Surgeon Block] in your spreadsheet was edited a little bit:

• A duplicate row was removed
• Calculated fields (Block Start Day & Block End Day) were added to make things simpler

Join

Only Surgeons were joined. The "joining" of time blocks was made with a calculation used as filter.

Calculations

These are the calculated fields used:

Filter

[Surg. In]  <= [Block End Day] AND

[Surg. Out] >= [Block Start Day]

Block Minutes

DATEDIFF('minute',

MAX([Surg. In],[Block Start Day]),

MIN([Surg. Out],[Block End Day])

)

Surg. Minutes

DATEDIFF('minute',[Surg. In],[Surg. Out])

Final Result

2 of 2 people found this helpful
• ###### 4. Re: Block Time analysis

Where you observed the duplicate that was actually a surgeon who had two separate rooms booked for the same time frame. Therefore he actually had 16 hours of available surgery time.  If I had that duplicate in the excel spreadsheet will this mess up the dashboard?

I appreciate your help and knowledge.  Thank you

• ###### 5. Re: Block Time analysis

I assume you need to include Room in "Surgeon Block" and thereafter include it in the join between "Surgeon Block" and "Data". This said, I don't really understand your hospital scenario and therefore don't know for sure what the expected output is. You can of course just try without adding/joining room and check if the output is what is expected.

• ###### 6. Re: Block Time analysis

Thank  you again very much this has gotten me much closer to the end result.  Which is taking available block time per surgeon vs actual used time from Surg in and Surg out.