Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Alex 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

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 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.

Alain 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 Merwe
Hi Alain

What type of report is this? Opportunity History or Opportunity Field History?
Alain 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.

https://help.salesforce.com/articleView?id=reports_opp_history.htm&type=5

Alex van der Merwe

Am I on the right path? the % number seems to be very high
Alain 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 Merwe
Do I chose grouping one or grouping two to have my formula now? Thanks for the help I am getting there slowly
Alain 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): ​