How to calculate number of weekends/holidays between two successive dates in a column
Hi Guys,
I'm kinda stuck with this problem which is like the last step to wrap up the business logic for my app.
I have to calculate time difference for a Datetime column for successive dates. For example
RequestID
Status
CreationDate
Difference
1
Originator
9/5/2018 12:18
-
1
Processor
9/10/2018 12:20
Diff
1
Validation
9/10/2018 17:20
Diff
1
Closed
9/12/2018 9:20
Diff
Now when I take the difference for first and second date, I'll get a weekend(sat and sun) i.e. 48 hours i need to deduct from the total difference. Now this is just one example, there can be n number of weekends or holidays in between . Can anyone help me write the logic where I can get the exact number of weekends/holidays in between two successive dates in the column then I can just multiply it with 24 to get total hours to deduct.
I tried to use NetworkDays() function but it didn't work out as I wanted it to or may be I could not use it properly to get the desired outcome.
Any help or suggestions will be hugely appreciated. I need to get this done to get over the line as this is quite obviously a blocker for me.
Thanks in advance.
P.S. I'm working in Qlik Sense but I guess there won't be any difference in logic in QlikView.
Hi Pranav, I needed to change the dates and the formats a bit. I changed the data so that multiple weekends are inlcuded. I created a date from and date till for every requestid with the previous function. Then I filled the dates between and flagged them when they are Saturday or Sunday. Then I summed the flags per requestID
Maybe you need a little adjustments, But you can try this script.
temp01:
LOAD
RequestID,
Status,
DATE(CreationDate) AS CreationDate,
DATE(IF(RequestID=PREVIOUS(RequestID),PREVIOUS(CreationDate),CreationDate)) AS Prev_CreationDate
INLINE [
RequestID, Status, CreationDate
1, Originator, 05-09-2018 12:18:00
1, Processor, 06-09-2018 12:20:00
1, Validation, 12-09-2018 17:20:00
1, Closed, 18-09-2018 09:20:00
];
temp02:
NOCONCATENATE LOAD
RequestID,
Status,
CreationDate,
Prev_CreationDate,
DATE(Prev_CreationDate+(ITERNO()-1)) AS Date,
IF(MATCH(WEEKDAY(DATE(Prev_CreationDate+(ITERNO()-1))),'Sa','Su')>0,1) AS NoOfWeekendDays
RESIDENT temp01
WHILE Prev_CreationDate+(ITERNO()-1) <= CreationDate;
