Networkdays function to change non working days from saturday-sunday to thursday-friday

[#1]

Hi Folks,

I have a requirement where we need to calculate the no of days between start date and end data.

We can use the

networkdays( start:date, end_date {, holiday} )

but this function automatically excludes saturday and sunday.

What i need is to exclude Thurday and Friday as non working days and include the saturday and sunday as working day while doing the calculation.

Our client working days are Sat-Wed  and Thu-Fri as weekOff.

So how can i use an logic to calculate the exact no of days.

Please advise how to achieve it.

Your suggestion is much appreciate.

Regards

[MayilVahanan]

Hi

Try like this,

Load *, if( WeekDay(DateField) = 'Thu' or WeekDay(DateField) = 'Fri', 0, 1) as Flag from tabkename;

Then Use count(Flag) as expression. You can achieve your requirement..

Hope it helps

[swuehl · Accepted solution]

If you only have dates for start and end date, you will first need to somehow create a date record for each day in your period. If you could calculate the networkdays in the script, you can do it like this:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41160,41162';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

9/1/12 11:08 AM,9/8/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM  

];

// Create a record per day in your periods

TMP:

LOAD  ID,

Date(daystart(DT1)+iterno()-1) as Date

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

// calculate the sum of days in your periods, that are not a Thu or Fri, or part of your Holidays (set variable vHol)

left join (INPUT) LOAD

ID,

count(Date) as Networkdays

Resident TMP where (WeekDay(Date)<3 or WeekDay(Date)>4) and not match(Date,$(vHol)) group by ID;

drop table TMP;

The Weekday() function returns 3 for Thursday and 4 for a Friday.

Hope this helps,

Stefan

[#4]

Hi Stefan,

Thanks for the promp response to my query.

The solution provided by you was easy to understand and implement.

Regards,

Vasim

[wizardo]

Hi,

as i see it it works but it creates raws for each day in between the dates in each row of the source table.

if my source table is big and in avg i have 5-10 days diff in each row it means ill need to create a table 5-10 times larger.

this might be a problem

any other ways?

i need networkdays function to give sunday-thursday as working days (excluding friday and saterday)

Wizardo

[marcus_sommer]

Maybe you could use an approach like this:

count(distinct if(match(weekday(Date), 0,1,2,3,6), Date))

- Marcus

[wizardo]

yes ofcourse thats what i did

but that doesnt solve the problem of the large table case

[marcus_sommer]

It was meant as calculation within the gui. Further you didn't need create flags or calculations over the fact-table - better is to use a master-calendar.

- Marcus

[wizardo]

what will the master-calendar help here?

i have a fact with 2 date in each row and i need to calculate the working days between these dates.

i see no way to use the master-calendar in this scenario

normally id use the function NETWORKDAYS() but this functions assumes week where Monday is first day of the week and something that can work with weeks where Sunday is first day and Friday and Saturday are non working days.

and

swuehl

solution was the best i have found so far

(except the issue with large fact table about which i asked)

Wizardo

[marcus_sommer]

I don't know what do you want to do with these networkdays-values. If you don't calculate these values further within the script it could be better to perform such calculation on a small amount of data (restricted by selections) within the gui.

Nevertheless you could create an expression with a main-logic like this:

(enddate - startdate) - floor((enddate - startdate) / 7)

with an additional considering from the weekday from the startdate and enddate and a little bit fine-tuning.

I have built such expression some time ago but actually couldn't find it. Good luck.

- Marcus