nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Help with Formula: most recent offer

Rosenerie Castillo

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

Find more posts tagged with

Reporting

SQL

Answers

Report Output

Analyses

Accepted answers

All comments

Prudence K

Hi Rosenerie - can you use the field 'Is Most Recent' as a filter?

Rosenerie Castillo

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.

Melissa-67834

Try editing the formula for historical status to select the max date.

Max("Submission CSW Status - Historical"."Submission Historical Status Start Date")

Nirmal-64675

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

http://www.dataterrain.com

Rosenerie Castillo

Thank you but it is only giving me the max date based for each historical status name. Thank you for the suggestion!!!

Rosenerie Castillo

It worked! Thank you so much Nirmal!!!!

Jonathan Chan gmg

THANKS x2!