OTBI: Find the number of Working/Business Days between two dates and exclude Public Holidays

rbeet88

Summary

OTBI: Requirement to find the number of Working/Business Days between two dates, therefore the calculation must exclude days that fall on Weekends or Public Holidays

Content

Hi All, 

My client is an Oracle Fusion SaaS customer and are currently developing a number of reports in OTBI. 

They have a requirement to find the number of Working/Business Days between two dates, therefore the calculation must exclude days that fall on Weekends or Public Holidays. 

I have two logical OBIEE formulas that exclude Weekends, both of which seem to be working fine, and I have pasted them below. The big issue we've got is excluding Public Holidays from this calculation. 

Is there any way of doing this in OTBI for Fusion Cloud customers?

Are there any custom calendars we can create in Fusion and map into our OTBI subject areas?

Formula 1 to exclude weekends:

-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 5, "- General Information"."Invoice Entered Date"))-1),TIMESTAMPADD(SQL_TSI_DAY, 5, "- General Information"."Invoice Entered Date")), TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Payables Payments - Payment History Real Time"."- Payment Information"."Check Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Payables Payments - Payment History Real Time"."- Payment Information"."Check Date")))/7*5+ MOD(7-DAYOFWEEK("Payables Payments - Payment History Real Time"."- Payment Information"."Check Date"),6)+CASE WHEN DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 5, "- General Information"."Invoice Entered Date"))-2>5 THEN 5 ELSE DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 5, "- General Information"."Invoice Entered Date"))-2 END

Formula 2 to exclude weekends:

CASE WHEN DAYOFWEEK("Submission Dates"."Submission Created Date") > DAYOFWEEK("Submission Dates"."Offer Accepted Date") THEN CASE WHEN DAYOFWEEK("Submission Dates"."Submission Created Date") = 7 THEN TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", "Submission Dates"."Offer Accepted Date") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", "Submission Dates"."Offer Accepted Date")/7)*2)-1 ELSE TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", "Submission Dates"."Offer Accepted Date") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", "Submission Dates"."Offer Accepted Date")/7)*2)-2 END ELSE TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", "Submission Dates"."Offer Accepted Date") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", "Submission Dates"."Offer Accepted Date")/7)*2) END

Happy days for excluding weekends... but how can we account for and exclude Public Holidays too?

Thanks, 

Richard

Rick Brobbel

Hi,

I have had a similar request some time ago, but I haven't found an answer in any formula.

You would need a table in the database with those dates.

They can vary per country or continent.

I had even a bigger challenge when workshops had varying opening hours (such as closed on Mondays).

I had to use the Work Day Calendar table from the source ERP-system (in my case Oracle JD Edwards) to service this.

Kind regards

Rick

rbeet88

Hi Rick, 

Thanks for your comments. 

In your case, did you end up building a report in BI Publisher that pointed directly at the physical table in JDE or were you able to model (or map) the Work Day Calendar table into your OBI/OTBI subject area? 

Within what tool did you end-up building your analyses?

Thanks, 

Richard

Girishan

Similar to what Rick pointed out, public holidays can vary by state and sometimes even by county. Some of these holidays are "floating holidays" . You would be better of having a flag in the calendar table indicating if the day is a weekend and/or another flag indicating if its a public holiday, rather than trying to get this count done using a formula.

Thanks,

Giri.

rbeet88

Thanks Giri, another sensible suggestion and I agree it gets very messy trying to code it, but since we have no real autonomy over the physical tables in SaaS (other than enabling various flexfields) the question lies in where/how we can create this flag against a calendar table.

In the HCM subject areas of OTBI, for example, the Time attributes point largely to the FND_CAL_DATE table, which does not provision for the inclusion of any DFFs. So in this case, where would a custom Public Holiday flag go and how would it get populated through Fusion?

Can we create custom calendars in Fusion and enable them for OTBI?

Tempted to open a separate thread for that last point. 

Thanks,  

Richard

Venu Kumar

Try below fusion tables of Public Holidays

PER_CALENDAR_EVENTS

PER_CALENDAR_EVENTS_TL

PER_CAL_EVENT_COVERAGE

PER_GEO_TREE_NODE

HR_LOCATIONS_ALL_f

Amrita Gupta-Oracle

Hi All,

A related query. I understand Public Holidays can be setup by adding an event on the Holiday Calendar page.

Is this information available in any PVO?

Regards

Amrita.

RichardChan

Do you have the option of bringing in your own data storing the dates of the holidays that you can then join to the query to exclude any dates in that dataset?