Ask Search:
Aaron PersichAaron Persich 

Report Formula to Count # of Opportunities

Hello,
 
I am working on a field history report which shows the # of times the stage of the opportunity changes.  What I am trying to do is create this report to show if the stage changes from X to Y per opportunity.  The report works but id creates duplicate opportunity records as shown below.
 
Is there a formula where I can count the number of Opportunities and not the number of stage changes?
 
User-added image
Any advice is much appreciated.
 
Thanks,

Aaron
 
Best Answer chosen by Aaron Persich
Steve MolisSteve Molis
I would not use a Field History Report for that, they're made for forensic aduditing, not analytics.  

I would use an "Opportunity History" report instead and sexy it up with Tom Tobin's "Power of One" and a few custom fields like this

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written*
https://success.salesforce.com/answers?id=9063000000048RbAAI

Tips & Tricks: Getting more out of Opportunity History Reports
https://success.salesforce.com/answers?id=90630000000DGJMAA4  

but that's just me...

All Answers

Steve DoddSteve Dodd
If you use an Opportunity History report type, you can use Stage Change = TRUE as a filter, then specify "X" as the From Stage and "Y" as the To Stage in the other two filters...here is an example:


User-added image
Steve MolisSteve Molis
I would not use a Field History Report for that, they're made for forensic aduditing, not analytics.  

I would use an "Opportunity History" report instead and sexy it up with Tom Tobin's "Power of One" and a few custom fields like this

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written*
https://success.salesforce.com/answers?id=9063000000048RbAAI

Tips & Tricks: Getting more out of Opportunity History Reports
https://success.salesforce.com/answers?id=90630000000DGJMAA4  

but that's just me...
This was selected as the best answer
Aaron PersichAaron Persich
Thanks guys.  I am now using the Opp History report.  I added the From and To Stage fitlers and the stage change = true.  But when I run it it now shows duplicate records again.  

User-added image
Steve DoddSteve Dodd
You mentioned you just wanted to show a change from Stage X to Stage Y

Your screen-shot shows four different Stages in the From Stage filter and three in the To Stage filter...so you're saying display a row every time an Opportunity went from one of these four Stages to one of those three Stages.
Steve MolisSteve Molis
++ SteveD  PREACH!!!
Aaron PersichAaron Persich
I got it to work using the new History Report and used the power of 1 formula.

Thanks for the help
Aaron PersichAaron Persich
Can I do the same report for when the Close Date Changes?
Steve MolisSteve Molis
CloseDate change ain't gonna be so easy, there isn't a native CloseDateChanged = True field on the History Object.  You might be packing your bags for the wonderful, whacky world of Apex Triggers for that one