Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
megasupermoon
Contributor III
Contributor III

Count Days Within Date Range When START_TIME is Lower

I have Qlik report which carculates the lenghts of department periods in different departments in days. First user should select date range (for example 2023/04/30 – 2023/05/31) and then report should count days between that range even when period’s starting date is, for example, during March 2023.

In other words, for example:

1) user selects date range 2023/04/30 – 2023/05/31

2) department period started 2023/03/15 and ended 2023/04/15 (so qlik should pick every row where there are days between selected range within department period’s start and end dates or something like that, right?)

3) report should calculate days between 2023/04/01 and 2023/04/15

But I don’t know/figure out how this could be done. Can master calendar or something help with this and how? If not, does anyone know or have an idea how to this?

I’m not very good at explaining so if necessary, more detailed questions are welcome.

Thanks in advance!

Here’s my current codes and department period duration table when date range isn’t selected:

Departmentperiods:
SELECT
CONCAT(DP.OMISTAJA, '#', DP.PART) AS DEPARTMENTPERIOD_ID,
DP.OWNER AS PATIENT_ID,
DP.START_TIME AS PERIID_START,
D.YEAR AS YEAR,
DE.NAME AS DEPARTMENT_NAME,
CASE WHEN DP.END_TIME IS NULL THEN GETDATE()
ELSE DP.END_TIME END AS PERIOD_END,
DATEDIFF("DAY", DP.START_TIME, ISNULL(DP.END_TIME, CURRENT_TIMESTAMP)) + (
		CASE
			WHEN DP.DELETED = 'E' AND DP.START_TIME = DP.END_TIME THEN 1
		 	ELSE 0
		END
	)  AS DEPARTMENTPERIOD_DURATION,
DATEDIFF
(
	DAY,
    DP.START_TIME,
	ISNULL(DP.END_TIME, CURRENT_TIMESTAMP)
) + (CASE
	WHEN DP.START_TIME = DP.END_TIME THEN 1
    	ELSE 0 END ) AS DEPARTMENTPERIOD_NETDURATION,

CASE 
	WHEN D.MM = 1 THEN 'January'
	WHEN D.MM = 2 THEN 'February'
	WHEN D.MM = 3 THEN 'March'
	WHEN D.MM = 4 THEN 'April'
	WHEN D.MM = 5 THEN 'May'
	WHEN D.MM = 6 THEN 'June'
	WHEN D.MM = 7 THEN 'July'
	WHEN D.MM = 8 THEN 'August'
	WHEN D.MM = 9 THEN 'September'
	WHEN D.MM = 10 THEN 'October'
	WHEN D.MM = 11 THEN 'November'
        ELSE 'December' END AS MONTH

FROM DEPARTMENTPERIODS DP

JOIN DIM_DATE D
    ON D.YEAR = YEAR(DP.START_TIME)
    AND D.MM = MONTH(DP.START_TIME)
    AND D.DAY = DAY(DP.START_TIME)
    
JOIN DEPARTMENTS DE
    ON DE.OWNER = DP.DEPARTMENT
	
WHERE (	
	DP.END_TIME >= '2022-01-01'
	OR DP.END_TIME IS NULL
      )
    AND DP.START_DATE < GETDATE()
    AND DP.START_DATE > '2014-01-01'

GROUP BY DP.OWNER, DP.DEPARTMENT, DP.START_DATE, DP.END_DATE, DP.PART, D.MM, DE.NAME, D.YEAR, DP.DELETED
;

departments and periods

SET DateFormat='DD.MM.YYYY';

PeriodDurations:
LOAD 
    *, 
    Date(PERIOD_START+IterNo()-1, 'DD.MM.YYYY') as DATE

While PERIOD_START+IterNo()-1<=DATE(PERIOD_END);

LOAD 
DEPARTMENTPERID_ID AS PERIOD_ID,
YEAR,
MONTH,
DEPARTMENT_NAME,
PATIENT_ID AS PATIENT,
DEPARTMENTPERIOD_DURATION AS NETDAYS,
DATE(PERIOD_START) AS PERIOD_START,
MIN(DATE(PERIOD_END)) AS PERIOD_END

Resident Departmentperiods
GROUP BY DEPARTMENTPERIOD_ID, PATIENT_ID, DEPARTMENTPERIOD_DURATION, PERIOD_START, PERIOD_END, YEAR, DEPARTMENT_NAME, MONTH
;

