Oracle Communities

Oracle Analytics and AI

Child Item

Tenure calculation in OTBI

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):

Find more posts tagged with

OTBI

Analyses

FDI

Accepted answers

All comments

Bhaskar Konar

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

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

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

Hi @Bhaskar Konar

This worked - thanks for your kind assistance!

Cheers!

Abhishek

Bhaskar Konar

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

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

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

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.

User_Z0M1T

Hello, how do we calculate the length of service for rehires, to include their total time with the organization?

Bhaskar Konar

Hi @User_Z0M1T,

Welcome to the Oracle Analytics & AI Community!

There are 2 dates like Enterprise Hire Date and Work Relationship Start Date. In case of Re-Hire both the dates will not be same and Enterprise Hire Date should provide the initial hire date.

So if it's a Re-Hire you can use the Enterprise Hire Date instead of Work Relationship Start Date.

Hope this help.

Thank you.