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