In Reporting you can set criteria on date fields to only show values within a certain calendar range. Whilst we give you lots of options of calendar ranges to set, you may require an arbitrary range not provided in the list.

To set an arbitrary range, we firstly create a calculated column which identifies records within this range, then by setting a calculated criteria on the calculated column, we can limit the results to the calendar range accordingly.

If we wanted to only display Activities created in the last 72 hours, we are, in other words, looking for cases where the created at time is equal to or greater than the time 72 hours ago. This can be calculated using the following formula:

IF(created_at >= DATE_SUB( NOW(), INTERVAL 72 HOUR), 1, 0)

Now in this example, an Activity cannot be created in the future, therefore we do not need to set an upper limit on the interval. However if the datetime field we're setting the criteria on can have future values, Due date for example, then an upper limit will need to be set:

IF(due_date >= DATE_SUB( NOW(), INTERVAL 72 HOUR) AND due_date <= NOW (), 1, 0)

With the calculated column in place, we simply set a calculated criteria on the column so that the report only return rows where this column is equal to 1.

For other intervals change the second parameter in the DATE_SUB function - find syntax for other intervals here.