Qlik Community

New to Qlik Sense

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

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qliksense_77
Creator
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_stayArrivalDateTimeDepartureDateTimeinterval(sum(ArrivalDateTime-DepartureDateTime), 'HH:MM')
4437958219-08-2020 10:00:0019-08-2020 17:25:000,31
4712067819-08-2020 18:00:0020-08-2020 12:00:000,75
4825007619-08-2020 12:00:0019-08-2020 20:41:000,36
5515927519-08-2021 14:42:0020-08-2021 10:43:000,83
5523897919-08-2021 15:45:0019-08-2021 22:00:000,26
5524174119-08-2021 00:40:0019-08-2021 09:30:000,37
5529213719-08-2021 11:30:0021-08-2021 11:15:001,99
5531349319-08-2021 14:03:0020-08-2021 11:30:000,89
5531429419-08-2021 17:00:0022-08-2021 14:35:002,90
5542527819-08-2020 17:45:0020-08-2020 15:00:000,89
5563133619-08-2021 20:30:0022-08-2021 22:10:003,07
5563224519-08-2021 20:45:0004-09-2021 13:39:0015,70

 

Desired outcome for specific ID:

ID_stayDate_occypancyOccypancy per date
5563133619/08/2021                                0,15
5563133620/08/2021                                1,00
5563133621/08/2021                                1,00
5563133622/08/2021                                0,92
Total: 3,07
1 Solution

Accepted Solutions
rbartley
Specialist
Specialist

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;

rbartley_0-1633094301052.png

 



View solution in original post

5 Replies
rbartley
Specialist
Specialist

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;

community_daysinhospital.PNG

App (qvf)  attached.

Qliksense_77
Creator
Creator
Author

@rbartley . Thats perfect, thank you 😀

Qliksense_77
Creator
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?

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

];

 

Qliksense_77_0-1633087124463.png

 

rbartley
Specialist
Specialist

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;

rbartley_0-1633094301052.png

 



Qliksense_77
Creator
Creator
Author

thank you @rbartley, its perfect now 😁