Qlik Community

Qlik Healthcare User Group

A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights.

Highlighted
priyankamalapaka
New Contributor

Set analysis with Condition

Hi, 

Am new to Qlikview . I have attended the training last week and trying to implement for the health domain.

Basically for each admission Id, we do have the admission date and Discharge date attributes. At times discharge attribute will be null( Empty) , for the Patients still in hospital.

So I want to include the records only with a valid discharge date and am trying to use the below expression to calculate the average length of stay ( i.e Dischargedate diff admission date ) but it does not work. Could you please help

Avg({$<Discharge_date={">=Date(01/01/2018)"}>}-admission_date).

Also, is there any null function check in qlikview?

backend code for tables load :

 

Load *,

Year(LoadDate) as admission_Year,
Month(LoadDate) as admission_Month,
Floor(LoadDate) as admission_date,
Floor(UnLoadDate) as Discharge_date;


LOAD ENCNTR_ID,

REG_DT_TM,
DISCH_DT_TM,
Date(Date#(REG_DT_TM,'DD/MM/YYYY')) as LoadDate,
Date(Date#(DISCH_DT_TM,'DD/MM/YYYY')) as UnLoadDate

Tags (1)
1 Reply
Jihad99
New Contributor II

Re: Set analysis with Condition

Hello,

Your formula is not correct.  In your reload script, you retrieved the admission_Year (2017, 2018, ...) so you can use it in the set analysis. Moreover, Qlikview when calculating the difference between 2 dates, it ignores the null values so you don't need to worry about the already in-hospital patients 🙂

Your formula should be like that: 

=avg({<admission_Year = {2018}>} discharge_date - admission_date)

Alternative if you want to use only the dates:

=avg({<admission_date = {">=1/1/2018<1/31/2018"}>} discharge_date - admission_date)

Thanks,

Jihad.