Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

Tenure calculation in OTBI

Received Response
351
Views
8
Comments
Abhishek Bagh
Abhishek Bagh Rank 4 - Community Specialist
in Oracle Transactional Business Intelligence

Summary:

Used Current_Date function & Enterprise Seniority Date field to calculate Tenure (in a separate field) however the result is not accurate (its showing value zero for some colleagues). Can someone help me understand what additional criteria do I need to use to calculate it correctly please

Content (required):


Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):

Tagged:

Answers

  • Bhaskar Konar
    Bhaskar Konar Rank 6 - Analytics Lead

    Hi Abhishek,

    Would you give some more details like what Tenure you are looking to calculate here? Perhaps you can attach your report xml / catalog if that's better.

    Cheers,

  • Abhishek Bagh
    Abhishek Bagh Rank 4 - Community Specialist

    Hi @Bhaskar Konar

    Thanks for your response.

    Below shared is the calculation i tried - using CURRENT DATE function & Seniority Date field (to calculate in time tenure).

    Hope below snap helps understand about my query better.

    Regards,

    Abhishek

  • Bhaskar Konar
    Bhaskar Konar Rank 6 - Analytics Lead

    Hi @Abhishek Bagh ,

    The formulas are really interesting, thanks for sharing.

    Would you mind using formula like below?

    Values will be on Float:

    CAST(TIMESTAMPDIFF(SQL_TSI_DAY, "Worker"."Enterprise Seniority Date", CURRENT_DATE) AS FLOAT) / 365

    without decimal:

    TIMESTAMPDIFF(SQL_TSI_DAY, "Worker"."Enterprise Seniority Date", CURRENT_DATE) / 365



    Hope this help.

    Cheers,

  • Abhishek Bagh
    Abhishek Bagh Rank 4 - Community Specialist

    Hi @Bhaskar Konar

    This worked - thanks for your kind assistance!

    Cheers!

    Abhishek

  • Bhaskar Konar
    Bhaskar Konar Rank 6 - Analytics Lead

    Abhishek Bagh,

    Glad it worked for you.

    Would you mind selecting the best answer that resolved your issue so that it'll help fellow community members in future?

    Cheers,

  • Abhishek Bagh
    Abhishek Bagh Rank 4 - Community Specialist

    Hi @Bhaskar Konar

    In my case, below one worked quite well. Thanks again!

    CAST(TIMESTAMPDIFF(SQL_TSI_DAY, "Worker"."Enterprise Seniority Date", CURRENT_DATE) AS FLOAT) / 365

    Regards/ Abhishek

  • Charla Blakely
    Charla Blakely Rank 1 - Community Starter

    Hi - Is there a formula that would provide Years of Service for current year? All the formula's above supply information based on current date and I am in need of for entire year.

  • Juliet Eaton
    Juliet Eaton Rank 1 - Community Starter

    Hi @Abhishek Bagh - thank you for the explanation! I also used the formula above to calculate tenure and it worked. I used Enterprise Hire Date rather than Enterprise Seniority Date since I wanted years of service since employee started with the company.

    Thanks again.