Count Days Within Date Range When START_TIME is Lower

[megasupermoon]

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:

[Tanish]

Hi,

You can use this Chart which is under Customer object. This Chart will let you select the date range 

[megasupermoon]

Hey Tanish,

Yes, I have that but it selects only those that started within selected time range (date picker using PERIOID_START column (1st SQL)).  It should select also those department periods that have days within the selected time range even if they started outside the selected time range but ended within it. After that, it should subtract the days that are outside the selected time range.

If I use DATE column (2nd SQL) Date picker selects correct rows, I believe. But report counts the total sum of days within department period from the start to the end when it should subtract those days outside selected date range.

So, maybe I should, somehow, try to count the days in a way other than directly in the code with DATEDIFF?

I've been wondering if I could get this to work with the master calendar somehow.