Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Remove weekend days

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

Tags (3)
1 Solution

Accepted Solutions
pat_agen
Valued Contributor

Remove weekend days

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?

6 Replies
pat_agen
Valued Contributor

Remove weekend days


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

Not applicable

Remove weekend days

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...

nagaiank
Valued Contributor III

Remove weekend days

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.

pat_agen
Valued Contributor

Remove weekend days

hi,

networkdays definitely looks like the answer. Check this example:

networkdays.png

Not applicable

Remove weekend days

Thanks Pat and Krishna

Hi Pat,

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

What I am trying to do is.

GIDates.jpg

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

pat_agen
Valued Contributor

Remove weekend days

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?

Community Browser