Ask Search:
Lauren MullinsLauren Mullins 

Report on stage duration

The report I am trying to create needs to show my opportunities that are currently in evaluation stage and above while showing how many days the opportunity has spent in evaluation stage and above.  It seems as though when trying to report historically, the report will not take the current stage into account and is showing old opportunities that are irrelevant to the reporting needs.  Can anyone help??
Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
Try adding:
Stage Change  [equals]  TRUE  
to your Report Filter criteria.  
 

All Answers

Steve MolisSteve Molis
 What kind of report are you using for your source Report?  Are you using an Opportunity History Report or a standard Opportunity Report?  
An Opportunity History Report should be able to get you that.  What is the selection/filter criteria that you are using in your report? 
Lauren MullinsLauren Mullins
I'm using the Opportunity History Report with the filters on "To Stage" to include only the 3 stages I'm interested in seeing the duration of.  One of the problems that I'm having here is that it shows every tiny change throughout the opportunity stage.  For example, if we change the probabilty it shows From Stage as Evaluation and To Stage as Evaluation and how many days it was there.  I really just want to see TOTAL number of days in Evaluation stage.  Does that make sense? 
Steve MolisSteve Molis
Try adding:
Stage Change  [equals]  TRUE  
to your Report Filter criteria.  
 

This was selected as the best answer
Lauren MullinsLauren Mullins
That's what I needed!  Thank you sooooo much!
Steve MolisSteve Molis
 No problem, that's why they pay me the Biiiiig Bucks!  ;-)
Lauren MullinsLauren Mullins
SteveMo, one last question on this.  I sent the report to my CEO and he pointed out that the stage duration is not calculating correctly.  For example on one account it shows that it changed stages on 10/6/2009 (and is still currently in that stage) but the stage duration is 25 days?  Is there a way to correct that?  Thanks in advance for your help.  I hope one day I will be savvy in this reporting like you seem to be!
Steve MolisSteve Molis
Can you post a screen shot of the Opportunity Stage History related list and the same opportunity as it appears in your Stage Duration report?  




Lauren MullinsLauren Mullins
- User-added image
Lauren MullinsLauren Mullins
Wow...that turned out small.  Can you even see that?
Steve MolisSteve Molis
 Yikes!  my eyes are good, but they ain't THAT good...  
