Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to get the last degree if the worker have 2 records in a Analysis and Report
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
Best Answer
-
Hi Claudia,
This would go in your Filter. See my example below:
1
Answers
-
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")
0 -
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
0 -
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
1 -
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
0 -
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.
0 -
0
-
You are the best! Thank you so much for your help! :)
That works perfectly!
Thank you and regards!
Claudia
0
