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

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,