topic Re: Facing Problem to find out the occupancy rate of a healthcare department in QlikView

Facing Problem to find out the occupancy rate of a healthcare department

mohitd910 — Wed, 25 Nov 2020 16:16:04 GMT

Deal All,

stalwar1‌

gwassenaar

mto‌

loveisfail‌

mayilvahanan‌

In my data 3 things are main

MOVED_IN_DATE,

OUT_DATE,

IH_EMR_VST_ID

Now Moved_IN_Date is admission Date and Out_Date is Discharge date

Now i want to calculate the Occupancy rate for the Hospital by per day ,per month, per year

Example:-

Like for 1 Jan 2014 my Distinct Count IH_EMR_VST_ID is 40

Next day 2 Jan 2014 - 20 more admissions happens but 3 discharge. Now i want as a result for 2 Jan 2014 = 57

Please Find the attached Qvds and and data to the query.

Regards,

Mohit Dahiya

Re: Facing Problem to find out the occupancy rate of a healthcare department

CELAMBARASAN — Sat, 26 Nov 2016 12:05:38 GMT

Hi Mohit,

Please use the below script , also check the attached qv file

Admissions:

LOAD IH_EMR_VST_ID,

MOVED_IN_DATE,

OUT_DATE,

IH_PHL_BED_IH_PHL_PHL_ID,

IH_PHL_BED_BED_LABEL,

LOS,

IH_EMR_PAT_ID,

IH_EMR_OUT_ID,

IH_EMR_INV_ID,

IH_EMR_CMV_ID,

IH_EMR_VST_ID_MAS,

AGE,

AGE_UNIT,

STAGE,

VST_STATUS,

NOTE_TYPE,

IH_EMR_VST_DATE,

VST_DATE_CREATED,

VST_DATE_MODIFIED,

TYPE_OF_VISIT,

IH_POS_POS_ID,

IH_HCP_HCP_ID

FROM

(qvd);

LinkTable:

LOAD

IH_EMR_VST_ID,

Date(MOVED_IN_DATE + IterNo() -1) as CalendarDate

Resident Admissions

While

(MOVED_IN_DATE + IterNo() -1) <= OUT_DATE;

Re: Facing Problem to find out the occupancy rate of a healthcare department

mohitd910 — Sat, 26 Nov 2016 12:18:51 GMT

Bro data is not matching

Re: Facing Problem to find out the occupancy rate of a healthcare department

Anonymous — Sat, 26 Nov 2016 15:22:18 GMT

Hi Mohit

I thought it will help you.So i am sharing similar logic. i applied for my requirement.

try this it will help you.

-----------------------------------------------------------------------

Rawtable:

Date	Hired	Terminated
01-01-2011	6	0
01-02-2011	4	2
01-03-2011	6	1

IF ( row()=1, Hired-Terminated , PEEK (EmployeeCount,-1) + (Hired-Terminated) ) = EmployeeCount

1^ST-> row=1 , 6-0 , # =6

2^nd-> =2, # , 6+(4-2) =8

3^rd-> =3, # , 8+(6-1) =13

Table1:

Row	Date	Hired	Terminated	Employee Count
1	01-01-2011	6	0	6
2	01-02-2011	4	2	8
3	01-03-2011	6	1	13

IF ( ROWNO()=1 , 0, EmployeeCount - PREVIOUS ( EmployeeCount ) ) = EmployeeVariance

1^st-> row=1, 0, # =0

2^nd-> =2 #, 8 - 6 =2

3^rd-> =3, #, 13 - 8 =5

Table2:

Row	Date	Employee Count	Employee Var
1	01-01-2011	6	0
2	01-02-2011	8	2
3	01-03-2011	13	5