0

I need to calculate the time an Opportunity has been in each stage it moved to. NOTE: The Opportunity History report will NOT suffice as it does not allow for pulling in related record data (basically not extendable) and it does not have the current stage duration or beginning.

I went the route of custom fields. I created a date field for each stage value that gets populated by a flow with the date the stage changes to that specific value.

I then needed to create formula/number fields for each stage to determine the number of days it was in that stage (if it was). I have the formula working for when the stage moves to the next/immediate stage but I'm not sure how to account for when the previous stage was changed to a value later in the sales cycle.

IE: If my stages go in this order: Identify>Qualify>Propose>Feasibility>Compile Quote>Closed (Won/Lost) and an Opportunity gets updated from Qualify to Feasibility, basically skipping a stage, I'm not sure how to write the formula to accommodate that as well.

This is what I currently have:

IF(
    ISPICKVAL(StageName, "Feasibility"), 
    TODAY() - Date_set_to_Feasibility__c, 
    Date_set_to_Feasibility__c - Date_Set_to_Propose__c
)

Anyone have any suggestions on how to get this?

1 Answer 1

2

While it is technically possible to do this with formulas, I think you're really getting into "you should do this in Apex" territory.

To completely handle all scenarios, you'd need to be able to go back to every previous stage in case all of the stages between "Identify" and your target stage were skipped (and maybe even back to the record's CreatedDate if the stage can initially be null).

e.g.

IF( 
    ISPICKVAL(StageName, "Feasibility"), 
    TODAY() - Date_set_to_Feasibility__c, 
    Date_Set_to_Feasibility - IF(
        NOT(ISBLANK(Date_Set_to_Propose__c)),
        Date_Set_to_Propose__c,
        IF(
            NOT(ISBLANK(Date_Set_to_Qualify__c)),
            Date_Set_to_Qualify__c,
            /* and so on... */
        )
    )
)

Or perhaps we could exploit BLANKVALUE()

IF(
    ISPICKVAL(StageName, "Feasibility"), 
    TODAY() - Date_set_to_Feasibility__c, 
    Date_Set_to_Feasibility -
        BLANKVALUE(Date_Set_to_Propose__c,
            BLANKVALUE(Date_Set_to_Qualify__c,
               /* and so on... */
            )
        )
)

but either way, you have a lot of repetition, and it'd be a small nightmare to change if stages are added/removed.

In Apex, this would look something like

// Query the OpportunityFieldHistory to get the stagename changes
Map<Id, Opportunity> oppsWithStageHistory = new Map<Id, Opportunity>(
    [SELECT Id, CreatedDate, 
        (SELECT Id, CreatedDate, Field, NewValue, OldValue FROM OpportunityFieldHistories WHERE Field = 'StageName' ORDER BY CreatedDate DESC) 
    FROM Opportunity 
    WHERE OpportunityId IN :oppIdList]
);

// Iterate over the opps, and find the date difference between this stage change
//   and the previous stage change
for(Opportunity opp : oppsWithStageHistory.values()) {
    Long dateDifference;
    List<OpportunityFieldHistory> stageHistories = oppsWithStageHistory.get(opp.Id).OpportunityFieldHistories;

    // No stage changes = no difference, and we can continue to
    //   the next opp
    if(stageHistories.size() == 0) { continue; }

    OpportunityFieldHistory mostRecentStageChange = stageHistories[0].CreatedDate;

    switch on stageHistories.size() {
        // 1 stage change = use the Opp's CreatedDate
        when 1 {
            dateDifference = mostRecentStageChange.CreatedDate - opp.CreatedDate;
        }
        // everything else, use the two most recent stage changes
        when else {
            dateDifference = mostRecentStageChange.CreatedDate - stageHistories[1].CreatedDate;
        }
    }

    // We need to use the old stagename to determine which field to update
    String targetField = switch on mostRecentStageChange.oldValue {
        when 'Identify' { 'Time_In_Identify__c' }
        when 'Qualify' { 'Time_In_Qualify__c' }
        /* and so on... */
        when else { null }
    };

    if(targetField != null){
        opp.put(targetField, dateDifference);
    }
}

// And then some DML to update the data (or perhaps not, if it's a before update trigger)

The Apex approach looks longer at first glance, but if you compare it to all of the formulas you'd have to write, I'd bet it'd end up being shorter.
The other important things to note are:

  • You wouldn't need that platoon of fields to track stage change times (nor the flow(s) that populates them)
  • Repetition is kept to a minimum
  • You only have one file to look at/change when stages are added/removed
  • Stage order doesn't really matter

The above Apex isn't tested, and isn't 100% complete, but it should get you/someone else headed in the right direction.

One gotcha with this Apex is that we need to write unit tests to deploy Apex to a production org, and things involving field history are notoriously difficult to test. Creating the Apex in a way that allows you to say "only run this query if we didn't inject data ourselves" is the way around that issue.

1
  • Thanks @Derek They actually decided not to allow the users to go BACK in the stages so that helps at least. I know its not the best way to go by not using APEX but unfortunately there are no developers to write it originally or maintain.
    – user7417
    Commented Apr 14 at 19:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .