topic excluding holidays and weekends in QlikView

excluding holidays and weekends

master_student — Fri, 29 Jul 2016 11:29:54 GMT

Hello guys,

Hope you're enjoying the summer

A question, I need to not count the weekends in the formula below :

if( match( date( Today(), 'M/D/YYYY'), date( [ROW_LASTMANT_DTTM] , 'M/D/YYYY') ) , 0 ,(NetWorkDays([ROW_ADDED_DTTM], Today()-1, $(vHol))) )

How can I do it?

$(vHol) = '','','','1/1/2013 12:00:00 AM','1/1/2014 12:00:00 AM','1/1/2015 12:00:00 AM', '1/1/2016 12:00:00 AM', '1/1/2017 12:00:00 AM', '1/14/2014 12:00:00 AM','1/14/2013 12:00:00 AM','1/14/2015 12:00:00 AM','1/14/2016 12:00:00 AM', '10/15/2013 12:00:00 AM','10/15/2014 12:00:00 AM','10/15/2015 12:00:00 AM', '10/15/2016 12:00:00 AM', '10/15/2017 12:00:00 AM', '3/20/2013 12:00:00 AM','3/20/2014 12:00:00 AM','3/20/2015 12:00:00 AM', '3/20/2016 12:00:00 AM', '3/20/2017 12:00:00 AM', '4/9/2013 12:00:00 AM','4/9/2014 12:00:00 AM','4/9/2015 12:00:00 AM', '4/9/2016 12:00:00 AM', '4/9/2017 12:00:00 AM', '5/1/2013 12:00:00 AM','5/1/2014 12:00:00 AM','5/1/2015 12:00:00 AM', '5/1/2016 12:00:00 AM', '5/1/2017 12:00:00 AM', '7/25/2013 12:00:00 AM','7/25/2014 12:00:00 AM','7/25/2015 12:00:00 AM', '7/25/2016 12:00:00 AM', '7/25/2017 12:00:00 AM', '8/13/2013 12:00:00 AM','8/13/2014 12:00:00 AM','8/13/2015 12:00:00 AM','8/13/2016 12:00:00 AM', '8/13/201712:00:00 AM', '5/1/2013 12:00:00 AM','5/1/2014 12:00:00 AM','5/1/2015 12:00:00 AM', '5/1/2016 12:00:00 AM', '5/1/2017 12:00:00 AM', '7/5/2016 12:00:00 AM', '7/6/2016 12:00:00 AM', '7/7/2016 12:00:00 AM'

Thanks

Re: excluding holidays and weekends

beck_bakytbek — Fri, 29 Jul 2016 11:32:14 GMT

hi master,

try this (only working days)

let vStartDay = date('01/01/2010');
let vEndDate = date('31/12/2016');

for x = vStartDay to vEndDate

tempCalendar:
load $(x) as Date AutoGenerate(1);

next x

NoConcatenate

Working_Calendar:
Load Date, weekday(Date) as WeekDay, week(Date) as Week, Month(Date) as Month, Year(Date) as Year, floor(month(Date)/3)+1 as Qtr

resident tempCalendar

where WeekDay(Date) <> 'Sat' and WeekDay(Date) <> 'Sun';

drop table tempCalendar;

i hope that helps

beck

Re: excluding holidays and weekends

master_student — Fri, 29 Jul 2016 11:36:41 GMT

I need to update my formula. not create an other dimension

Thanks

Re: excluding holidays and weekends

trdandamudi — Fri, 29 Jul 2016 12:02:18 GMT

I think the formula you have is correct. By default the NetWorkDays when calculating will exclude the weekend dates.

"Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays."

if( match( date( Today(), 'M/D/YYYY'), date( [ROW_LASTMANT_DTTM] , 'M/D/YYYY') ) , 0 ,(NetWorkDays([ROW_ADDED_DTTM], Today()-1, $(vHol))) )

So, in your formula the weekends are excluded between [ROW_ADDED_DTTM] and Today()-1 along with holidays $(vHol).

Re: excluding holidays and weekends

sunny_talwar — Sun, 31 Jul 2016 19:11:25 GMT

Is there a reason you are using time stamps, instead of dates? Why not like this?

$(vHol) = '1/1/2013','1/1/2014','1/1/2015', '1/1/2016',.....