Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Help in finding difference of two dates

Received Response
52
Views
14
Comments
user11394485
user11394485 Rank 3 - Community Apprentice
edited Aug 13, 2024 7:08PM in Oracle Analytics Cloud and Server

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   OpenDate
Project1Gate115-Feb-16
Gate220-Feb-16
Project2Gate11-Mar-16
Gate210-Mar-16

}

Regards,

Lak

«1

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    user11394485 Rank 3 - Community Apprentice

    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 IMG_0427.JPGadvance.

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    Please check

    Your requirement

    Requirments.png

    Criteria Structure:

    Criteria.png

    Formulas by each column:

    ProjectName.png

    GateName.png

    OpenDate2.png

    Year1.png

    Year2.png

    SumYear.png

    SumYear2.png

    MetricCalculated.png

    Kind Regards,

  • user11394485
    user11394485 Rank 3 - Community Apprentice

    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
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

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

    suppres.png

    Kind Regards,

  • user11394485
    user11394485 Rank 3 - Community Apprentice

    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
    cesar.advincula.o Rank 6 - Analytics Lead

    Dont worry lak, the pleasure is mine.

    Kind Regards,

  • user11394485
    user11394485 Rank 3 - Community Apprentice

    Cesar,

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

    Please advice.

    Regards,

    Lak

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

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

    DATETIME.png

    Kind Regards,

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

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

    NQSConfig.png

    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,