Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pradnyan
New Contributor II

Calculate Date-Time Interval without considering weekend.


id

Start Date

End Date

A13/07/2017  05:01:1514/07/2017 03:10:12
B14/07/2017 02:12:40

17/07/2017 01:10:30

C18/07/2017 04:05:1220/07/2017 02:15:30

In Above Scenario

I want to calculate the interval between ID's,

1) in case of ID "A" we can directly calculate date-time Interval as there's no weekend or holiday.

2) but in case of ID "B" start date is 14/07/2017 i.e Friday and end date is 17/07/2017 i.e Monday so there's weekend between dates.

so how to calculate date-time Interval without considering weekend.

3) and In case of ID "C" suppose there is Holiday on 19/07/2017, so how to calculate date-time Interval without considering holiday.

8 Replies
antoniotiman
Honored Contributor III

Re: Calculate Date-Time Interval without considering weekend.

sateeshkumar
Contributor

Re: Calculate Date-Time Interval without considering weekend.

Hi Pradnya,

Just try using below method:

networkdays (start_date, end_date [, holiday])


Thanks,

Sateesh

pradnyan
New Contributor II

Re: Calculate Date-Time Interval without considering weekend.

Thank you for suggestion, but i want to calculate with time so not getting correct result.

pradnyan
New Contributor II

Re: Calculate Date-Time Interval without considering weekend.

Thank you, I will try and let you know.

antoniotiman
Honored Contributor III

Re: Calculate Date-Time Interval without considering weekend.

NetWorkDays(Date([Start Date]),Date([End Date]))

and Set Variables in Script according Your Source Date Format

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

pradnyan
New Contributor II

Re: Calculate Date-Time Interval without considering weekend.

id

StartDate

EndDate

A

01/01/2017 10:11:44

02/01/2017 12:11:44

B

02/01/2017 10:11:44

03/01/2017 05:11:44

C

03/01/2017 10:11:44

04/01/2017 01:11:44

D

04/01/2017 10:11:44

05/01/2017 02:11:44

E

05/01/2017 10:11:44

06/01/2017 03:11:44

F

06/01/2017 10:11:44

07/01/2017 03:11:44

G

07/01/2017 10:11:44

08/01/2017 04:11:44

H

06/01/2017 10:11:44

09/01/2017 15:11:44

I

09/01/2017 10:11:44

10/01/2017 06:11:44

Here in above eg we need to find TAT between EndDate and StartDate.I need to exclude saturday ,sunday and holidays.

Above timings are in date and time .We need the TAT in exact days and time.

There is an 'id' in the above data having StartDate 06/01/2017 10:11:44 and EndDate 09/01/2017 15:11:44. In between these days there are sat and sunday.

According to me whatever the time difference will be there we need to exclude 48hrs of sat n sunday and display the output .Output will be 3 days 5hrs . And if we exclude sat and sunday 1 day 5 hrs.


Kindly suggest and help


antoniotiman
Honored Contributor III

Re: Calculate Date-Time Interval without considering weekend.

Try this

LOAD *,
If(EndDate-StartDate >= NetWorkDays(StartDate,EndDate),
Interval(EndDate-StartDate - NetWorkDays(StartDate,EndDate),'D hh:mm'),
Interval(Fabs(EndDate+1-StartDate - NetWorkDays(StartDate,EndDate)),'hh:mm')) as TAT Inline [
id,StartDate,EndDate
A,01/01/2017 10:11:44,02/01/2017 12:11:44
B,02/01/2017 10:11:44,03/01/2017 05:11:44
C,03/01/2017 10:11:44,04/01/2017 01:11:44
D,04/01/2017 10:11:44,05/01/2017 02:11:44
E,05/01/2017 10:11:44,06/01/2017 03:11:44
F,06/01/2017 10:11:44,07/01/2017 03:11:44
G,07/01/2017 10:11:44,08/01/2017 04:11:44
H,06/01/2017 10:11:44,09/01/2017 15:11:44
I,09/01/2017 10:11:44,10/01/2017 06:11:44]
;

Regards,

Antonio

mangalsk
Contributor III

Re: Calculate Date-Time Interval without considering weekend.

Dear Pradnya ,

PFA

Community Browser