Refer Opportunity stage table there you will find on what date the stage changed. so that will solve your problem but you may need to do some work around.
You will have to create two custom fields for each stage in the stage field.For ,example,One field will be starting date of stage field and another field with the closing date.Now,you can calulate the avearge date of a particular date and same for other stage field,It will only be possble with the trigger.
Starting date of the stage field will be the current date and when you move to the next stage,the date on the which you moved to the next stage will give you the closing date.
I know it is possible becuse one of my friends has done this sucessfully.
I know this is one of the easy way to achieve this, but if i follow this approach i cant calculate the values for already existing opportunities in various stages.
Try using object "OpportunityFieldHistory " to get old and new values of opportunity field. Use created date field of this object to calculate time difference between two stages.
Query : SELECT CreatedDate,Field,Id,NewValue,OldValue,OpportunityId FROM OpportunityFieldHistory where OpportunityId ='XXXXXX'
you can get the number of days between created date and the closed date, so from that you can calculate average age.
hey thanks for your reply,
That gives me average sales cycle. But i want calculate average opportuniy duration in each stage.
Can you help me ?
Thanks,
S.Sivakumar
Refer Opportunity stage table there you will find on what date the stage changed. so that will solve your problem but you may need to do some work around.
You will have to create two custom fields for each stage in the stage field.For ,example,One field will be starting date of stage field and another field with the closing date.Now,you can calulate the avearge date of a particular date and same for other stage field,It will only be possble with the trigger.
Starting date of the stage field will be the current date and when you move to the next stage,the date on the which you moved to the next stage will give you the closing date.
I know it is possible becuse one of my friends has done this sucessfully.
I know this is one of the easy way to achieve this, but if i follow this approach i cant calculate the values for already existing opportunities in various stages.
Regards,
S.Sivakumar
Hi,
Try using object "OpportunityFieldHistory " to get old and new values of opportunity field. Use created date field of this object to calculate time difference between two stages.
Query : SELECT CreatedDate,Field,Id,NewValue,OldValue,OpportunityId FROM OpportunityFieldHistory where OpportunityId ='XXXXXX'