topic Remove weekend days in QlikView

Remove weekend days

Mon, 30 May 2011 15:59:10 GMT

Hi Guys,

I have two days in a table..

For ex:

TableX:

Load

PlannedGoodsIssueDate,

ActualGoodsIssueDate

From X;

From this table I need to find the delay in Service Performance. That means the

Difference = ActualGoodsIssueDate - PlannedGoodsIssueDate

This is easy. Now my problem..

I need to remove the weekends from the dates which are between ActualGoodsIssueDate and PlannedGoodsIssueDate because they are not counted.

Any Idea how to get that?

Thanks

Sravan

Remove weekend days

pat_agen — Mon, 30 May 2011 16:02:43 GMT

hi,

have you tried the function Networkdays()?

from the help

networkdays ( start:date, end_date {, holiday} )
Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:
networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

hope this helps

Remove weekend days

Mon, 30 May 2011 16:06:40 GMT

Hi Pat,

Networkdays gives the No of Holidays but it does not help in my case. When I am subtracting the dates, I need to find whether it is a weekend day and ignore if it is saturday or sunday..

I hope you understand what I am after...

Remove weekend days

nagaiank — Mon, 30 May 2011 16:29:15 GMT

networkdays ( start_date, end_date {, holiday} )

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

If you want to check if your start_date and/or end_date falls on a weekend and want to use previous or later workday, you may try the functions firstworkdate and/or lastworkdate with your dates.

e.g. networkdays(firstworkdate(start_date,1) - lastworkdate(end_date,1))

Hope this helps.

Remove weekend days

pat_agen — Tue, 31 May 2011 07:13:59 GMT

hi,

networkdays definitely looks like the answer. Check this example:

Remove weekend days

Tue, 31 May 2011 10:35:23 GMT

Thanks Pat and Krishna

Hi Pat,

Thanks for the answer. I was not clear. sorry for that..

What I am trying to do is.

I have two Dates like this..

I need now to remove the Dates which are Holidays or weekends, when calculating the differences.

For example.

05-05-2011(DD.MM.YYYY) is the PlannedGoodsIssueDate and 09-05-2011 is the ActualGoodsIssueDate

it means, the difference is only 2 days(06-05-2011 and 09-05-2011) as 08.05.2011 and 09.05.2011 are Saturday and Sunday.

It shows 3 Days when implemented.

Am I understanding something wrong??

Regards

Sravan

Remove weekend days

pat_agen — Tue, 31 May 2011 10:48:54 GMT

hi Sravan,

taking your example you want to exclude the first day if I understand correctly. For example if the Planned GoodsIssue and the ActualGoodsIssue happen on the same day then the answer you want is zero. networkdays() on its own would show thsi as 1.

Can't you simply then subtract 1 from the answer delivered by sung the function?

=networkdays(date( PlannedGoodsIssueDate),date(ActualGoodsIssueDate))-1

how does that look?