Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

Help with Formula: most recent offer

Received Response
92
Views
7
Comments
Rosenerie Castillo
Rosenerie Castillo Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

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
Tagged:

Answers

  • Prudence K
    Prudence K Rank 5 - Community Champion

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

    2018-02-20_7-39-36.png

  • Rosenerie Castillo
    Rosenerie Castillo Rank 1 - Community Starter

    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
    Melissa-67834 Rank 1 - Community Starter

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

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

  • Nirmal-64675
    Nirmal-64675 Rank 2 - Community Beginner

    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

    Filter.JPG

  • Rosenerie Castillo
    Rosenerie Castillo Rank 1 - Community Starter

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

  • Rosenerie Castillo
    Rosenerie Castillo Rank 1 - Community Starter

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

  • Jonathan Chan gmg
    Jonathan Chan gmg Rank 5 - Community Champion

    THANKS x2!