topic Re: how to take holidays into account to calculate remaining workdays in QlikView

how to take holidays into account to calculate remaining workdays

chriscools — Sat, 16 Nov 2013 14:02:59 GMT

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

Re: how to take holidays into account to calculate remaining workdays

swuehl — Sat, 16 Nov 2013 14:10:40 GMT

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.

Re: how to take holidays into account to calculate remaining workdays

Clever_Anjos — Sat, 16 Nov 2013 15:38:12 GMT

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

Re: how to take holidays into account to calculate remaining workdays

MK_QSL — Sat, 16 Nov 2013 18:26:04 GMT

Hi, Check my post on this...

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

Re: how to take holidays into account to calculate remaining workdays

Sun, 17 Nov 2013 06:49:08 GMT

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

Re: how to take holidays into account to calculate remaining workdays

chriscools — Sun, 17 Nov 2013 18:57:15 GMT

Thank you,

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

grtz,

chris