BusinessObjects Board

Need to return Business Days in Formula

building reports webintelligence legacy 1/2
#1

I have two date fields: EstDateofDeparture and EstDateofArrival.

My problem is when EstDateofDeparture falls on a Friday then EstDateofArrival shows on a Saturday and we don’t deliver on Saturdays.
We do have departures on Saturdays but no pickups on Sats and sundays.

So I need the EstDateofArrival to be the following business day (Monday).
Here’s an example below:

EstDateofDeparture: Friday, Dec 7th, 2007
EstDateofArrival: Saturday, Dec 8th, 2007 (it needs to be Monday, Dec 10th, 2007 – next business day).

Example # 2:

EstDateofDeparture: Saturday, Dec 8th, 2007
EstDateofArrival: Sunday, Dec 9th, 2007 (it needs to be Monday, Dec 10th, 2007 – next business day).

T-SQL has a functionalilty where you could for example add getdate()-1 or getdate()+2.

But I can’t find anything in webi formula editor that shows me how to I can do that.

Is there a function for this??

thx,

John

latingntlman (BOB member since 2007-12-21)

#2

This is the code you would use in a DeskI variable:

=If DayNumberOfWeek(<Departure Date>)=5 Then <Departure Date>+3 Else  If DayNumberOfWeek(<Departure Date>)=6 Then <Departure Date>+2 Else <Departure Date>+1

Presumably, (because my server is down at the moment so I can’t check :roll_eyes:), the WebI equivalent would be something like:

=If(DayNumberOfWeek([Departure Date])=5;[Departure Date]+3;If(DayNumberOfWeek([Departure Date])=6;[Departure Date]+2;[Departure Date]+1))

Steve

steveayres :uk: (BOB member since 2006-11-23)

#3

Thanks for the help, but that syntax didn’t work. After I researched it, I learned that there’s a function [RelativeDate] which returns a date plus any increment (whole number) which works.

i.e. =RelativeDate(“01/01/2008”;2) would return “01/03/2008”

John

:smiley:

latingntlman (BOB member since 2007-12-21)

#4

You could also create these in your universe as objects, Oracle syntax…

Tablename.Field.Name+getdate()-1
Tablename.Field.Name+getdate()-2

Mak 1 :uk: (BOB member since 2005-01-06)