how to take holidays into account to calculate remaining workdays

[chriscools]

Hello,

i have a formula to calculate the remaining working days for the current month:

NetWorkDays(   today(2), MonthEnd(Today()))

Is it possible to take holydays, company closing days etcetera into account for the next 12 months?

thanx!

Chris

[swuehl · Accepted solution]

Sure, just add the list of holidays as additional arguments to Networkdays function.

Here is an example from the HELP:

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

There are also examples here in the forum on how to read the holidays in as a table and create the holiday-argument-list from that using a variable.

[Clever_Anjos]

Holydays:
LOAD * INLINE [
    F1
    12/05/2013
    01/01/2014
];


LOAD
  chr(39) & Concat(F1,chr(39)&','&chr(39)) & chr(39) as List
Resident Holydays;  


LET vList=Peek('List');

Now you can use vList variable as parameter to NetWorkDays(StartDate,EndDate,$(vList))

[MK_QSL]

Hi, Check my post on this...

http://community.qlik.com/docs/DOC-5314

[#5]

Hi  ,

First use this Set statement in your Script

Set DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Then please try the code as below

If you consider saturday as Holiday then use 'Sat' in below exp

=Round((MonthEnd (Today()) - MonthStart(Today()) ) )    -

(Floor((MonthEnd (Today()) - MonthStart(Today()) )/7 ) +

if( num( WeekDay(MonthStart(Today())))+1<= Match('Sat', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) and

Match('Sat', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) <=num( WeekDay(MonthEnd(Today())))+1 ,1,0))

Here i used current month i.e today() , you can replace by the date field for every month.

Please let me know if there is anything

Regards

Yusuf

[chriscools]

Thank you,

i tried your answer, it seems the easiest way to solve this!

grtz,

chris