How to get the last degree if the worker have 2 records in a Analysis and Report — Oracle Analytics

Oracle Transactional Business Intelligence

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

How to get the last degree if the worker have 2 records in a Analysis and Report

Accepted answer
24
Views
8
Comments

Hi @Everyone,

How do we get the last degree if the worker has 2 or more records in an Analysis and Report? We need to get the last degree considering their last start date.

We need a report to get the last degree if the employee has 2 or more records in an Analysis and Report.
Example: In the image, if we have 3 records, we want to get the last degree considering their start date or end date.

Thanks and Regards

Claudia

Career_image.PNG

Best Answer

Answers

  • Richard Kemp-Eyre
    Richard Kemp-Eyre Rank 5 - Community Champion

    Hi Claudia,

    Try using a RANK BY PersonNumber on the start date field then filter to select the First Ranked row.

    However, this will not work if you have two qualifications with the same start date should you have these type of scenarios

    I use the RANK By to select the latest DBS Check recorded in Document of Records as shown in the code snippet below:

    RANK("Workforce Management - Documents of Record Real Time"."Document of Record Details"."PER_DOCUMENTS_OF_RECORD_DFF_RENEWAL_DATE_" BY "Worker"."Person Number")

    image.png
  • Claudia Linares L
    Claudia Linares L Rank 3 - Community Apprentice

    Hi @Richard Kemp-Eyre

    Thank you for this suggestion, but I apologize for the confusion. I am trying to get the degree only in the last field of this report. Not the document record of this degree.
    Example: In the image, if the employee has 3 degrees, we want to get the last degree considering their start date or end date. The report should show only the Master's Degree for ID 020526. Because is the most recent degree that employees have in the system.

    max2.PNG

    I appreciate it if you could help me with this.

    Thanks and regards!

    Claudia

  • Richard Kemp-Eyre
    Richard Kemp-Eyre Rank 5 - Community Champion
    edited Oct 18, 2024 8:36AM

    Hi Claudia,

    This will work for you if you apply the rank formula to your fields. I was giving an example of how I use it with Document of Records. So in your case, you would add a second column for Degree Start Date, then edit the Formula to something like :

    RANK("Workforce Profiles - Person Profile Real Time"."- Degrees"."Start Date" BY "Worker"."Person Number")

    Check that this gives a 1 against the degree with the most recent Start Date. If it does then add this column to your Filter and select 'is equal to' 1. When you run your report it should then only give you one line for each employee showing their most recent degree.

    Hope that helps.

    Richard

  • Claudia Linares L
    Claudia Linares L Rank 3 - Community Apprentice

    Hi @Richard Kemp-Eyre

    Thank you so much! That works great! I appreciate your help!

    Rankformula.PNG

    Only I have to ask the workers to complete the Start date because if it is blank it does not appear on the report marked with any number in the column.

    WithoutStartDate.PNG

    Thanks and regards

    Claudia

  • Richard Kemp-Eyre
    Richard Kemp-Eyre Rank 5 - Community Champion

    Hi Claudia,

    Glad it worked. Getting data out of our employees for things like Qualification Start Date is always an issue as sometimes they can't remember and leave it blank. You may want to add an OR statement in your filter, so something like:

    RANK("Workforce Profiles - Person Profile Real Time"."- Degrees"."Start Date" BY "Worker"."Person Number") = 1

    OR

    RANK("Workforce Profiles - Person Profile Real Time"."- Degrees"."Start Date" BY "Worker"."Person Number")

    IS NULL

    Alternatively add a :

    IFNULL("Workforce Profiles - Person Profile Real Time"."- Degrees"."Start Date",

    "Worker"."Enterprise Hire Date"")

    though this may give you two or more lines if the employee has not given any start dates for their qualifications.

    Richard.

  • Claudia Linares L
    Claudia Linares L Rank 3 - Community Apprentice

    Hi @Richard Kemp-Eyre

    It is showing an error message, maybe I am using the wrong formula?

    errormessageF.PNG
  • Claudia Linares L
    Claudia Linares L Rank 3 - Community Apprentice

    Hi @Richard Kemp-Eyre

    You are the best! Thank you so much for your help! :)

    That works perfectly!

    FormulaCond.PNG

    Thank you and regards!

    Claudia