Networkdays

building reports webintelligence
1

Hi,

Do we have any function that excludes (saturday and sunday) between two dates.
In excel we use networkdays() function to derive the result. What function we have to use for above scenario in WEBI? :hb:

Thanks
Sriraman

srijackson (BOB member since 2009-07-29)

2

Hi,

There is not such function in WebI.

You either use a calendar table, if you have anything like that in your database. Or use quite complicated formula described in this blog:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Hi,

One of the member mention the below formula

=Floor((DaysBetween([start_date];[end_date]))-Truncate((DayNumberOfWeek([start_date])+DaysBetween([start_date];[end_date])) /7 ;0)*2)+1

i tested with few date range and it works fine.

any justification on this formula will be more appreciated.

Thanks,
Sriraman

srijackson (BOB member since 2009-07-29)

4

Genius…!!! This is working perfectly. :+1: :+1:

Also I test the Dave’s solution and found it working fine. But I feel it gave me 3 days less than the actual. The formula I used is -

=Truncate(DaysBetween([Approved Min Date]; [Received Date]) / 7 ; 0) * 5

Now I have dates like -
Approved Min Date = 6/7/06
Received Date = 4/17/06 , Now the result should come 38, but by using above formula I got 35 and when I used the first formula , I got the correct results.

I guess I am not using Dave’s formula perfectly or I am making some silly mistake.

aniketp :uk: (BOB member since 2007-10-05)

5

This seems to work well bu I’m not sure why the Floor function is used ?

The first days between will give you a whole integer and the truncate bit in the latter part of the formula will give you a whole number so it basically whole number A - whole number B + 1 so the Floor bit is overkill as it is trying to round a whole number down to the nearest integer which will be the same number eg 9 rounded down is 9.

Otherwise good.

Nniixx :australia: (BOB member since 2009-09-02)

6

you may want to validate the results for the days start and end on the weekend of the same week, spanning from Sat to Sat, Sun and Sun to Sat, Sun of 1 week and many week. Based on my experience, I realised there are many test cases, which can be easily missed.

vigi_guna :australia: (BOB member since 2008-07-09)

7

Hi,

I believe u did not apply the dave concept completely

=((Truncate(DaysBetween( ‘4/17/2006’;‘6/7/2006’) / 7 ; 0) * 5) + ToNumber(Substr(“1234555123444512333451222345111234500123450123455”; ((DayNumberOfWeek(‘4/17/2006’)-1)*7)+Mod(DaysBetween(‘4/17/2006’;‘6/7/2006’);7)+1 ; 1)))

if u try the above formula u will get the exact result - 38
.
The above formula which will exclude weekends.
can anyone help me to exclude holiday too. ?

Thanks,
Sriraman

srijackson (BOB member since 2009-07-29)

8

If you want to also exclude holidays, you will probabaly need universe calendar objects.

REB01 :us: (BOB member since 2004-11-29)

9

Holidays differ from country to country, maybe even from company to company.

So you definitely need to use a calendar table (where your holidays are marked/flagged) to exclude holidays from the calculation.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

10

Thanks for your reply

how to apply holiday flag where holiday varies from state to state.

e.g for state1 26th jan will be holiday but state 2 it may not.

instead of attaching flag at time dimension, can i create seprate table which holds state_code and holiday_flag?

Thanks
Sriraman

srijackson (BOB member since 2009-07-29)

11

that’s a different forumla to the one you suggested earlier

No wonder there is confusion

Nniixx :australia: (BOB member since 2009-09-02)

12

Hi Dave,

We had a request to exclude only Sunday, I have used Dave’s logic and implemented, it is working absolutely fine. Now the business is requesting to have the Turnover Time in actual hours/days Date Time
Actual Time Utilized
Start Date Friday 7/27/12 3:30 PM 8:30
Saturday 7/28/12 12:00 AM 24:00
Sunday 7/29/12 12:00 AM 00:00
End Date Monday 7/30/12 10:30 AM 10:30
Total Time 43:00

If I use dave’s the formula i get 3 working days (Excluding Sunday). However the actual time utilized is 43 hours which 1.79 day i.e. 2 days approx. I tried few things, but nothing is working out. Is there a way to get this.

shashi_07 (BOB member since 2009-02-11)