Using Literal Filter Criteria or “Rolling” Date Ranges on Reports

December 28, 2012

One of those “aha” moments for a Salesforce Administrator is when they discover you can build a report with a rolling date range. For example “All completed Activities in the last 365 days”, “All Opportunities closing in the next 90 days”, “Number of Accounts created last month”, Contacts with no activity in the last 60 days,”etc.

Salesforce officially calls these type of date range filters “Literal Filter Criteria” in Help, though I’ve also heard them referred to as “Relative” or “Rolling” date filters. They allow you to write a dynamic report that looks forward or backward for a set period of time based on when you ran the report. For example, if you have a report to return all Opportunities created “Last Month” (based on the Created Date field) and ran that report in December, you’d see Opportunities created in November. If you ran that same report in January, you’d see Opportunities created in December. In many cases this type of dynamic date filter can be desirable versus hard-coding a date range (for example in a Dashboard that reports sales “this month” or “last month”).

You can use a literal date range filter on any date field available in your report. Keep in mind if that date field is filtered elsewhere in your report (like in the report “Time Frame” section as in the screenshot below), you may get unwanted results.

Example Literal Date Range Report Filter

With the Summer 12 Release, Salesforce beefed up the options available. Here is a complete list of filters you can apply in a report:

  • YESTERDAY
  • TODAY
  • TOMORROW
  • LAST WEEK
  • THIS WEEK
  • NEXT WEEK
  • LAST n WEEKS
  • NEXT n WEEKS
  • n WEEKS AGO
  • LAST MONTH
  • THIS MONTH
  • NEXT n MONTHS
  • LAST n MONTHS
  • n MONTHS AGO
  • NEXT MONTH
  • LAST 90 DAYS
  • NEXT 90 DAYS
  • LAST n DAYS
  • NEXT n DAYS
  • n DAYS AGO
  • LAST QUARTER
  • THIS QUARTER
  • NEXT QUARTER
  • LAST n QUARTERS
  • NEXT n QUARTERS
  • n QUARTERS AGO
  • LAST YEAR
  • THIS YEAR
  • NEXT YEAR
  • n YEARS AGO
  • LAST n YEARS
  • NEXT n YEARS
  • LAST FISCAL QUARTER
  • THIS FISCAL QUARTER
  • NEXT FISCAL QUARTER
  • LAST n FISCAL QUARTERS
  • NEXT n FISCAL QUARTERS
  • n FISCAL QUARTERS AGO
  • LAST FISCAL YEAR
  • THIS FISCAL YEAR
  • NEXT FISCAL YEAR
  • LAST n FISCAL YEARS
  • NEXT n FISCAL YEARS
  • n FISCAL YEARS AGO