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

rbeet88

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

Gianni Ceresa

Hi,

"Public holiday" already don't mean a lot as your public holiday are different than mine and each place has its own.

So you can easily imagine a tool, which job is to do analysis and reports in an agnostic way, has no idea of what a public holiday is.

This must come from your data, or of course you can add by hand hardcoding all the public holidays of the next 10 years to your formulas.

rbeet88

Hi Gianni,

Well yes public holidays are different per country, clearly, but it was more reference to whether we could use any exposed out-the-box date fields in OBIEE/OTBI (sourced from the Fusion ERP application) and use them in an Answers formula. 

Hard coding public holidays over the next n years in a BI Answers formula would not be very efficient so I don't really see that as a viable option.

Thanks,

Richard

Thomas Dodds

Properly formed calendar and date dimensions is what you need ... then you can do Gregorian, fiscal, holiday (by nation/culture), marketing, etc ...

When doing analytics and BI it's the FIRST thing built ... EVERYTHING after built after that relies on properly formed calendars and dates.  We live in TIME and SPACE ...

rbeet88

Hi Thomas,

Thanks for your comments.

Agreed, so the approach is finding that calendar in Fusion and forming the calendar appropriately in the application.

The challenge is getting that calendar mapped into our OTBI subject areas, from a SaaS environment, where we have no access to an RPD.

Thanks,

Richard