topic Create function to calculate Neto Work Days in QlikView

Create function to calculate Neto Work Days

Sat, 23 Oct 2010 19:01:54 GMT

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

Hi Guys,

Please see the attachment below.

What I need to achieve, is to calculate the number of working days between Date1 and Date2, as detailed below.

Since I did not find a way to solve it throw tables, I am trying with VB Script.

It is almost done, but having a problem with function NetoWork.

Thanks in advance,

Aldo.

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.

Create function to calculate Neto Work Days

pover — Sat, 23 Oct 2010 19:46:38 GMT

Aldo,

I hadn't realized you could create custom functions in VB for the script. I'll have to think about what I can do with that.

However, wouldn't your problem be better resolved with a chart expression like the following after modifying a little the data model to make a table with all the possible dates between the ranges linking that table with the work day and holiday table?

count(distinct if(Date>=Date1 and Date<=Date2
and IsWorkDay
and IsHoliday<>0,Date))

I did the attached example on the chart side, but with a some more work you can do the same on the script side with intervalmatch, joins and/or mappings instead of using a custom function.

Regards.

Create function to calculate Neto Work Days

Sat, 23 Oct 2010 20:36:07 GMT

Hi Karl,

Something is wrong with the file. Could you upload it again, please?

Thanks,

Aldo.

Create function to calculate Neto Work Days

pover — Sat, 23 Oct 2010 21:07:40 GMT

Try it again. What error does it send you? Failed to Load?

Regards.

Create function to calculate Neto Work Days

Sun, 24 Oct 2010 08:47:39 GMT

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

I tried to work it around using interval match, but having problems to set the "Count" expression..

How do I solve it?

Thanks,

Aldo.

Create function to calculate Neto Work Days

Sun, 24 Oct 2010 09:11:14 GMT

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

Okay! I have it working!!

Is there any simpler and faster way to do it?

Thanks a lot.

Aldo.