5 Replies Latest reply: Jun 5, 2018 4:44 AM by Mikael Scorielle RSS

    Room Turnover

    Kimberly Jeffries



      In QlikSense, I am trying to create a set analysis formula to determine average room turnover in our OR, and I'm having a bit of trouble getting started.

      The fields I have that I think I need are Entity, Department, Room Name, PatientInRoomInstant and PatientOutRoomInstant (mm/dd/yy hh:mm). I want to be able to have this calculate by day, week, month, year, depending on how the user filters the sheet.


      The metric is how quickly do we turn a room from when Patient A exits the room and Patient B enters for the next procedure.  There are multiple rooms in multiple areas with several facilities.


      Thanks in advance for any guidance.



        • Re: Room Turnover
          Olivier GAUTIER



          first you have to choose what date you link with your calendar : InInstant or outInstant


          then calculate the average of (OutInstant - InInstant) for row where outInstant not null, so :


          avg({<outInstant = {'*'} >}  outInstant - InInstant)



          • Re: Room Turnover
            Mikael Scorielle

            i'd recommend you start by defining what data you need to be able to make such a calculation, and if it's not available, calculate it in the script;


            at first siight, you need to have a field for each procedure that contains the end of the previous procedure in the same room on the same day

            you should be able to generate that field in the script using some "advanced" manipulations of your tables (order them by time, compare previous rows, conditionnaly calculate the end of the previous procedure for a certain procedure).

            when finished, i'm not even convinced you'll need set analysis...

              • Re: Room Turnover
                Kimberly Jeffries

                This data is auto-populated from every night at midnight, so I'm not sure the table function would work.

                The fields I need are Operating Room Name, Surgery Date, Patient In, Patient Out. The previous patient Patient Out to the next Patient In is where I'm stuck.  The table function would work, but I'm not sure how that would work with the source system field.

                  • Re: Room Turnover
                    Mikael Scorielle

                    don't expect this to be automatic... this is about bringing "intelligence" to data :

                    i'd think you'd have 3 steps (or more :-) ) :


                    1) create temporary table containing data from source system temp1: Load *; Sql select * from ***; etc.


                    2) create a table in which you do the "special" calculations:

                    - it needs to be sorted in order to allow calculations (order by)

                    - then use previous() function in combination with if() expressions to define your logic




                    if(previous(OpRoom)= OpRoom and previous(Date)= Date, previous(endTime)) as PreviousOpEndTime


                    resident temp1

                    order by OpRoom asc, Date asc;


                    3) additionnal creation of fields etc; dropping temp tables; etc

                    Final Table:



                    StartTime - PreviousOpEndTime as DelayBetweenPreviousAndStartOp

                    resident tempTable 2;


                    drop tables...