It is often needed to calculate the number of days spent in each stage for an opportunity. This helps the management to identify bottlenecks and trends in sales. As an example, if opportunities are taking too much time on the Qualification stage, then management can take necessary steps to expedite this phase. In this blog we share an implementation of this feature using Flows.
In this sample implementation, we have taken three opportunity stages (Qualification, Proposal/Price Quote and Negotiation/Review). We want to calculate the number of days for each stage's duration.
For three Picklist values, we will have to create four fields in total – three Number fields (One number field each for one picklist value), one Date field, and one process builder to update all the duration fields.
Please follow the details mentioned below:
1. Create a custom Date field: Go to Setup >Object Manager > Opportunity > Fields, and relationship > For Data Type Select Date > Field Label: Time Stamp.
2. Create a custom Number field for the first picklist value (Qualification): Go to Setup > Object Manager > Opportunity > Fields, and relationship > For Data Type Select Number > Field Label: “Qualification Duration”(Decimal Places= 0). Follow this steps again for each picklist value.
3. Create a Process builder which will Update the “Time Stamp” field and “duration fields”, whenever a picklist value will be changed.
3.1 Go to Setup >In Quick Find, Search for Process Builder and click on it.
3.2 Click on New | Process Name “Update Time Stamp”| The process starts when: “A Record Changes”.
3.3 Click + to Add Object, Select Opportunity from the Object drop-down list.
3.4 To Start the process select when a record is created or edited | Click Save.
3.5 Click + Add Criteria.
3.6 Enter a name for this criteria node.- Qualification Time Stamp.
3.7 Criteria for Executing Actions: Formula evaluates to true.
3.8 Add a Formula: OR( AND( ISNEW(), ISPICKVAL([Opportunity].StageName,”Qualification”)) ,AND(ISCHANGED([Opportunity].StageName ),ISPICKVAL([Opportunity].StageName,” Qualification “)) ) | Click Save
3.9 In Immediate Action, Click + Add Action and Choose Update Record | Action Name “Qualification Time Stamp”| Record Type: Select the Opportunity record that started your process.
3.10 Set new Field Value: Field = “Time Stamp”, Type = “Formula”, Value = Today().
3.11 Click Use this formula and Save.
Note: Follow the same steps from 3.5 for other picklist values in the next nodes.
Please see the below images for reference.
Criteria Screen
Action image
4. Create new nodes under the above nodes in the process builder which will Update the “TimeStamp” field, whenever a picklist value will be changed.
Qualification Duration Criteria
Qualification Duration Action
4.1 Click on + to Add Criteria.
4.2 Enter a name for this criteria node Qualification Duration.
4.3 Criteria for Executing Actions: Formula evaluates to true.
4.4 Add a Formula: – AND (ISCHANGED ([Opportunity].StageName ),ISPICKVAL(PRIORVALUE([Opportunity].StageName),”Qualification”)) | Click Save.
4.5 In Immediate Action Click +Add Action and Choose Update Record | Action Name “Qualification Duration”| Record Type: Select the Opportunity record that started your process.
4.6 Set new Field Value: Field = “Qualification Duration”, Type = “Formula”, Value = IF(OR(ISNULL([Opportunity].Qualification_duration__c )[Opportunity].Qualification_Duration__c <= 0), Today()- PRIORVALUE([Opportunity].Time_stamp__c ), [Opportunity].Qualification_Duration__c +(Today()- PRIORVALUE([Opportunity].Time_stamp__c )))
4.7 Click Use this formula | do the same for the rest of the picklist values.
4.8 Once all the nodes are created, select “Evaluate the next criteria” in the “Specify What Happens After Evaluating This Criteria” section for all the nodes. Save and Activate the process.
Final process builder Nodes
5. The above process will calculate the number of days an opportunity remains in a particular stage. This is how the data will look like on the record Page.
Summary
Following the above process builder, we are able to calculate the duration of any picklist field. We will have to create a field for each picklist value we want to calculate duration and a field to have the time stamp value.