Business Object desktop intelligence Date formula

[boboleft]

I am using Business Object desktop intelligence and trying to come up with a formula to count days in between two dates. I have two date fields say start date and end date.I need to find out the days between these two dates excluding Weekends(saturday and sunday's). Any help would be appreciated.Thank you again

 

[boboleft]

Does anybody have any ideas about this??

 

[blom0344]

Yes, I do. In the past we simply added a special calender file with an index on the individual days, making sure the index incremented with value 1 for each working day (and no increment for weekends/regular holidays) This way we only need to fetch the index on the dates and apply a simple subtraction to the index values.

This way we could not only account for weekends , but also for those non-working days through the year (on fixed dates as well as shifting days; easter for instance)

Ties Blom

 

[skrandel]

Ties is right. It's virtually impossible to do this with a formula in the report.

Steve Krandel
Intuit

 

[boboleft]

Hi Ties, Steve

Thanks for the help..I am new to BO Deski. Can you provide the steps for creating the calendar file??

thanks

 

[blom0344]

Excel:

1.
Create continous range of dates in A column
2.
B1 as:
=IF(OR(WEEKDAY(A1;2) = 6;WEEKDAY(A1;2)=7);0;1)
3. AS:
=SUM(B$1:B1)

Drag definitions down the columns B and C to create the calculations for the entire date range..

Ties Blom

 

[boboleft]

Hi Ties,

Thanks for the response. I am a little bit confused..I need to create a calendar file in excel instead in BO desktop intelligence??

thanks

 

[blom0344]

No. You can create the file in Excel, then arrange to upload it to the database to create a table which you can use as a time dimension in your universe.

Alternative is to look for the many procedures which are already build to create calendertables.

This is all pretty well doable when you are working with MS SQL server, it will require assistance from a DBA when you are on Oracle / other RDBMS

Which database are you reporting against?

Ties Blom