Hi, Could you please help in finding the difference between two Gate dates as new column in OBIEE.They have been separated as shown below. Thanks in advance. { Project1Gate NameGate 1 Open Date Gate2 OpenDateProject1Gate115-Feb-16Gate220-Feb-16Project2Gate11-Mar-16Gate210-Mar-16 } Regards, Lak

Hi, Is there any way to limit the result to one row in 'MetricCalculated' column for each project. Cesar,I have tried your approach to eliminate the duplicates but didn't go well. Regards, Laxman

Hello, maybe is not the efficent in the time of response, however the first advice solve the requeriment, this requirement its a little tricky because we have to deal with two rows, to get a metric, as a the best answer I also have the idea to push it with ETL or ELT process. Kind Regards,

I didn't say a word ;-) But in all honesty I'd also push this to the DB so that you only ever use a result in OBI. Either by your one-row-approach or by doing the calc in ETL/ELT and then use that physical result.

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 Analytics and AI

Child Item

Help in finding difference of two dates

user11394485

Hi,

Could you please help in finding the difference between two Gate dates as new column in OBIEE.They have been separated as shown below.

Thanks in advance.

{

Project1	Gate Name	Gate 1 Open Date	Gate2 OpenDate
Project1	Gate1	15-Feb-16
	Gate2		20-Feb-16
Project2	Gate1	1-Mar-16
	Gate2		10-Mar-16

}

Regards,

Lak

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

to do this in Answers is very inefficient ... you'd be better served having this implemented in the database (use of your information drives your design). however,

In answers criteria tab ... use the Project column, the date column (filtered by gate1), the date column (filtered by gate2), then ensuring you have real date/timestamps you can easily get the difference:

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

intervals are:

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR

user11394485

Thanks for the prompt responce.I understood how to calucate the date difference using TimestampDiff but not exactly how to filter these columns.

I am attaching the screenshot how the data appears

Thanks in advance.

cesar.advincula.o

Hello,

Please check

Your requirement

Criteria Structure:

Formulas by each column:

Kind Regards,

user11394485

Hi cesar,

Thank you so much for your time.

I will try your logic on my actual data and let you know the results.

BTW, is there any way to remove the duplicates in 'MetricCalculated' column.

Regards,

Lak

cesar.advincula.o

Hello,

Please try with this., CAST your VALUE as CHAR and apply.

Kind Regards,

user11394485

Thank you very much cesar.

Basically ,I am from PLM background and bit new to this OBIEE.

I will try all your inputs and let you know the results.

Regards,

Lak

cesar.advincula.o

Dont worry lak, the pleasure is mine.

Kind Regards,

user11394485

Cesar,

Can we achieve the same result through LEAD/LAG functions along with TimestampDiff fucntion.

Please advice.

Regards,

Lak

cesar.advincula.o

Hello,

Sorry but LAG/LEAD is not support DATE or DATETIME.

Kind Regards,

cesar.advincula.o

Hello,

Another option, its to set up, your EVAUATE_SUPPORT_LEVEL in you NQSConfig.ini file

Develop an database function, and implement your LEAD/LAG, and managed whatever calculation that you want, Oracle BI Answers, has a little restricts.

Despite of this, in the first advice that i gave you, do you want to considered, hours, minute and second? if the answers is yes, you could get also this.

Hope this help.

Kind Regards,