Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
I need to update my formula. not create an other dimension
Thanks
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).
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',.....