+ Start a Discussion
jbozajboza 

Lead Stage Change Pecrcentage

I would like to have a dashboard show the percentage of leads that change per each stage. So all leads that move from stage 1 to 2, and then also for stage 2 to 3, and so on. I am stumped thinking of the formula and how to implement.
ShashForceShashForce
Hi,

We need the PRIORVALUE() function for this. But, this function is not available in formula fields, but only in workflows and validation rules.

So, we can configure a workflow rule and do a field update to show that gthe lead changed from satge1 to stage2. Please bear in mind that this update will happen when a lead record is created or edited, unlike a formula. The criteria of the workflow will look something like below. I framed this example assuming Stage is a custom picklist field with values stage1, stage2, stage3, stage4.

OR(AND(ISPICKVAL(Stage__c,"stage2"),ISPICKVAL(PRIORVALUE(Stage__c),"stage1")),AND(ISPICKVAL(Stage__c,"stage3"),ISPICKVAL(PRIORVALUE(Stage__c),"stage2")),AND(ISPICKVAL(Stage__c,"stage4"),ISPICKVAL(PRIORVALUE(Stage__c),"stage3")))

The formula for the field update may look something like:

TEXT(PRIORVALUE( Stage__c )) & " TO " & TEXT(Stage__c)

This will update, for example, "stage1 to stage 2" on whichever field you choose to be updated.

Replace stage__c with your stage picklist field, and depending on how many values you have in your field, the workflow criteria formula will become longer, i.e. more ANDs in the OR.

If this answers your question, please mark this as the Best Answer for this post, so that others can benefit from this post.

Thanks,
Shashank
jbozajboza
I think I understand and then I will need to create a formula for the percent of change of each stage. Basically I want to give my manager a report with stage changes over each month. Basically grouped percentages by month.
jbozajboza
The reports only allow a certain number of formulas. I want to show how many leads changed from Stage 1 to 2 and stage 2 to 3, and stage 3 to 4, and stage 1 to 3, stage 1 to 4, and so on. I can not think of the best way to show these percentages grouped over a monthly report.