Calculating number of admission days in Qlik Sense

[Sharbel]

Hi,

I have the following data regarding admission days :

Null --> that means the patient is not released yet.

I am trying to create bar chart that displays number of admission days per Year:

Any ideas on how to calculate number of admission days per period (Year/Month)?

Attached excel file as reference.

Thanks,

Sharbel

[Oleg_Troyansky]

It looks like you need to use IntervalMatch and associate each Month (start or end date) with the range of dates between admission and discharge. Then, you can calculate the number of days for each month, depending on the relations between admission dates and month start dates and discharge dates and month end dates. Something like this...

[Sharbel]

i ran the following load script using IntervalMatch: 

DimDate1:

Load StartMonth

Resident Dim_Date;

Admission:

Load * Inline [

tEnter,tExit,VisitId

08/11/2020,15/05/2021,21021384

08/05/2022,01/07/2023,23033315

];

Inner join 

IntervalMatch (StartMonth)

Load tEnter,tExit

Resident Admission;

Drop Table DimDate1;

DimDate2:

Load EndMonth

Resident Dim_Date;

NoConcatenate

Admission2:

Load tEnter,tExit,VisitId

Resident Admission;

Drop Table Admission;

Inner join 

IntervalMatch (EndMonth)

Load tEnter,tExit

Resident Admission2;

Drop Table DimDate2;

the script returned Admissin2 table (see picture below) but the StartMonth Field is missing.

Any clues what went wrong with the Load script?