Salesforce Report Formulas

  1. Using PREVGROUPVAL
    1. Formula for period-to-period percentage change. Use a date grouping, such as created date (group by week, month, etc. Then use a report summary formula such as: (RowCount – PREVGROUPVAL(RowCount, CREATED_DATE))/PREVGROUPVAL(RowCount, CREATED_DATE) Date group must be sorted ascending. Check out the full post.
  2. Using PARENTGROUPVAL
    1. Percentage of grand total summary. Found this useful when showing group record counts (Matrix reports) as percentage of the total:
      1. Complete Formula:  RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)
  3. Non-Grouping Reporting
    1. Another useful article on calculating win (WON:SUM / CLOSED:SUM) and loss ((CLOSED:SUM – WON:SUM) / CLOSED:SUM) rates
  4. Report formula: New this Month
    1. Use this formula to return whether an opportunity was created during the month in which the report was run. This is useful to track number of opportunities created during a month and can be extended to include Lost this Month and Won this Month
      1. (IF(MONTH(DATEVALUE(Opportunity.CreatedDate)) = MONTH(Today()), 1, 0))
  5. Faster Excel VLOOKUP function
    1. For large datasets, say above 100 000 rows, the vlookup function with exact match mode set can be very slow. To overcome this, use two vlookup functions both with exact match mode set to false. Full explanation here. The formula looks like this:
      1. =IF(VLOOKUP(value_to_find,data_to_search,1,TRUE)=value_to_find, VLOOKUP(value_to_find,data_to_search,TRUE), “Not found”)