Year over Year Comparisons with Formula Fields
Sep 19, 2005
A common key performance indicator (KPI) in most companies is the "year-over-year" comparison, e.g., "How does sales performance in Q3 last year compare to Q3 this year?" Formula fields make it possible to provide this key metric in real-time, in salesforce.com. If you think your executives would like to see this metric, you can be a hero by reading on and spending about 10-20 minutes to set it up.
How to
set it up
I. Create formula fields on the relevant objects to categorize the
revenue by time period. These fields do not need to be displayed on any page
layouts, but they must be visible to show up in reports. In this example, I
configure these metrics for Opportunities:
A) Create 2 text formula fields:
1. Month Formula: CASE( MONTH ( {!CloseDate} ) , 1, "01 - January", 2,
"02 - February", 3, "03 - March", 4, "04 -
April", 5, "05 - May", 6, "06 - June", 7, "07 -
July", 8, "08 - August", 9, "09 - September", 10,
"10 - October", 11, "11 - November", "12 -
December")
2. Quarter Formula: CASE( MONTH ( {!CloseDate} ) , 1, "Q1", 2, "Q1", 3, "Q1", 4, "Q2", 5, "Q2", 6, "Q2", 7, "Q3", 8, "Q3", 9, "Q3", "Q4")
B) Create 2 currency formula fields:
1. Current Year Amount
IF( YEAR( {!CloseDate} ) = YEAR(TODAY()) , {!Amount} , 0 )
2. Previous Year Amount = IF( YEAR( {!CloseDate} ) = YEAR(TODAY()) - 1 ,
{!Amount} , 0 )
II. Create a new Summary Report on the object, grouping by Quarter and/or
Month. Total the Current and Previous Year Amount fields, and create a new
report formula field as follows:
Percentage Change = ( {!Opportunity.Current_Year_Amount__c:SUM} - {!Opportunity.Previous_Year_Amount__c:SUM} ) / {!Opportunity.Previous_Year_Amount__c:SUM}
Remarks
- Make sure your report's date range covers the 2 years in question
- For best results, hide the report details for the initial view:
Bookmark
This is an excellent way to compare year on year results.
Posted by: Chris | June 13, 2006 at 07:45 AM
If the previous year data is 0, you get a divide by 0 error. Easy fix, set your Percentage Change report formula as:
if (Opportunity.Previous_Year_Amount__c:SUM = 0, 1 ,
(Opportunity.Current_Year_Amount__c:SUM - Opportunity.Previous_Year_Amount__c:SUM )
/ Opportunity.Previous_Year_Amount__c:SUM
)
Posted by: Matt Pestritto | June 18, 2007 at 12:31 PM
Is this ability available to Professional version?
thanks!
Posted by: Lynn whye | March 24, 2009 at 11:13 AM
Yes it is available in Group Edition and above. Erik
Posted by: Erik Yewell | March 24, 2009 at 04:19 PM
I am stuck in the last step.
I have created these fields not displayed on pagy layout, but I have no idea how to set these fiels visible to show up in reports? Could you please advise?
Posted by: Yvonne | July 21, 2009 at 07:06 AM
Hello Yvonne,
I think you probably used Field Level Security to hide the fields. This prevents the fields from being accessible on the reports.
Instead, make the fields visible to users, but don't put them on the page layout. Then you should be able to find the fields, and put them on a report, even though users can't see the fields on the oppty.
Erik
Posted by: Erik Yewell | July 21, 2009 at 07:23 AM
Hi Erik,
Thank you for your quick response. We are using professional edition. There is no Field Level Security function avaiable to this edition.
Posted by: Yvonne | July 21, 2009 at 07:31 AM
I am confused here. I want to compare last years September leads to this years Septembers leads. How do I do this? I see the code above but it does not work.
Posted by: Terri | October 01, 2009 at 02:54 PM
Hello Terri and Yvonne,
Terri,
You need to create a lead report, which groups the results like this:
*first by lead created date, by *month in year*,
**then nested below that, by lead created date, by *year*
People don't often see the extra ability in filters to set the time period to group by (day, week, month, year, etc...). These options are only available when you are grouping by a date field.
Yvonne: as soon as you create a custom field, it is available in reporting. I can't think of why it would not be visible. Note however, that custom fields are located further down the "Choose Fields" page in the reporting wizard than the standard fields.
Hope that helps.
Erik
Posted by: erik yewell | October 01, 2009 at 04:43 PM