Oracle Analytics and AI

Hi Claudia,

This would go in your Filter. See my example below:

All comments

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

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.

I appreciate it if you could help me with this.

Thanks and regards!

Claudia

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

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

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.

Thanks and regards

Claudia

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

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.

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

Hi Claudia,

This would go in your Filter. See my example below: