Customising a report's date filtering

Use the report filter formula to apply advanced date filter logic

Sometimes the built-in date filtering doesn't quite do enough. Some examples where custom date filtering would help are:

  • Switching between "Created Date" for new jobs, and "Completed Date" for finished jobs
  • Including a "previous period"

Setting up a formula based date range filter

The first step is enable the date range filter, and pick Row Filter Formula:

Next, you'll need to add some logic to the formula. The user-selected dates will be available as [Date Range Start] and [Date Range End].

Here is an example which does a simple check on Invoice Date:

Here is another example formula which applies logic based on status: 

IF([Status] = "Completed",
[Completed Date] >= [Date Range Start] AND [Completed Date] <= [Date Range End],
[Opened Date] >= [Date Range Start] AND [Opened Date] <= [Date Range End]

Here is another example which includes a previous period:

([Invoice Date] >= [Date Range Start] AND [Invoice Date] <= [Date Range End])
([Invoice Date] >= ([Date Range Start] - INTERVALVALUE("1 year"))
[Invoice Date] <= ([Date Range End] - INVERVALVALUE("1 year"))