Ask Search:
Jeff WillsJeff Wills 

Is there a list of the variables used for filters (i.e. LAST 30 DAYS)?

I would like to know what all of the special values can be used in the filtering (i.e TODAY ...). 

Also, is there a way to display only the top 5 of a grouping?  I am grouping my report, but don't want all of the information returned.  I would like the top 5.  Any ideas?

Thanks,

Jeff
Mark PassovoyMark Passovoy
you can limit the number of rows displayed in a report (in the new report builder), by going to Edit Custom Filters at the top of the customize page and then configure the Limit Rows section:

User-added image
Rhonda RossRhonda Ross
If you do a tabular report, you have the option of limiting it to the top 5.  You do this in the custom filter portion of the report design.

Here's all the special date filters -- copied from Online Help

Special Date Values for Filter Criteria
Available in: All Editions

Use the following special date values when defining filter criteria using dates. If you choose a date field (for example, Create Date, Last Update Date) as a filter criteria, the value can be a date in the format allowed by your Locale setting or any of the following special values:

Special Date ValueRange
YESTERDAYStarts at 12:00:00 a.m. on the day before the current day and continues for 24 hours.
TODAYStarts at 12:00:00 a.m. on the current day and continues for 24 hours.
TOMORROWStarts at 12:00:00 a.m. on the day after the current day and continues for 24 hours.
LAST WEEKStarts at 12:00:00 a.m. on the first day of the week before the current week and continues for seven days.

For Enterprise, Unlimited, Professional, and Developer Editions, the week is defined by the Locale drop-down list in your personal information settings at Your Name | Setup | My Personal Information | Personal Information. For Contact Manager, Group, and Personal Editions, the week is defined by the Locale setting in the company profile at Your Name | Setup | Company Profile | Company Information. For example, when the Locale is US English, a week runs Sunday to Saturday, whereas with UK English, a week spans Monday to Sunday.

THIS WEEKStarts at 12:00:00 a.m. on the first day of the current week and continues for seven days.

For Enterprise, Unlimited, Professional, and Developer Editions, the week is defined by the Locale drop-down list in your personal information settings at Your Name | Setup | My Personal Information | Personal Information. For Contact Manager, Group, and Personal Editions, the week is defined by the Locale setting in the company profile at Your Name | Setup | Company Profile | Company Information. For example, when the Locale is US English, a week runs Sunday to Saturday, whereas with UK English, a week spans Monday to Sunday.

NEXT WEEKStarts at 12:00:00 a.m. on the first day of the week after the current week and continues for seven days.

For Enterprise, Unlimited, Professional, and Developer Editions, the week is defined by the Locale drop-down list in your personal information settings at Your Name | Setup | My Personal Information | Personal Information. For Contact Manager, Group, and Personal Editions, the week is defined by the Locale setting in the company profile at Your Name | Setup | Company Profile | Company Information. For example, when the Locale is US English, a week runs Sunday to Saturday, whereas with UK English, a week spans Monday to Sunday.

LAST MONTHStarts at 12:00:00 a.m. on the first day of the month before the current month and continues for all the days of that month.
THIS MONTHStarts at 12:00:00 a.m. on the first day of the current month and continues for all the days of that month.
NEXT MONTHStarts at 12:00:00 a.m. on the first day of the month after the current month and continues for all the days of that month.
LAST 90 DAYSStarts at 12:00:00 a.m. 90 days before the current day and continues up to the current second. (The range includes today.)
NEXT 90 DAYSStarts at 12:00:00 a.m. on the day after the current day and continues for 90 days. (The range does not include today.)
LAST n DAYSStarts at 12:00:00 a.m. n days before the current day and continues up to the current second. (The range includes today.)
NEXT n DAYSStarts at 12:00:00 a.m. on the next day and continues for the next n days. (The range does not include today.)
LAST QUARTERStarts at 12:00:00 a.m. on the first day of the quarter before the current quarter and continues to the end of that quarter.
THIS QUARTERStarts at 12:00:00 a.m. on the first day of the current quarter and continues to the end of the quarter.
NEXT QUARTERStarts at 12:00:00 a.m. on the first day of the quarter after the current quarter and continues to the end of that quarter.
LAST n QUARTERSStarts at 12:00:00 a.m. on the first day of the quarter n quarters ago and continues to the end of the quarter before the current quarter. (The range does not include the current quarter.)
NEXT n QUARTERSStarts at 12:00:00 a.m. on the first day of the quarter after the current quarter and continues to the end of the quarter n quarters in the future. (The range does not include the current quarter.)
LAST YEARStarts at 12:00:00 a.m. on January 1 of the year before the current year and continues through the end of December 31 of that year.
THIS YEARStarts at 12:00:00 a.m. on January 1 of the current year and continues through the end of December 31 of the current year.
NEXT YEARStarts at 12:00:00 a.m. on January 1 of the year after the current year and continues through the end of December 31 of that year.
LAST n YEARSStarts at 12:00:00 a.m. on January 1 of the year n years ago and continues through December 31 of the year before the current year.
NEXT n YEARSStarts at 12:00:00 a.m. on January 1 of the year after the current year and continues through the end of December 31 of the nth year.
LAST FISCAL QUARTERStarts at 12:00:00 a.m. on the first day of the fiscal quarter before the current fiscal quarter and continues through the last day of that fiscal quarter. The fiscal quarter is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year. Note
None of the FISCAL special date values are supported when creating mobile custom views.
THIS FISCAL QUARTERStarts at 12:00:00 a.m. on the first day of the current fiscal quarter and continues through the end of the last day of the current fiscal quarter. The fiscal quarter is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
NEXT FISCAL QUARTERStarts at 12:00:00 a.m. on the first day of the fiscal quarter after the current fiscal quarter and continues through the last day of that fiscal quarter. (The range does not include the current quarter.) The fiscal quarter is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
LAST n FISCAL QUARTERSStarts at 12:00:00 a.m. on the first day of the fiscal quarter n fiscal quarters ago and continues through the end of the last day of the previous fiscal quarter. (The range does not include the current fiscal quarter.) The fiscal quarter is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
NEXT n FISCAL QUARTERSStarts at 12:00:00 a.m. on the first day of the fiscal quarter after the current fiscal quarter and continues through the end of the last day of the nth fiscal quarter. (The range does not include the current fiscal quarter.) The fiscal quarter is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
LAST FISCAL YEARStarts at 12:00:00 a.m. on the first day of the fiscal year before the current fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
THIS FISCAL YEARStarts at 12:00:00 a.m. on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal year is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
NEXT FISCAL YEARStarts at 12:00:00 a.m. on the first day of the fiscal year after the current fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
LAST n FISCAL YEARSStarts at 12:00:00 a.m. on the first day of the fiscal year n fiscal years ago and continues through the end of the last day of the fiscal year before the current fiscal year. (The range does not include the current fiscal year.) The fiscal year is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.
NEXT n FISCAL YEARSStarts at 12:00:00 a.m. on the first day of the fiscal year after the current fiscal year and continues through the end of the last day of the nth fiscal year. (The range does not include the current fiscal year.) The fiscal year is defined in the company profile at Your Name | Setup | Company Profile | Fiscal Year.

