Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

New Contributor II

Room Turnover


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.


5 Replies
Valued Contributor II

Re: Room Turnover


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)


Contributor III

Re: Room Turnover

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

New Contributor II

Re: Room Turnover

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

New Contributor II

Re: Room Turnover

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.

Contributor III

Re: Room Turnover

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

Community Browser