Business Intelligence

Get Involved. Join the Conversation.

Topic

    Jose Casquero
    add working days to a DateAnswered
    Topic posted July 1, 2019 by Jose CasqueroGreen Ribbon: 100+ Points, last edited July 1, 2019, tagged Business Intelligence, OBI 
    124 Views, 11 Comments
    Title:
    add working days to a Date
    Content:

    Hi there

    i need to calculate final dates as result of adding working dates to previous dates in OBIEE

    is there any way how to do this in OBIEE when defining a new variable (date data type) using a formula in the edit column formula of the variable?

     

    Regards

     

    Version:
    Oracle® Business Intelligence Enterprise Edition 11g Release 1 (11.1.1)

    Best Comment

    Doug Ross

    You can probably start with a formula like this.. which adds 3 working days to a date column:

    case DAYOFWEEK("Dim - Period"."Start Date")  when 1 then  TIMESTAMPADD(SQL_TSI_DAY, 3, "Dim - Period"."Start Date") when 2 then  TIMESTAMPADD(SQL_TSI_DAY, 3, "Dim - Period"."Start Date")  when 3 then  TIMESTAMPADD(SQL_TSI_DAY, 3, "Dim - Period"."Start Date")  when 4 then  TIMESTAMPADD(SQL_TSI_DAY, 5, "Dim - Period"."Start Date")  when 5 then  TIMESTAMPADD(SQL_TSI_DAY, 5, "Dim - Period"."Start Date")  when 6 then  TIMESTAMPADD(SQL_TSI_DAY, 5, "Dim - Period"."Start Date")  when 7 then  TIMESTAMPADD(SQL_TSI_DAY, 4, "Dim - Period"."Start Date")   else  "Dim - Period"."Start Date" end

    Each line is uses a different value to add to the date column depending on which day of week it is.   You would have to modify this to match however many days you want to add... or there may be a way to make it more generic with additional coding. 

    Comment

     

    • Doug Ross

      Can you define "working date"?   For example, are you saying that adding 1 day to a date this is on a Friday, you want to skip Saturday and Sunday and return Monday's date?

      • Doug Ross

        And I am assuming you don't need to account for holidays?  Otherwise that becomes a more difficult process that would likely involve loading a table with working dates.

    • Jose Casquero

      apologies, as working days, i mean weekdays (monday to friday)

      Doug, you are right , i just want to exclude weekends when adding days to a date

    • Doug Ross

      You can probably start with a formula like this.. which adds 3 working days to a date column:

      case DAYOFWEEK("Dim - Period"."Start Date")  when 1 then  TIMESTAMPADD(SQL_TSI_DAY, 3, "Dim - Period"."Start Date") when 2 then  TIMESTAMPADD(SQL_TSI_DAY, 3, "Dim - Period"."Start Date")  when 3 then  TIMESTAMPADD(SQL_TSI_DAY, 3, "Dim - Period"."Start Date")  when 4 then  TIMESTAMPADD(SQL_TSI_DAY, 5, "Dim - Period"."Start Date")  when 5 then  TIMESTAMPADD(SQL_TSI_DAY, 5, "Dim - Period"."Start Date")  when 6 then  TIMESTAMPADD(SQL_TSI_DAY, 5, "Dim - Period"."Start Date")  when 7 then  TIMESTAMPADD(SQL_TSI_DAY, 4, "Dim - Period"."Start Date")   else  "Dim - Period"."Start Date" end

      Each line is uses a different value to add to the date column depending on which day of week it is.   You would have to modify this to match however many days you want to add... or there may be a way to make it more generic with additional coding. 

    • Jose Casquero

      i just reviewed your formula -> everything makes sense but not the last two statements. should be like this?

       

      when 6 then  TIMESTAMPADD(SQL_TSI_DAY,4, "Dim - Period"."Start Date")  when 7 then  TIMESTAMPADD(SQL_TSI_DAY, 3, "Dim - Period"."Start Date")   else  "Dim - Period"."Start Date" end

      • Doug Ross

        So what it is saying is if the date is a Friday (dayofweek value 6) then to add 3 workings days, you would add 5 to skip Sat, Sun and give the result of the next Wednesday.  If it is Saturday (7) , then add 4 to skip Sunday and return Wednesday also.  So Friday, Saturday, and Sunday all return Wednesday if you add 3 days.

    • Jose Casquero

      oh i see. i did not know sunday is day 1 dayoftheweek (in OBI).  i would expect sunday is 7 but now i get you

       

      • Christian Berg

        DAYOFTHEWEEK is actually sensitive to the server's configuration adn this will change depending on your locale settings. It's not an absolute "in OBI".

        Also you can never assume that all regions using your system will use the same calendar - neither for the first day of the week nor for the "work days".

        The best and most correct solution is to use a correctly formed time dimension which holds - in detail -. all information about work days, weekends and days off etc.

        A hack with formula can never ever do this for you. Also, if you use code that whole logic will have to be parse for every single result row every single time you query. It's extremely inefficient and costly.

    • Doug Ross

      "A hack with formula can never ever do this for you. Also, if you use code that whole logic will have to be parse for every single result row every single time you query. It's extremely inefficient and costly."

      Thanks for labeling my suggestion a hack.  I find it amusing that using built-in OBIEE functions n a straightforward manner would fall in the "hack" category.   The view from up on your high horse must be amazing. 

      As for extremely inefficient and costly - compare it to building a proper time dimension from scratch and integrating that into the model.  Not everyone is running OBIEE on multi-terabyte tables with users spanning many countries.   This solution will likely meet the requirements of many OBIEE implementations.   And it can be done at the report level without requiring any further developer involvement, RPD migration, or regression testing.    Why don't we let the person who posed the question determine if it is inefficient or not.   

       

      • Gianni Ceresa

        Building a proper time dimension takes about 30 minutes including the query to create and populate the table in the database. Let’s say 1h if you have many tables to join it to and many models.

        Using built-in functions doesn’t exclude by default something as being an hack. You seem to have a negative view of what a hack is, so let’s call it a quick-win shortcut waiting to be fixed by proper modeling. After all OBIEE is all about modeling things. As for the quality of the generated query, have a look at it and you will feel the difference even with just few thousands rows.

        Obviously the OP is the only one judging what kind of solutions he wants in his system, he will be the one having to live with it, not me or you or Christian. A sign of the future issues coming from that “hack” is just what happened above: the OP reading the formula and directly not finding it naturally logic as Sunday for many isn’t the first day of the week. We can assume most of the users finding that formula in front of them in an analysis will have the same reaction. By experience, in OBIEE, somebody will pay the price for big pieces of logic in the front end vs. RPD, time will say who it is....

         

        • Doug Ross
          That is great if you work in an environment where you edit the RPD in online mode and deploy to production. Most of the customers I have worked for have change control processes that take days or weeks to move an RPD through development to test to prod. So the 30 minute fix to the RPD sometimes takes a week to see the light of day. It might be possible that the original poster does not have access to the database or the RPD. Saying to just add a table to a production warehouse in 30 minutes is not my experience. Yours may be different. My response was just to the general tone of the back response. Everyone isn't an expert in OBIEE and acting condescendingly to people simply trying to help might scare off others from responding.