# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Creator

## Exact occupancy per date

Hi,

I have data like below with hospital stays.

I would like to calculate the exact bed occupancy per day.

An example is below (green text) with the ID_stay 55631336. The patient arrives  20:30 on the 19-08-2021 and therefore occupies the bed for 0,15 day on the 19-08-2021. And so on.

I want to be able to generate a statistic of the total occupancy per date.

I could also be interested in a solution for calculating occupancy per hour. But that is not so important right now.

Anybody that can help?

Data:

 ID_stay ArrivalDateTime DepartureDateTime interval(sum(ArrivalDateTime-DepartureDateTime), 'HH:MM') 44379582 19-08-2020 10:00:00 19-08-2020 17:25:00 0,31 47120678 19-08-2020 18:00:00 20-08-2020 12:00:00 0,75 48250076 19-08-2020 12:00:00 19-08-2020 20:41:00 0,36 55159275 19-08-2021 14:42:00 20-08-2021 10:43:00 0,83 55238979 19-08-2021 15:45:00 19-08-2021 22:00:00 0,26 55241741 19-08-2021 00:40:00 19-08-2021 09:30:00 0,37 55292137 19-08-2021 11:30:00 21-08-2021 11:15:00 1,99 55313493 19-08-2021 14:03:00 20-08-2021 11:30:00 0,89 55314294 19-08-2021 17:00:00 22-08-2021 14:35:00 2,90 55425278 19-08-2020 17:45:00 20-08-2020 15:00:00 0,89 55631336 19-08-2021 20:30:00 22-08-2021 22:10:00 3,07 55632245 19-08-2021 20:45:00 04-09-2021 13:39:00 15,70

Desired outcome for specific ID:

 ID_stay Date_occypancy Occypancy per date 55631336 19/08/2021 0,15 55631336 20/08/2021 1,00 55631336 21/08/2021 1,00 55631336 22/08/2021 0,92 Total: 3,07
1 Solution

Accepted Solutions
Specialist

Sure, I made a mistake with the Occupancy logic,  replace the [date_expanded] table with this one:-

``````[data_expanded]:
NoConcatenate
ID_stay,
ArrivalDateTime,
DepartureDateTime,
Date(ArrivalDate + IterNo()-1) as OccupancyDate,
IterNo( ) as DaysInHospital,
if(
DepartureDate=ArrivalDate,OccupancyTime, //in and out on same day
if(IterNo( )>1 And IterNo( )-1<OccupancyTime And Date(ArrivalDate + IterNo()-1) <>DepartureDate,1,  //Total occupancy time > DaysInHospital so full day
if(IterNo( )=1,Round(Ceil(ArrivalDateTime)-ArrivalDateTime,.001), //First day
Round(DepartureDateTime-Floor(DepartureDateTime),.001) //Last day
)
)
) as Occupancy,
OccupancyTime
Resident data2
while IterNo( ) <= DepartureDate - ArrivalDate +1;``````

5 Replies
Specialist

Hi,

You need to use the IterNo() function combined with while.  Here's how I did it:

``````[data]:

Inline
[ID_stay	,	ArrivalDateTime	,	DepartureDateTime	,	OccupancyTime
44379582	,	19/08/2020 10:00	,	19/08/2020 17:25	,	0.31
47120678	,	19/08/2020 18:00	,	20/08/2020 12:00	,	0.75
48250076	,	19/08/2020 12:00	,	19/08/2020 20:41	,	0.36
55159275	,	19/08/2021 14:42	,	20/08/2021 10:43	,	0.83
55238979	,	19/08/2021 15:45	,	19/08/2021 22:00	,	0.26
55241741	,	19/08/2021 00:40	,	19/08/2021 09:30	,	0.37
55292137	,	19/08/2021 11:30	,	21/08/2021 11:15	,	1.99
55313493	,	19/08/2021 14:03	,	20/08/2021 11:30	,	0.89
55314294	,	19/08/2021 17:00	,	22/08/2021 14:35	,	2.90
55425278	,	19/08/2020 17:45	,	20/08/2020 15:00	,	0.89
55631336	,	19/08/2021 20:30	,	22/08/2021 22:10	,	3.07
55632245	,	19/08/2021 20:45	,	04/09/2021 13:39	,	15.70
];

//convert date strings into dates and numbers
[data2]:
NoConcatenate
ID_stay,
Date#(ArrivalDateTime,'DD/MM/YYYY hh:mm') as ArrivalDateTime,
Date#(DepartureDateTime,'DD/MM/YYYY hh:mm') as DepartureDateTime,
Num(Floor(Date#(ArrivalDateTime,'DD/MM/YYYY hh:mm'))) as ArrivalDate,
Num(Floor(Date#(DepartureDateTime,'DD/MM/YYYY hh:mm'))) as DepartureDate,
OccupancyTime
Resident data;

Drop Table data;

//iterate between arrival date and departure date and calculate occupancy
[data_expanded]:
NoConcatenate
ID_stay,
ArrivalDateTime,
DepartureDateTime,
Date(ArrivalDate + IterNo()-1) as OccupancyDate,
IterNo( ) as DaysInHospital,
if(
DepartureDate=ArrivalDate,OccupancyTime, //in and out on same day
if(IterNo( )>1 And IterNo( )-1<OccupancyTime,1,  //Total occupancy time > DaysInHospital so full day
if(IterNo( )=1,Round(Ceil(ArrivalDateTime)-ArrivalDateTime,.001),
Round(DepartureDateTime-Floor(DepartureDateTime),.001)
)
)
) as Occupancy,
OccupancyTime
Resident data2
while IterNo( ) <= DepartureDate - ArrivalDate +1;

Drop Table data2;
``````

App (qvf)  attached.

Creator
Author

@rbartley . Thats perfect, thank you 😀

Creator
Author

Hi again @rbartley

See the example below. The occupancytime on occupancyday the 15.10.2020 should not be a full day since the patient leaves at 13:20.  You know how to fix this?

Inline
[ID_stay , ArrivalDateTime , DepartureDateTime , OccupancyTime
44379582 , 09/10/2020 12:00 , 15/10/2020 13:20 , 6.05

];

Specialist

Sure, I made a mistake with the Occupancy logic,  replace the [date_expanded] table with this one:-

``````[data_expanded]:
NoConcatenate
ID_stay,
ArrivalDateTime,
DepartureDateTime,
Date(ArrivalDate + IterNo()-1) as OccupancyDate,
IterNo( ) as DaysInHospital,
if(
DepartureDate=ArrivalDate,OccupancyTime, //in and out on same day
if(IterNo( )>1 And IterNo( )-1<OccupancyTime And Date(ArrivalDate + IterNo()-1) <>DepartureDate,1,  //Total occupancy time > DaysInHospital so full day
if(IterNo( )=1,Round(Ceil(ArrivalDateTime)-ArrivalDateTime,.001), //First day
Round(DepartureDateTime-Floor(DepartureDateTime),.001) //Last day
)
)
) as Occupancy,
OccupancyTime
Resident data2
while IterNo( ) <= DepartureDate - ArrivalDate +1;``````

Creator
Author

thank you @rbartley, its perfect now 😁