Business day only logic

Question

Hello OTN board,

My version : Oracle Business Intelligence 12.2.1.3.0

Question: What is the easiest way to create a column in OBIEE analysis to use in graph to show business only days (excluding weekends and public holidays)?

I came across a site that had this formula in it, but im not sure if this is what I actually need and where this actually goes. Is there a way to pull over a standard date field and then put in a more simple formula?

The goal of my task is to create a graph with count of transaction ID's (that part I got) as the Y axis and the business days as the X axis.

example i found online:

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

Gianni Ceresa · Answer

The concept of weekends and public holidays is extremely subjective: for some weekend means Satuday-Sunday, for others Friday-Saturday. Public holiday is even worse as in a single country they could be different by region etc.

Therefore this meaning is 100% a metadata which needs to be part of your dimensions. To be more precise you need to have this into your time dimension, allowing you to have multiple meaning/version of it just like you would have multiple calendars etc.

There isn't a formula which can make it as it's a business rule.

Without saying that this kind of formula to be evaluated for every single row is a nightmare and the best way to have people complaining the analysis take minutes instead of seconds.