Qlik Community

Qlik Healthcare User Group

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

New Contributor

Set analysis with Condition


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


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;


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

Tags (1)
1 Reply
New Contributor II

Re: Set analysis with Condition


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)