Room Turnover

[kjeffries16]

Hello,

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.

Kim

[ogautier62]

Hi,

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)

regards

[mikaelsc]

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...

[kjeffries16]

This one calculates the OR time for the single patient/procedure, so I'm not sure what I'm doing wrong with the formula.

[kjeffries16]

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.

[mikaelsc]

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

tempTable2:

Load

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:

Load

*,

StartTime - PreviousOpEndTime as DelayBetweenPreviousAndStartOp

resident tempTable 2;

drop tables...