nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

get greatest of two dates in OTBI

Parthiban Vaithiyanathan-86705

Content

Hi,

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

how can we do this in OTBI?

Find more posts tagged with

Answers

OTBI

Analyses

Comments

Prudence K

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

Parthiban Vaithiyanathan-86705

Hi - dates are in two different field

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.

Prudence K

OK, try this then:

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

[Deleted User]

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

Prudence K

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.

New Announcements

New Events

Analytics - Fusion Data Intelligence: Semantic Model Extensions in FDI: Concepts, Use Cases, and Architecture
Learn how Fusion Data Intelligence supports customization and extensibility across content, data, semantic modeling, and security. This session explains why customers extend FDI, how the FDI architecture enables governed customization, and how to choose among OAC self-service, Data Augmentation, Semantic Model Extensions,…
Analytics - Fusion Data Intelligence: CEAL Guidance Office Hours | Americas and Europe
Agenda Presentation: Salesforce Connector. Unlock the full value of the Salesforce CRM Connector with out-of-the-box subject areas that deliver immediate business insights, and discover how easily they can be extended to meet your unique reporting and analytics needs. Through "art of the possible" demonstrations, explore…
AIDP - Building an AI-Ready Enterprise (Europe and Middle East Time Zones)
Description: Join Oracle and ISG Research to explore what the ISG Buyers Guide™ reveals about the requirements behind successful enterprise AI deployments. ISG will share key findings on the trends shaping buyer priorities, and Oracle will show how Oracle AI Data Platform helps organizations bring AI closer to trusted…