How do I use relative date filters?

To use a relative date filter, add a rule with a date field, choose an operator, and switch the Value from a fixed date to a dynamic date. Then pick a preset period, such as This Week, or a custom number of hours, days, weeks, months or years before or after the current moment.

On this page, the following topics are covered:

• Which operators can you use for date fields?
• How do you switch between a fixed date and a dynamic date?
• How do you filter on a period like this week or last month?
• How do you filter on a rolling period like the last 30 days?
• What is the difference between During and Greater than or equal to?
• Examples of relative date filters

Which operators can you use for date fields?

Date fields in Spotler CRM have their own set of operators, which define how the criteria builder compares a date with the date or period you select. You choose the operator under Operator in a rule, after you select a date field such as a created date.

The criteria builder supports these operators for date fields:

• Equal to and Not equal to: the date matches, or does not match, the selected date or period.
• Greater than: the date is after the selected point in time.
• Greater than or equal to: the date is on or after the selected point in time.
• Less than: the date is before the selected point in time.
• Less than or equal to: the date is on or before the selected point in time.
• During and Not during: the date falls, or does not fall, within the selected period, from its start to its end.
• Between and Not between: the date falls, or does not fall, between two points in time.
• Is set and Not set: the date field has a value, or is empty.

How do you switch between a fixed date and a dynamic date?

A fixed date is one specific date that you pick in a calendar. A dynamic date is a period or a point in time relative to today, which you choose from a list. You switch between the two with the small icon in front of the Value field of a rule on a date field.

• When the Value field shows a date picker, click the calendar icon to switch to a dynamic date. Hovering over the icon shows Dynamic date.
• When the Value field shows a dropdown, click the pencil icon to switch back to a fixed date. Hovering over the icon shows Fixed date.

How do you filter on a period like this week or last month?

To filter on a period such as this week, last month or next quarter, choose a preset in the dynamic date dropdown, under Presets. Spotler CRM determines the start and end of the period automatically each time the criteria are used. Use the During operator to select all records with a date within that period.

These presets are available:

The presets follow these rules:

• Weeks always run from Monday to Sunday. This cannot be changed.
• Quarters are standard calendar quarters: January to March, April to June, July to September and October to December.
• For most Spotler CRM environments, the financial year starts in January. The Calendar Year presets then select exactly the same period as the Year presets.

How do you filter on a rolling period like the last 30 days?

A rolling period counts a number of hours, days, weeks, months or years back or forward from the current moment, for example the last 30 days. The point in time is calculated again each time the criteria are used, for example when you run the report. You set a rolling period by choosing a unit under Custom in the dynamic date dropdown.

To filter on a rolling period:

1. Add a rule with a date field.
2. Choose an operator, for example Greater than or equal to.
3. Switch the Value to a dynamic date.
4. Under Custom, select a unit: Hours, Days, Weeks, Months or Years. A plus or minus sign and a number appear.
5. Choose minus to go back in time or plus to go forward, and enter the number.

For example, Greater than or equal to with minus 30 days selects all records with a date in the last 30 days. Greater than or equal to with plus 1 day selects all records with a date from tomorrow onwards.

Custom units count exactly from the current moment. Minus 1 week goes back exactly 7 days, which is different from the Last Week preset: that preset always covers the full Monday to Sunday week before this week.

What is the difference between During and Greater than or equal to?

During only selects dates within the period itself, while Greater than or equal to selects everything from the start of the period onwards, including later periods. Both operators are available in rules on date fields in the criteria builder.

For example, with the preset This Year:

• During This Year selects dates from 1 January through 31 December of this year.
• Greater than or equal to This Year selects dates from 1 January of this year, including the years after it.
• Less than or equal to This Year selects dates up to and including this year.

Examples of relative date filters

The table shows common selections and how to set them up in a rule on a date field in the criteria builder.