Print this page

How to Calculate parameters like Year Over Year (YoY), quarter on quarter (QoQ) data using reports custom summary formula

Knowledge Article Number 000220136
Description

Is there a way for me to direct from which of the previous groups, the present value is calculated against. The following article will help you complete one/all of the following:

  • YOY - Track the progressions of an individuals sales Year over Year without needing to create any custom fields.
 
Close Date
Grand Total
 
Opportunity Owner
 
FY 2013
FY 2014
FY 2015
 
Jon Smits
Sum of Amount
Year Over Year
Record Count
$635,000.00

2
$600,000.00
94.49%
3
$598,000.00
99.67%
2
$1,833,000.00

7
 
Grand Total
Sum of Amount
Record Count
$635,000.00
2
$600,000.00
3
$598,000.00
2
$1,833,000.00
7
 
  • QOQ - Track the progressions of an individuals sales Q1 Last year vs Q1 Today without needing to create any custom fields.

     
      Close Date 
     
     Opportunity Owner Q1 FY2013Q2 FY2013Q3 FY2013Q4 FY2013 
     
      Sam P. (Sales)Sum of Amount$39,000.00 $35,000.00 $78,000.00 $78,000.00  
    Qt / Qt 
    QoQ Change % 
     Grand TotalSum of Amount$39,000.00 $35,000.00 $78,000.00 $78,000.00  
     
  Close DateGrand Total 
 
 Opportunity Owner Q1 FY2014Q2 FY2014Q2 FY2015 
 
  Sam P. (Sales)Sum of Amount$650,000.00 $550,000.00 $598,000.00 $2,028,000.00  
Qt / Qt1666.67%1571.43%766.67% 
QoQ Change %1566.67%1471.43%666.67% 
 Grand TotalSum of Amount$650,000.00 $550,000.00 $598,000.00 $2,028,000.00  
 
* Including the change %
Resolution

(Note: Examples are using opportunities, but these principles apply to all salesforce reporting)

  1.  From Reports Tab > Click New Report 
  2.  Select Opportunities report type > Click "Create"
  3.  Set the report as a Matrix Format report 
  4.  Drag Opportunity Owner as the primary "Row Summary" 
  5.  Drag the Close Date field into the "Column Summary" > Hover over field Grouping > Click the "v" > and Choose Group By > Group By Fiscal Year
  6. Once you have these you will create a Custom Summary Formula field to calculate value,
    1. Click "Add Formula"
    2. Enter "Column Name"   >  set "Format" to Percent 
    3. Ensure formula calculation occurs "At a specific row/column grouping level..."  
    4. Use the following formulas accordingly: 
      (i) ​​Year over Year :

      AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)

      (note: the same formula will be used for quarter over quarter progressive)

      (ii) Q1 (This Year) over Q1 (Prior Year):


      AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)

       

      (iii) Year on Year ( YoY ) or Quarter over Quarter ( QoQ ) Change %: 

      ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE))  /  PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)  -  Year on Year 
      ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE))  /  PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4) - Quarter on Quarter 

    5. Save & Run Report




promote demote