Can you try posting it as a link using either Screencast [ http://www.screencast.com/ ] or SlideShare [ http://www.slideshare.net/Salesforce ]
I used Screencast for mine. 
Lauren MullinsLauren Mullins
http://www.screencast.com/t/MzQ2ZjE3ZTE

Let's see if this works!  I hope I did that right.
Steve MolisSteve Molis
 Maybe I don't understand what your boss wants the report to display, but to me the report looks correct.  
According to the Opportunity Stage History in your screenshot the Opportunity was:

Active: 12/15/08 - 9/11/09 = 270 days
Champion: 9/11/09 - 10/06/09 = 25 days
Evaluation: 10/06/09 - 1/04/10 = 90 days
Proposal: 1/04/10 - Present


Lauren MullinsLauren Mullins
Yes, you are right.  The only thing missing is that the report doesn't seem to be taking into account the duration of the proposal stage thus far.  For example Proposal: 1/04/10 - Present should be added to the 385 total as well.
Steve MolisSteve Molis
Ugh!  you're right, looks like there's a loophole in the Opportunity History Report in that you cannot display the Duration of the Current Stage, or get a handle to the Opportunity Age.  

If you really need to have this I might have something that will work for you (but it ain't gonna be pretty).
Lauren MullinsLauren Mullins
Uh Oh.......we do need it.  So tell me what you've got.

Keep in mind, I'm very new to the SalesForce Admin role.  Went to training a couple of weeks ago which was useless when talking about organization specific reporting.  :)
Steve MolisSteve Molis
Okay, first thing.  Which edition of SFDC is your organization using?  In order to use WorkFlow Rules with Field Updates you need to be on Enterprise or Unlimited Edition.
 
Lauren MullinsLauren Mullins
We are on Enterprise Edition
Steve MolisSteve Molis
As Sgt. Horvath said in Saving Private Ryan "We're in business boys!"  

Okay there's a couple of different things we need to do here:  
We need to create a couple of custom fields on the Opportunity.  One of them is going to be a Date, this is gonna hold the value of the Date that the Opportunity Stage was changed to it's current Stage.   Next we're gonna need a Formula(Number,0) field to hold our Current Stage Duration.  We're gonna calculate this using a Formula:
TODAY() - Stage_Change_Date__c  
Next we're gonna need a WorkFlowRule with a Field Update that will trigger when the Opportunity Stage is changed and populate our Stage Change Date field with the result of this formula:
TODAY()
Oh yeah, set the Field-Level Security Settings for both custom field to Visible and Read Only for all User Profiles on your SFDC.org (just in case any of your users start felling frisky)


okay, now the bad news...  this will only work going forwards, it cannot calculate the Current Stage Duration for opportunities that have been sitting at their current stage for NN Days.  

But fear not...  you've got the Force.com Data Loader!  With that you can either export an Opportunity History report to a CSV file, or extract your Opportunity History table to a CSV file.  Get the Date of the most recent Opportunity Stage change, and upload that Date using the DataLoader to populate the Stage Change Date on your current Opportunities.  

Does that sound okay to you?  Do you need any help with any of it?     


  
 
Lauren MullinsLauren Mullins
Ohhhh Lord.....let me try it and see what happens.   Stand by!  HAHA
Lauren MullinsLauren Mullins
Ok, all is done except....., I have ran an opportunity history report with the Opportunity Name,"From Stage","To Stage","Stage Change","Stage Duration","Last Modified", exported that as a CSV file.  This is my first experience with DataLoader so I'm not quite sure how to get the most recent "last modified" date into the "stage change date". 
Steve MolisSteve Molis
No problem, would it be easier if you just emailed me directly? (stevemolis@gmail.com)  It's not that it's too too difficult, but there's lot of minutiae to explain between the DataLoader, MS-Excel.
Steve MolisSteve Molis
 Okay, create a report that includes the Opportunity ID, Stage Change, To Stage, Last Modified Date, Account Name, Opportunity Name (for QC purposes).  
Export the report to a CSV File.  
Open the CSV File and sort it by Opportunity ID then by Last Modified Date (newest to oldest).  
Filter the file by Opportunity ID (unique) and save it to another spreadsheet (this is the file you're gonna upload)  
Now fire up the DataLoader and update your Opportunity records by inserting the Last Modified Date into the custom "Stage Change Date" field on the Opportunity object.  
From this point on your WFR and Field Update should take care of all Opportunity Stage Changes. 

Steve MolisSteve Molis
Hi @OlenaP  can you post a screenshot of your WFR configuration?  Or copy&paste the WFR Formula that you are using?
Olena PazderskaOlena Pazderska
Did you find any other gaps with Opportunity History report besides the fact that it does not calculate the current stage duration?

It seemed to me that the history report double counts some stages.  Any additional thoughts on gaps?
Steve MolisSteve Molis
I'm sorry @OlenaP I'm not sure I follow you.  Are you having a specific problem implementing the solution that I posted?  Are you trying to do something different?
Olena PazderskaOlena Pazderska
Hello @Steve Mo

WFR figured out - can only use ISCHANGED function with every time created or edited rule

Regarding what I am trying to do - I am looking at displaying report that would show the duration of Opportunity Stages to see if Opportunities linger at any stages/skip any

That's where I found this post and it looks like you are talking about the downsides of the native Opportunity History report - not having ability to report on duration of the current stage. Is there any other gaps that you know in this report as it pertains to reporting on Opportunity Stages? Thanks so much !
Chris NalewayChris Naleway
I'm going to follow up with some screen shots. I'm still something here still :(

We need to create a couple of custom fields on the Opportunity.  One of them is going to be a Date, this is gonna hold the value of the Date that the Opportunity Stage was changed to it's current Stage.   Next we're gonna need a Formula(Number,0) field to hold our Current Stage Duration.  We're gonna calculate this using a Formula:
TODAY() - Stage_Change_Date__c  
Next we're gonna need a WorkFlowRule with a Field Update that will trigger when the Opportunity Stage is changed and populate our Stage Change Date field with the result of this formula:
TODAY()
Oh yeah, set the Field-Level Security Settings for both custom field to Visible and Read Only for all User Profiles on your SFDC.org (just in case any of your users start felling frisky)
Chris NalewayChris Naleway
Figured it out. I needed ISCHANGED (STAGENAME)
Reuven ShelefReuven Shelef
@SteveMo @OlenaP

Seems like the Stage History Report counts Stage Duration only for Stage changes that occur within the timeframe of the current CloseDate. Are you familiar with this behavior? Do you know if this is correct or I'm missing something?

Thanks,
Reuven.
Olena PazderskaOlena Pazderska
@Reuven Shelef hm not sure I am following. Can you tell me more about the report you are running and what are you seeing?
Lisa SnowLisa Snow
I believe I am looking for a similar report. I have read several articles regarding this and am even more confused. I need to measure our velocity from stage to stage. We have a workflow that when completed, will update the stage accordingly. The issue I am seeing and from I am reading above is that the stage duration is not calculating correctly. The numbers get wonky when for example a user goes in and makes changes to the close date for example (and possible does this several times). I would really like to see an example of one working and what I need to create to get this up and running.

*I tried Opportunity History Report and matrix. 

Thanks,

Lisa
Lynne RichardsonLynne Richardson
I am also in need of creating a Stage Duration field to show on our Opportunity layout, but I'm not so good with formulas and workflows.  Can anyone provide screenshots for creating the custom date fields and workflows on this?  Seems that so many people really need this.  If only Salesforce would add the Stage Duration field to the opportunity layout, that'd be so easy.  I'm concerned that people have said the stage duration isn't calculating correctly.  When I try creating an Opportunity Field History Report, I can't seem to eliminate the same opportunities from populating several times since it's tracking the history of every edit made, and I only want it to track the changes to Stage History. So confused.
Lisa SnowLisa Snow
It is very confusing! I've read several posts regarding this. When I create a Opportunity history report, it's not picking up all my stages (we have 9)
1. Qualified Suspect, 2. Qualified Sponsor, 3. Qualified Power Sponsor, 4. Decision Due, 5. Pending Sale, 6. Won, 7,8,9 happen after the sales is closed and don't need to be measured. I need to be able to track by stage the number of days and then possibly the average.

I need to figure out how long an OPP sits in each stage. So far I'm not finding a way of calculating this. Also noticed the age no going to 0 after the OPP is won. This needs to be more clear what the difference between AGE and Stage Duration. I really need help with trying to build out a report that covers 
Bernie MachtBernie Macht
As of now (I don't know when it came available) there is a field called Stage Duration in the standard report type Opportunities.  It measures how long the opp has been in its current stage.  The Age field measures from the create date to Today or the Close Date, whichever is first.  I haven't worked with the Stage Duration field much, yet, but in my first foray with it last week it seemed to be reporting quite well.
Alex SherwoodAlex Sherwood
There is alternative solution to creating a report which captures both the Stage Duration History and the Stage Duration for the currently selected stage, without creating custom fields.

All you need to do is created a joined report using the Opportunity History and Opportunity reports (others may work too but I haven't tested them). Then use a custom formula to add together the stage duration (sum) from each block, to calculate the Total Duration.

The only limitation of this report is that if there is no value for the Opportunity History duration, the custom formula won't return the Opportunity duration only and returns a blank value instead (if know of a way to resolve this please let me know!).
I've dealt with this by displaying both the custom formula value and the Opportunity duration summarised as sum in the report. Then if there's no value for the Total Duration, I can refer to the value in the Opportunity column instead.

User-added image
Lynne RichardsonLynne Richardson
Thank you! Lynne Richardson office 847 960 1167 > mobile 617 480 8399 www.sunergosllc.com
Alex SherwoodAlex Sherwood
CORRECTION - You must apply the filter Stage Change = True to the Opportunity History report. Otherwise the stage duration for any fields which have been modified (not just the Stage field) will be included in the Stage Duration.
Chelsea HillisChelsea Hillis
I am having sort of this same issue too but I don't see Stage Change as something to add as a filter.
Alex SherwoodAlex Sherwood
Hi Chelsea, you definitely will as long as you've selected the correct report type (Opportunity History not Opportunity Field History or Opportunity). That filter's available in my developer org so it's available as standard.
Chelsea HillisChelsea Hillis
Yup! I was on the wrong one, I was on opportunity field history. Thank you!
Smriti SaxenaSmriti Saxena
Hey! Can I create such a report of a grand child of Opportunity along with activity. It has a similar stage change and I need to know which record was stuck on which stage for how long? 
Chelsea HillisChelsea Hillis
For grand child of an Opportunity? Do you mean an Object you made in Salesforce? You should be able to report of objects you made by going into other reports, when selecting a report type, and choosing the object you would like to report off of.

If you are just talking about opportunity's Stage field, to get Stage Durration you will need to use one of the Opprotunity  report types "Opportunity", "Opportunity History" or "Opportunity Field History".
Maia SaffouMaia Saffou

Can you do similar on Lead "custom" stages?

Jill OliveiraJill Oliveira
@Steve Molis, back to your post from 4/28/10, once we are able to calculate the current stage duration, how do we get a single report that combines stage duration for history and present stage? That is what I'm struggling with. Management wants to see a report that shows average days/stage through the current stage. 
Brad BernsteinBrad Bernstein
Has anyone figured out how to include Current Stage in the Opportunity History Report to give a comprehensive Average Stage Duration Report?
Lorin LopesLorin Lopes
Can anyone tell me what the Stage Duration equals True filter even does?  It changes my results slightly but I can't see which records it is excluding (the basis for their exclusion).