General OBIEE

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Rosenerie Castillo
    Help with Formula: most recent offerAnswered
    Topic posted February 20, 2018 by Rosenerie CastilloGreen Ribbon: 100+ Points, tagged Analyses, OBI Answers, OBIEE Answers, Report Output, Reports, SQL 
    91 Views, 6 Comments
    Title:
    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

    Best Comment

    Nirmal

    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

    Comment

     

    • Prudence Kruchten

      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

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

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

    • Nirmal

      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

     

    community forum