For example, to create a custom view or report of accounts created last week, enter “Create Date equals LAST WEEK.” Date values are not case-sensitive; you can use “LAST WEEK” or “last week.” For special date values with variables (such as “NEXT N DAYS”), substitute N with any number greater than zero. Do not use commas.

 

Jeff WillsJeff Wills
Thanks for your help. The SF help didn't display the list of variables to use in the filtering criteria. 

I was able to utilize additional capabilities in the dashboards to filter rows.  The limit row feature for tabular reporting doesn't allow me to limit by the field that is grouped.  Grouping changes the report to either a summary or matrix report.

Using dashboards enables the flexibility using tables to limit, group, and sort information.
Mark PassovoyMark Passovoy
@Rhonda - can you post the URL where that information is located?
Meredith SalgetMeredith Salget
Hello all,

Just reading through this forum and some of Rhonda's tips are new to me.

Where is the "Next n days" filter found? My supervisor requested this, but I didn't know it existed! Any help would be much appreciated.

Thanks!
Meredith
David HoltDavid Holt
https://help.salesforce.com/apex/HTViewHelpDoc?id=custom_dates.htm&language=en_US Contains all the special date values used in selection criteria.
Ajay DubediAjay Dubedi
Hi Jeff
Please take a moment to mark the most helpful post as Best Answer or share your solution to help others in the community with similar questions.  Should you require additional help, please do not hesitate to ask.
Thank you all for sharing your vast knowledge with Success Community.
Jordan WhiteheadJordan Whitehead
Is there a way to filter by certain days, etc like "NEXT MONDAY"? I am traying to make a report to auto generate for the following week but haven't had any luck
Raegan HillRaegan Hill
I want my report to show ONLY results for the last 30 days BEFORE this week. So it wouldn't take into account THIS week. If today is 1/14/17 it would show 12/7/16 through 1/7/17 and NOT THIS WEEK. Does that make sense? Is there a way to create a formula in the Date field where you subtract the last 7 days from it so they don't show in the search results? I don't want this week to show in the search results but want the last 30 days BEFORE this week started. 
Bailey McGalliardBailey McGalliard
@Meredith, just type the relative reference where you would normally type the date instead of selecting a date. 
@Raegan, use filter logic to query Date equals LAST 30 DAYS AND Date not equal to THIS WEEK.
Robyn RobertsRobyn Roberts
I'm trying to set up a workflow that will move leads that are older than 3 weeks old to a different status but having difficulty finding the right filter criteria.  tried THREE WEEKS AGO with no luck.  Does anyone know the correct verbiage to enter?
Bailey McGalliardBailey McGalliard
Robyn, Date not equal to LAST 3 WEEKS should work.
Brandon HolmesBrandon Holmes
Is there an equivalent that will give the current date/time combo? I'd like a list that only reports records with a Date/Time field greater than or equal to RIGHT NOW, but can't find the right variable to use to get the current date/time.
Win Htoo HtooWin Htoo Htoo
Here is the url.

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm