Filters and Relative Dates

Filters allow you to set limits on the scope of the report. There are two ways to apply filters. You can apply a fact-level filter in the Filter expression box for the desired fact. Alternatively, you can apply a filter to the entire report by pressing the [Add Filter] button and creating the filter in its own fact.

To create a filter:

  1. Add the data field by which the report will be filtered. Note that you may add fields by dragging them from the Fields pane. You may also add fields by typing in the filter fact and selecting from a drop-down list.
  2. Add an operator by typing it directly into the filter fact. View available operators below.
  3. Enter the value by which to filter.
    • Relative date values should be surrounded by {curly braces}. These display in yellow.
    • All other values should be surrounded by “quotation marks”. These display in white boxes e.g "2017-07-01 00:00:00" or "2017-07-01"
    • Static dates should be entered in mm-dd-yyyy format e.g. 04-16-2012.

Available Operators

  • AND
  • OR
  • NOT
  • IN
  • > and ≥
  • < and ≤
  • = and ≠

Relative Dates

Relative dates are date values that change relative to the present time e.g. {yesterday}, {last week}, and {2 months ago}. Relative dates are useful for creating reports that must be generated at regular intervals without having to manually change the date filter each time.

For example, say you have a sales report that you want to generate daily for sales from the previous day. Without using a relative date, you would need to edit the date filter each day. However, if you set the filter to the relative date {yesterday}, the report will always generate with sales from the previous day.

The syntax for relative dates is quite powerful. All of the following could be used as relative dates. Note that relative date values should be surrounded by {curly braces}:

  • {yesterday}
  • {today}
  • {now}
  • {last week}
  • {last month}
  • {7 days ago}
  • {9 weeks ago}
  • {4 months ago}
  • {2 years ago}

Note that the scope of the report filter is determined by the unit of time used in the relative date. If you filter for sales from {last week}, you will generate a report with data from the whole of the previous week. However, if you filter for sales from {7 days ago}, you will generate a report with one day’s worth of data from 7 days prior.

Have more questions? Submit a request

2 Comments

  • 0
    Avatar
    Jason

    What about future relative dates? Only present and past filter options are included here, but what is the syntax to use for dates in the future. I've found {next month}, but how would we get 30 days in to the future, or 2 months, etc.?

    Thanks

  • 0
    Avatar
    Quan Vu

    Hi Jason,
    Sorry for the delayed response here. We're opening up a new ticket in Zendesk now to field this question from you further.

Please sign in to leave a comment.
Powered by Zendesk