topic Difference between dates - with no holidays in QlikView

Difference between dates - with no holidays

Wed, 20 Oct 2010 15:03:32 GMT

Hi Guys,

I need to calculate the time in days/hours/minutes between two dates, (substracting holidays).

I need to be able to set which day is a working day, and which one is a holiday.

Which should be the best way to achieve that?

Thanks in advance,

Aldo.

Difference between dates - with no holidays

boorgura — Wed, 20 Oct 2010 15:05:51 GMT

You can get the number of days easily using the NetWorkDays() function.

Where you can list the holidays as well.

Let me know if this is what you are looking for.

Difference between dates - with no holidays

Wed, 20 Oct 2010 15:18:39 GMT

Its a start, but I still need some changes:

The first one is that I need to set which days are Neto Work days. I also need to insert holidays.

The second thing is that i need to be able to calculate hours also.

Thanks,

Aldo.

I was trying:

let d1 = makedate(2010,1,1);

let d2 = makedate(2010,1,31);

let DiffDays = d2 - d1;

Let DiffWorkDays = NetWorkDays(d1, d2);

load

makedate(2010,1,1) as Date1,

makedate(2010,1,31) as Date2,

$(DiffDays) as GrossDiff,

$(DiffWorkDays) as NetoDiff

autogenerate 1;

Difference between dates - with no holidays

boorgura — Wed, 20 Oct 2010 16:02:09 GMT

you can include list out the holidays in the NetWorkDays() function.

Once you have the number of days - you can derive the number of hours - right?

Difference between dates - with no holidays

Wed, 20 Oct 2010 16:17:51 GMT

yes, but how do I tell to the function NetWorkDays() that muy working days are only Mondays and Wednesadys?

Difference between dates - with no holidays

johnw — Wed, 20 Oct 2010 19:34:41 GMT

You either list all Tuesdays, Thursdays and Fridays as holidays (impractical), or you don't use networkdays(). So... you need to know the number of Mondays and Wednesdays between two dates? With holidays removed?

I'd probably build a Workdays table with all the workdays you want to count in it. Then count({<Workday={">=YourFirstDate <=YourLastDate"}>} Workday). You'll likely need something more interesting in place of YourFirstDate and YourLastDate, but I'm not sure exactly how you're using this functionality, so I don't know what to put there.

Difference between dates - with no holidays

jonathandienst — Wed, 20 Oct 2010 19:49:14 GMT

Aldo

John's suggestion should work in your case, but if you want to look at NetwokDays in more detail, I have written a short series on working day calculations in my blog at http://yahqblog.blogspot.com/.

Jonathan

Difference between dates - with no holidays

Thu, 21 Oct 2010 08:13:48 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Script-syntax-_2D00_-Calculate-Neto-Working-Time-between-Date1-and-Date2.qvw]

Hi Guys,

Attached an example of what I am trying to achieve.

Thanks in advance,

Aldo.

Below an explanation:

What I need to achieve, is to calculate the number of working days between Date1 and Date2, so if

Date1= 07/10/2010 - Thursday - Working day

08/10/2010 - Friday - NOT Working day

09/10/2010 - Saturday- NOT Working day

10/10/2010 - Sunday

11/10/2010 - Monday - Holiday

12/10/2010 - Tuesday

13/10/2010 - Wednesday

14/10/2010 - Thursday

15/10/2010 - Friday- NOT Working day

16/10/2010 - Saturday- NOT Working day

17/10/2010 - Sunday

Date2= 18/10/2010 - Monday

And if I defined 11/10/2010 as Holiday, then I should get:

Neto Working Days = 6 Days.

Difference between dates - with no holidays

Sun, 24 Oct 2010 09:18:31 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/NetoWork3.qvw]

I used John's suggestion with IntervalMatch, and it seems to be working.

Thanks a lot,

Aldo.