Exact occupancy per date

[Qliksense_77]

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

[rbartley]

Hi,

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

[data]:

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

[Qliksense_77]

@rbartley . Thats perfect, thank you 😀

[Qliksense_77]

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?

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

];

[rbartley · Accepted solution]

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

[data_expanded]:
NoConcatenate
LOAD
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;

[Qliksense_77]

thank you @rbartley, its perfect now 😁