for date range selection

and table:

1.JPG

 

Labels (3)
1 Solution

Accepted Solutions
edwin
Master II
Master II

there are two parts to your problem, first you need a bridge between your dept start and end dates to the calendar.  this way you can compute the number of days between the department start and end dates.  im guessing your calendar will later become a bit complicated as you may want to incorporate date attributes like weekends and holidays and dates where the depts arent operating (assuming also all depts have the same calendar)

then the other question is gven a separate start and end date field that the user enters bridge that to your calendar as well.

 

data:
load *, deptStartDate & '|' & deptEndDate as deptDateKey inline [
dept, deptStartDate, deptEndDate
D1, 4/30/2023, 5/15/2023
D2, 5/15/2023, 6/30/2023
D3, 5/1/2023, 7/31/2023
];

calendar:
load date(date('7/31/2023')-iterno()+1) as date
while date(date('7/31/2023')-iterno()+1)>=date('4/30/2023');
load 1 AutoGenerate(1);


tmp:
load distinct 
	deptStartDate, deptEndDate, deptDateKey
Resident data;

Inner join (tmp)
load date Resident
calendar;

NoConcatenate
deptBridge:
load deptDateKey, date
Resident tmp
where date >=deptStartDate and date<=deptEndDate;

drop table tmp;

NoConcatenate
tmp: 
load date as startDate Resident calendar;

inner join (tmp)
load date as endDate Resident calendar;

selectionDates:
load startDate, endDate, startDate & '|' &  endDate as selectionDateKey
Resident
	tmp
where startDate<= endDate;
drop table tmp;

NoConcatenate
tmp:
load startDate, endDate, selectionDateKey
Resident
	selectionDates;
    
inner join (tmp)
load date Resident calendar;

selectionDateBridge:
load selectionDateKey, date
Resident
	tmp
where date >= startDate and date<=endDate;

drop table tmp;

 

 

here is the DM:

edwin_0-1692050303910.png

 

here is a sample, users elects start and end dates:

edwin_1-1692050340656.png

from the fake data only D1 and D3 are selected bec they have valida dates between the date ranges selected.

since you built most of your logic in your DM, the couting is straight forward and will execute pretty quick

View solution in original post

1 Reply
edwin
Master II
Master II

there are two parts to your problem, first you need a bridge between your dept start and end dates to the calendar.  this way you can compute the number of days between the department start and end dates.  im guessing your calendar will later become a bit complicated as you may want to incorporate date attributes like weekends and holidays and dates where the depts arent operating (assuming also all depts have the same calendar)

then the other question is gven a separate start and end date field that the user enters bridge that to your calendar as well.

 

data:
load *, deptStartDate & '|' & deptEndDate as deptDateKey inline [
dept, deptStartDate, deptEndDate
D1, 4/30/2023, 5/15/2023
D2, 5/15/2023, 6/30/2023
D3, 5/1/2023, 7/31/2023
];

calendar:
load date(date('7/31/2023')-iterno()+1) as date
while date(date('7/31/2023')-iterno()+1)>=date('4/30/2023');
load 1 AutoGenerate(1);


tmp:
load distinct 
	deptStartDate, deptEndDate, deptDateKey
Resident data;

Inner join (tmp)
load date Resident
calendar;

NoConcatenate
deptBridge:
load deptDateKey, date
Resident tmp
where date >=deptStartDate and date<=deptEndDate;

drop table tmp;

NoConcatenate
tmp: 
load date as startDate Resident calendar;

inner join (tmp)
load date as endDate Resident calendar;

selectionDates:
load startDate, endDate, startDate & '|' &  endDate as selectionDateKey
Resident
	tmp
where startDate<= endDate;
drop table tmp;

NoConcatenate
tmp:
load startDate, endDate, selectionDateKey
Resident
	selectionDates;
    
inner join (tmp)
load date Resident calendar;

selectionDateBridge:
load selectionDateKey, date
Resident
	tmp
where date >= startDate and date<=endDate;

drop table tmp;

 

 

here is the DM:

edwin_0-1692050303910.png

 

here is a sample, users elects start and end dates:

edwin_1-1692050340656.png

from the fake data only D1 and D3 are selected bec they have valida dates between the date ranges selected.

since you built most of your logic in your DM, the couting is straight forward and will execute pretty quick