General OBIEE

Get Involved. Join the Conversation.

Topic

    Parthiban Vaithiyanathan
    get greatest of two dates in OTBI
    Topic posted January 31, 2019 by Parthiban Vaithiyanathan, tagged Analyses, OBIEE Answers, OTBI 
    124 Views, 6 Comments
    Title:
    get greatest of two dates in OTBI
    Content:

    Hi,

    I need to find greatest /least of two dates in OTBI using formula

    how can we do this in OTBI?

    Comment

     

    • Prudence Kruchten

      Hi - assuming that the two dates are the same field, then you would do MAX("FIELD").

      • Parthiban Vaithiyanathan

        Hi - dates are in two different field

        • Prudence Kruchten

          OK, try this then:

          CASE
          WHEN "FIELD1" > "FIELD2" THEN "FIELD1"
          WHEN "FIELD2" > "FIELD1" THEN "FIELD2"
          END

           

          • Christian Berg

            Wait where's the ELSE? What if they're the same? :-P

            • Prudence Kruchten

              You do not always need the ELSE on there, in this statement it would just return a blank cell.

              If you want it to return something for it being the same, you would do something like this:

              CASE
              WHEN "FIELD1" > "FIELD2" THEN "FIELD1"
              WHEN "FIELD2" > "FIELD1" THEN "FIELD2"
              ELSE 'SAME'
              END

              When I tested this, it errored because FIELD1 and FIELD2 are dates ... so I had to use code like this:

              CASE
              WHEN "FIELD1" > "FIELD2" THEN 'Field 1 name'
              WHEN "FIELD2" > "FIELD1" THEN 'Field 2 name'
              ELSE 'SAME'
              END

              I am not sure how to combine the text/date fields for it to work.

    • Wade Wilson

      Are the fields dates or timestamps? Either way, you should be able to use TIMESTAMPDIFF and some CASE statement. Pseudocode below:

      TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE),TIMESTAMPADD(SQL_TSI_DAY, 0, CURRENT_DATE))
      

      ...

      CASE when (above formula >= 0) then date on right of method ELSE date on left of method END

      Hope this helps.