General OBIEE

Get Involved. Join the Conversation.

Topic

    Richard Beet
    OTBI: Find the number of Working/Business Days between two...
    Topic posted July 19, 2017 by Richard BeetRed Ribbon: 250+ Points, last edited July 19, 2017, tagged Analyses, Fusion, OBI Answers, OBIEE Answers, OTBI, Reports 
    1199 Views, 4 Comments
    Title:
    OTBI: Find the number of Working/Business Days between two dates and exclude Public Holidays
    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

    Comment

     

    • 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

      • Richard Beet

        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 Shanmugam

      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.

      • Richard Beet

        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