Occupied bed days and Seplos calculations

[juliefletcher]

Good Afternoon, I am very new to qlik sense and I am trying to write a report that has Occupied bed days and sepLOS calculations in it. 

I have the admission and discharge dates of the patients.(please see below spreadsheet). I just am unsure of how to get  qlik sense to produce the same numbers especially the highlighted ones, in a report. I can do it in the spreadsheet ok.

I don't have a IT or programming background, I analyse data and create reports for others.   

Any help would be much appreciated.

[Vegar]

I notice that your dates are in different format, it might be a trouble if you don't handle it correctly.

To calculate occupancy just subtract Adm Date+1 from Disch Date. 

Like this:

LOAD 

[Disch Date], [Adm Date], 

Date#([Disch Date], 'DD. MM. YYYY') + 1 - Date#([Adm Date], 'D/MM/YYYY') AS Occupacy

FROM...

Please explain the concept of SepLOS in order for me to help you. 

[juliefletcher]

thanks for your reply,

SepLOS is if admit date is 07/04/2020 and disch is 05/05/2020 then there is 28 days in total

7/04/2020

5/05/2020

5A

4

28

Where as occupied bed days  is from the 01/05/2020 to 05/05/2020, and in both cases you don't count the Admit date.

Hope this makes sense. Sorry can I ask why the +1 in your example?? is that for the admit date not being counted??

Thanks

[Vegar]

My previous calculation is not Occupacy but an attaempt to calculate Sep Los. Yes I used +1 to include the admit date.

I looked again at your excel and do not really understand how you get the Sep Los values that you do.

How come the two yellow marked transactions have the same Sep Los value? They have different Adm dates, but the same dish dates - shouldn't the Sep Los differ between the two?

You also need to explain the Occupacy again, I didn't understand what you are doing? 

Could you please explain why Occupancy is 2? (In his case I don't even understand why Sep Los is 0)

Adm Date	Disch Date	Care Type	Leave Days	Occupancy	Sep Los
30/05/2020	2020-06-01	4		2	0

[juliefletcher]

A better explanation of what I am after  hopefully

Occupied bed days

The total number of bed days of all admitted patients accommodated

during the reporting period (month) and

• The day the patient is separated is not counted as a patient day.

SEPLOS

• Acute patient length of stay (LOS) is the number of days an acute patient spends in hospital and
• The day the patient is separated is not counted as a patient day.

The case that you said below includes Leave Days but I will work this out later.

[Saravanan_Desingh]

Are you looking something like this?

tab1:
LOAD *, [Disch Date]-[Adm Date] As [Sep Los]
	  , [Disch Date]-If(Month([Disch Date])=Month([Adm Date]),[Adm Date],MonthStart([Disch Date])) As Occupancy;
LOAD RowID, Date(Date#([Adm Date],'D/MM/YYYY')) As [Adm Date], [Disch Date], [Care Type];
LOAD RecNo() As RowID,* INLINE [
    Adm Date, Disch Date, Care Type
     4/05/2020, 5/4/2020, 4
     6/05/2020, 5/9/2020, 4
     29/05/2020, 5/30/2020, 4
     23/05/2020, 5/24/2020, 4
     21/05/2020, 5/25/2020, 4
     7/04/2020, 5/5/2020, 5A
     28/05/2020, 5/28/2020, 4
     3/05/2020, 5/4/2020, 4
     18/05/2020, 5/18/2020, 4
     3/05/2020, 5/6/2020, 4
     20/05/2020, 5/20/2020, 4
     28/05/2020, 5/28/2020, 4
     17/05/2020, 5/19/2020, 4
     20/05/2020, 5/29/2020, 8
     20/05/2020, 5/28/2020, 6
     20/05/2020, 5/20/2020, 4
     21/05/2020, 5/25/2020, 4
     18/05/2020, 5/18/2020, 4
     11/05/2020, 5/11/2020, 4
     29/05/2020, 5/29/2020, 4
     16/05/2020, 5/18/2020, 4
     30/05/2020, 6/1/2020, 4
     21/05/2020, 5/21/2020, 4
     19/05/2020, 5/19/2020, 4
     1/05/2020, 5/7/2020, 4
     14/05/2020, 5/18/2020, 4
     4/05/2020, 5/4/2020, 4
     25/05/2020, 5/25/2020, 4
     29/05/2020, 5/29/2020, 4
     22/05/2020, 5/23/2020, 4
     26/05/2020, 6/1/2020, 4
];

[Saravanan_Desingh]

Output.

[juliefletcher]

Thanks so much that has worked , except in occupancy how can I get it to add a one for patients admitted and dischargesd on the same day ( known as same day patients)???

Really like your work

Thanks again.

[Saravanan_Desingh]

Try this.

tab1:
LOAD *, If([Disch Date]=[Adm Date],1,[Disch Date]-[Adm Date]) As [Sep Los]
	  , If([Disch Date]=[Adm Date],1,
	  		[Disch Date]-If(Month([Disch Date])=Month([Adm Date]),[Adm Date],MonthStart([Disch Date]))) 
	  		As Occupancy;
LOAD RowID, Date(Date#([Adm Date],'D/MM/YYYY')) As [Adm Date], [Disch Date], [Care Type];
LOAD RecNo() As RowID,* INLINE [
    Adm Date, Disch Date, Care Type
     4/05/2020, 5/4/2020, 4
     6/05/2020, 5/9/2020, 4
     29/05/2020, 5/30/2020, 4
     23/05/2020, 5/24/2020, 4
     21/05/2020, 5/25/2020, 4
     7/04/2020, 5/5/2020, 5A
     28/05/2020, 5/28/2020, 4
     3/05/2020, 5/4/2020, 4
     18/05/2020, 5/18/2020, 4
     3/05/2020, 5/6/2020, 4
     20/05/2020, 5/20/2020, 4
     28/05/2020, 5/28/2020, 4
     17/05/2020, 5/19/2020, 4
     20/05/2020, 5/29/2020, 8
     20/05/2020, 5/28/2020, 6
     20/05/2020, 5/20/2020, 4
     21/05/2020, 5/25/2020, 4
     18/05/2020, 5/18/2020, 4
     11/05/2020, 5/11/2020, 4
     29/05/2020, 5/29/2020, 4
     16/05/2020, 5/18/2020, 4
     30/05/2020, 6/1/2020, 4
     21/05/2020, 5/21/2020, 4
     19/05/2020, 5/19/2020, 4
     1/05/2020, 5/7/2020, 4
     14/05/2020, 5/18/2020, 4
     4/05/2020, 5/4/2020, 4
     25/05/2020, 5/25/2020, 4
     29/05/2020, 5/29/2020, 4
     22/05/2020, 5/23/2020, 4
     26/05/2020, 6/1/2020, 4
];

[Saravanan_Desingh · Accepted solution]