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

    Block Time analysis

    Aaron Valleroy

      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
          Zhouyi Zhang

          Hi, Aaron


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



          • 2. Re: Block Time analysis
            Aaron Valleroy

            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



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

              Block time analysis - Join - thread 241110.png



              These are the calculated fields used:



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

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


              Block Minutes


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

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



              Surg. Minutes

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


              Final ResultBlock time analysis - thread 241110.png

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

                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
                    Aaron Valleroy

                    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.