Time-Based Filtering

SAQL gives you many ways to specify the range of dates that you want to look at, such as "all ops from the last fiscal quarter" or "all cases from the last seven days".

Using Date Ranges in Filters

Use these filters to specify the date range you want to look at:

Fixed date range, for example between August 1, 2018 and June 2, 2017
Relative date range, for example between two years ago and last month
Open-ended ranges, for example before 04/2/2018
Add and subtract dates, for example all records from three months before yesterday

Example: Display Opportunities Closed This Month

Suppose that you want to see which opportunities closed this month. Your data includes the account name, the close date fields, and the epoch seconds field.

Diagram showing the number of days each account has been opened for.

Use date() to generate the close date in date format. Then use relative date ranges to filter opportunities closed in the current month.

If the query is run in May 2018, the resulting data stream contains one entry:

Screenshot displaying opportunities closed in the current month, with seconds epoch time.

To add the close date in a readable format, use toDate().

The resulting data stream includes the full date and time of the close date.

Screenshot displaying opportunities closed in the current month, with seconds epoch.

You can also display just the month and day of the close date.

The resulting data stream contains the month and day of the close date.

Screenshot displaying opportunities closed in the current month, with close date and day.

Fixed Date Ranges

Use dateRange() to specify a fixed range of dates in a filter:

startArray_y_m_d is an array that specifies the start date

endArray_y_m_d is an array that specifies the end date

For example, return all records between October 2, 2014 and August 16, 2016:

Relative Date Ranges

Use relative date ranges to answer questions such as "how many opportunities did each rep close in the past fiscal quarter"? To specify a relative date range, use the in operator on an array with relative date keywords. For example, return all records from one year ago up to and including the current year.

Return all records from two quarters ago, up to and including two quarters from now.

Return all records from the last two fiscal years, up to and including today.

Use these relative date keywords:

current day
n day(s) ago
n day(s) ahead
current week
n week(s) ago
n week(s) ahead
current month
n month(s) ago
n month(s) ahead
current quarter
n quarter(s) ago
n quarter(s) ahead
current fiscal_quarter
n fiscal_quarter(s) ago
n fiscal_quarter(s) ahead
current year
n year(s) ago
n year(s) ahead
current fiscal_year
n fiscal_year(s) ago
n fiscal_year(s) ahead

Only standard fiscal periods are supported. See "About Fiscal Years" in Salesforce Help.

Note

Open-Ended Date Ranges

Use open-ended date ranges for queries such as "List all opportunities closed after 12/23/2014". For example, return all records up to and including the current month.

You can also specify a closed relative date range. For example, return all records from three years ago up to and including today.

Add and Subtract Dates

You can add and subtract dates using the relative date keywords. For example, return all records from one year ago, up to and including today.

Return all records from today up to two years and three months from now.

Analytics SAQL Reference