What's your expectation of the end result? Not quite get what's issue and what you want to achieve.
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.
2 of 2 people found this helpful
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
Only Surgeons were joined. The "joining" of time blocks was made with a calculation used as filter.
These are the calculated fields used:
[Surg. In] <= [Block End Day] AND
[Surg. Out] >= [Block Start Day]
MAX([Surg. In],[Block Start Day]),
MIN([Surg. Out],[Block End Day])
DATEDIFF('minute',[Surg. In],[Surg. Out])
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
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.
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.