Set analysis with Condition

[priyankamalapaka]

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

[Jihad99]

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.