function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Alex van der MerweAlex van der Merwe 

stage duration %

Hi peeps, I need to create a report that shows a % of how many opportunities moved from stage to stage 
User-added image
This was created in excel and would like to create it in salesforce. Basically the way it was calculated was using a simple =SUMIFS(oppys amount,sales qualified stage,"x")/SUMIFS(oppys amount,unqualified stage,"x") formula.

Please help me understand if this is possible to re-create in salesforce or not. My VP of sales is counting on me. 
Best Answer chosen by Alex van der Merwe
Alain CabonAlain Cabon
Hi,

You can just play with the buckets (created into a bucket field) to order the results of a summary report group by stage and use the summary function PREVGROUPVAL.

The values of the bucket is: 1-Unq (containing one stage "Unq") , 2-Sq, 3-Quoting, 4-Sent, 5-Awd  and so on.

The formula field wiht a percent type is just: AMOUNT:SUM / PREVGROUPVAL(AMOUNT:SUM, BucketField_<your number>) 

PREVGROUPVAL of Sq is Unq  because the order of the report used the order of the bucket names : 1-Unq < 2-Sq

With more than 9 stages, you can use: 01-Sq, 02-Unq, ...

The bucket field name is automatically converted in BucketField_<your number> by the formula editor.
 

All Answers

Alain CabonAlain Cabon
Hi,

You can just play with the buckets (created into a bucket field) to order the results of a summary report group by stage and use the summary function PREVGROUPVAL.

The values of the bucket is: 1-Unq (containing one stage "Unq") , 2-Sq, 3-Quoting, 4-Sent, 5-Awd  and so on.

The formula field wiht a percent type is just: AMOUNT:SUM / PREVGROUPVAL(AMOUNT:SUM, BucketField_<your number>) 

PREVGROUPVAL of Sq is Unq  because the order of the report used the order of the bucket names : 1-Unq < 2-Sq

With more than 9 stages, you can use: 01-Sq, 02-Unq, ...

The bucket field name is automatically converted in BucketField_<your number> by the formula editor.
 
This was selected as the best answer
Alex van der MerweAlex van der Merwe
Hi Alain

What type of report is this? Opportunity History or Opportunity Field History?
Alain CabonAlain Cabon
Hi,

Opportunity History has "From stage" (group level 1) and "To stage" (group level 2) for the groups of the summary report.

so that should be simpler with  just a formula field of type percent like:

AMOUNT:SUM/PARENTGROUPVAL(AMOUNT:SUM, FROM_OPPORTUNITY_STAGE_NAME)

The first idea was based on existing data but not on the object history and that was not your need.
You don't need buckets anymore probably.

There is already this report:
https://help.salesforce.com/articleView?id=reports_opp_history.htm&type=5
 
Alex van der MerweAlex van der Merwe
User-added imageUser-added imageUser-added image

Am I on the right path? the % number seems to be very high 
Alain CabonAlain Cabon
The buckets could help for ordering the data of the report but the formula percent is just now:

AMOUNT:SUM/PARENTGROUPVAL(AMOUNT:SUM, FROM_OPPORTUNITY_STAGE_NAME)

That should be sufficient.
Alex van der MerweAlex van der Merwe
Do I chose grouping one or grouping two to have my formula now? Thanks for the help I am getting there slowly
User-added image
Alain CabonAlain Cabon
Ok you can try any level that you want, but always use the generator of formula on the right until you can see "Summary" in dropdown list of "Functions" where you see "All" above. As soon as you have selected, "Summary" you can choose "PARENTGROUPVAL" or "PREVGROUPVAL" and use the button "Insert" for a sure generated formula where you have just to replace the generic "summary_field" with AMOUNT:SUM (for example).


VIDEO: Salesforce.com PARENTGROUPVAL Report Formula (4 min 35): ​
https://www.youtube.com/watch?v=7pn-9yCLgRE