Count Days Within Date Range When START_TIME is Lower

megasupermoon — Tue, 08 Aug 2023 08:54:52 GMT

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:

Re: Count Days Within Date Range When START_TIME is Lower

edwin — Mon, 14 Aug 2023 22:01:47 GMT

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:

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

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

topic Count Days Within Date Range When START_TIME is Lower in App Development

Count Days Within Date Range When START_TIME is Lower

Re: Count Days Within Date Range When START_TIME is Lower