Activity Forums Salesforce® Discussions How to create a report to compare current year sales vs. previous year sales in salesforce?

  • Yogesh

    Member
    December 3, 2019 at 9:10 am

    Hello,

    You can do something like that using a Matrix report grouped by Division and CloseDate(Month) and CloseDate(Year).
    However for the variance you'll need to create Custom Summary Formulas (CSF) and you might run into a problem displaying the monthly variance.

     

  • Deepak

    Member
    December 3, 2019 at 10:27 am

    Here's how to create a Custom Summary Formula to return period-over-period data comparisons within a report.

     

    Example: Period-Over-Period Reports

    Year-over-Year (YoY) - Track the progression of an individual's sales in 2018 compared to 2017 without needing to create any custom fields.
    Quarter-over-Quarter (QoQ) - Track the progression of an individual's sales Q1 Last Year vs Q1 Today without needing to create any custom fields.
    Resolution
    These instructions use the Opportunities Object, but the principles apply to all Salesforce reporting.

    1. From 'Reports,' click New Report
    2. Select the 'Opportunities' report type, then click Create.
    3. Set the report as Matrix Format.
    4. Drag Opportunity Owner as the primary 'Row Summary'
    5. Drag the Close Date field into the 'Column Summary' | Hover over field Grouping | Click 'v' | select Group By | Group By Fiscal Year.
    6. Once you have these, create a Custom Summary Formula field to calculate the value:

    1. Click Add Formula.
    2. Enter a Column Name | select 'Format' as Percent
    3. Ensure formula calculation occurs At a specific row/column grouping level...
    4. Enter the following formulas accordingly.

    ​​Year over Year:
    AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
    (Note: the same formula will be used for quarter over quarter progressive)

    Q1 (This Year) over Q1 (Prior Year):
    AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)
    Year on Year Change %:
    ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE))  /  PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
    Quarter-over-Quarter ( QoQ ) Change %:
    ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE))  /  PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)

    7. Click Save As.
    8. Enter your desired report name and saving location.
    9. Click Save and Run Report.

     

    Note: #Error! is returned anytime a formula is dividing by zero. If your formula errors in this way, the denominator in the fraction has returned "0."

Log In to reply.

Popular Salesforce Blogs

Popular Salesforce Videos