How to calculate the age of the stage field when it changes from one stage to another stage on the opportunity

Question

Can any one help me out .

I have a requirement where i need to calculate the age of each stage when their change from one stage to another stage .

For Example :

I have two stages say as Stage 1 --ABC ,Stage 2 -- XYZ . I Create a record named as opp stage 1 ,with stage1 -ABC on 15th JAN and now the other users changes the stage level from ABC to XYZ on say date as 20th JAN 2016.SO now i would like to calculate the age of the stage 1 ...how many days it stayed .Similarly when i change from stage 2 to stage 3 ...what is the duration of the stage2 stayed .

Any help very much appreciated.

Answer 1

Enable Opportunity Field History Tracking in Salesforce. Salesforce track changes natively so, you don't need build something to track changes. You can query Opportunity Stage changes as:

 List<OpportunityFieldHistory> oppHistory = [Select Id, CreatedDate,
 Field, NewValue, OldValue from OpportunityFieldHistory Where Field = 'StageName'];

This way you will have complete visibility to stage changes. You can build your logic over it.

If you want to query through Opportunity instead of OpportunityFieldHistory then query would be:-

List<Opportunity> oppList = [SELECT id,
        (SELECT StageName FROM OpportunityHistories) 
        FROM Opportunity];

List<OpportunityHistory> histories = oppList.OpportunityHistories;

Answer 2

If you want to track by your own just create a textArea field and store all information in that with the help of trigger. each value store formate in text Area

Stage1ABC#sd=12/12/2015#ed=2/03/2016#age=0,

sd is startDate,
ed is endDate,
age is the picklist val AGE,
'#' is the value separator
',' is Picklist value separator

Trigger for this implementation

trigger ageCalculator on TestObject__c (before insert, before update) {

    if(trigger.isInsert){
        for(TestObject__c obj : trigger.new){                        
                obj.txtAreaval__c = obj.Stages__c+'#sd='+system.now()+ '#ed='+' #'+'age=0,';                              
        }
    }
    if(trigger.isUpdate){
        for(TestObject__c obj : trigger.new){
                String oldpickListVal = trigger.oldMap.get(obj.id).Stages__c;

                string picklistvalDetail = oldpickListVal+''+obj.txtAreaval__c.substringBetween(oldpickListVal, ',');

                List<string> values = new List<string>();
                values = picklistvalDetail.split('#');                
                List<string> startDate = new List<string>();
                startDate = values[1].split('=');
                List<string> endDate = new List<string>();
                endDate = values[2].split('=');
                List<string> age = new List<string>();
                age = values[3].split('=');

                Integer fage = Integer.valueof(age[1]); 
                fage += date.valueof(datetime.valueof(startDate[1])).daysBetween(date.valueof(system.now()));
                string new_val = oldpickListVal+'#sd='+startDate[1]+ '#ed='+system.now()+' #'+'age='+fage ;                                  

                string finalString = obj.txtAreaval__c.replace(picklistvalDetail, new_val);           
                obj.txtAreaval__c = finalString ;                

                String pickListVal = obj.Stages__c;
                integer index = obj.txtAreaval__c.indexOf(pickListVal);

                if(index == -1){
                    obj.txtAreaval__c += pickListVal+'#sd='+system.now()+ '#ed='+' #'+'age=0,';                    
                }else{
                    picklistvalDetail = pickListVal+''+obj.txtAreaval__c.substringBetween(pickListVal, ',');

                    values = picklistvalDetail.split('#');

                    startDate = values[1].split('=');
                    endDate = values[2].split('=');
                    age = values[3].split('=');
                    fage = Integer.valueof(age[1]); 
                    fage += date.valueof(datetime.valueof(startDate[1])).daysBetween(date.valueof(datetime.valueof(endDate[1]))) ;
                    new_val = pickListVal+'#sd='+system.now()+ '#ed='+' #'+'age='+fage ;                  
                    finalString = obj.txtAreaval__c.replace(picklistvalDetail, new_val);           
                    obj.txtAreaval__c = finalString ;
                }
        }
     }   
}

Answer 3

Actually it is possible without trigger but for that you need to create 4 fields and 3 workflow :

Like :

1. create a boolean field say isUpdate
2. create a date field say previous value
3. create a date field say new value
4. create a formula(Number) field say time spend which is new_value__c - previous_value__c

and now create workflows :

1. workflow for record creation Evaluation Criteria : when a record is created and Rule Criteria : isUpdate= false and on this workflow action ......Field to Update : Opportunity: previous_value__c and checked the radio buttion Use a formula to set the new value and insert field CreatedDate.

2. workflow for record updation Evaluation Criteria : when a record is created, and every time it’s edited and Rule Criteria : ISCHANGED( isUpdate) and on this workflow action .... Field to Update : Opportunity: previous_value__c and checked the radio buttion Use a formula to set the new value and insert field new_value__c.

3. workflow for stage updation Evaluation Criteria : when a record is created, and every time it’s edited and Rule creteria : ISCHANGED( StageName) and on this there is 3 workflow action which is :

   1. Field to Update : Opportunity: new_value__c and checked the radio buttion Use a formula to set the new value and insert field LastModifiedDate.
   2. Field to Update : Opportunity: isUpdate and checked true in Checkbox Options .
   3. Field to Update : Opportunity: previous_value__c and checked the radio buttion Use a formula to set the new value and insert field new_value__c.

That's all :)