Oracle Transactional Business Intelligence
Help with Formula: most recent offer
Summary
I need help creating a formula to pull in the most recent offer
Content
I'm pulling together a list of YTD offers. I am finding records where the candidate has accepted the offer then declined a few days later. I would like to the report to only show the most current offer. I can not use the "Current Step Name" as some of the candidates have moved to "Hire".
In the example below, I only want to see the Candidate Declined record
Any help would be greatly appreciated. Thanks in advance.
| Current Status | Req. Identifier | Submission Identifier | Historical Step Name | Historical Status Name | Submission Historical Status Start Date | Current Step Name |
| Sourcing | 1234567 | 9999999 | *Offer | Candidate Declined | 2-7-2018 3:32:40 PM | Hire |
| Sourcing | 1234567 | 9999999 | *Offer | Accepted | 2-5-2018 2:17:13 AM | Hire |
Answers
-
Hi Rosenerie - can you use the field 'Is Most Recent' as a filter?
-
I tried using "Is Most Recent" but it doesn't work as it is the same offer that has been accepted then declined - both records show "Yes". Thanks for the suggestion though.
-
Try editing the formula for historical status to select the max date.
Max("Submission CSW Status - Historical"."Submission Historical Status Start Date")
-
Hi,
Add the below filter condition to your report, this will display only the most recent status records based on the submission identifier.
MAX("Submission CSW Status - Historical"."Submission Historical Status Start Date" BY "Submission General Info"."Submission Identifier") ="Submission CSW Status - Historical"."Submission Historical Status Start Date"
Thanks
Nirmal
-
Thank you but it is only giving me the max date based for each historical status name. Thank you for the suggestion!!!
-
It worked! Thank you so much Nirmal!!!!
-
THANKS x2!