topic How to calculate number of weekends/holidays between two successive dates in a column in QlikView

How to calculate number of weekends/holidays between two successive dates in a column

pranaview — Wed, 25 Nov 2020 16:16:04 GMT

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.

Pranav

Re: How to calculate number of weekends/holidays between two successive dates in a column

avkeep01 — Wed, 24 Oct 2018 13:32:16 GMT

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;

DROP TABLE temp01;

Final:

NOCONCATENATE LOAD

RequestID,

Status,

CreationDate,

SUM(NoOfWeekendDays) AS NoOfWeekendDays

RESIDENT temp02

GROUP BY RequestID, Status, CreationDate;

DROP TABLE temp02;