Filters

Topics and measures and can be filtered to curate both the UI and calculations, respectively. The filter syntax was built to mirror filtering in the UI, and embed the filters into modeled objects for simpler curation. They are structured with filter: (measures) or default_filters: (topics), followed by the field to filter, the filter type, and the values.

Examples

topics:
  west_coast_transactions:
    base_view: order_items
    default_filters:
      users.state:
       is: [California, Oregon, Washington]
      users.country:
       not: null

measures:
  count_minors:
    aggregate_type: count
    filters:
      age:
        less_than: 18

  count_18_or_19:
    aggregate_type: count
    filters:
      users.age:
        or:
          - is: 18
          - is: 19

  count_california_seniors:
    aggregate_type: count
    filters:
      age:
        greater_than_or_equal_to: 65
      state:
        is: California
        
  queries_last_28_days:
    aggregate_type: count
    filters:
      created_at:
        time_for_duration:
          - 28 complete days ago
          - 28 days
          
  queries_2023:
    label: Queries in 2023
    aggregate_type: count
    filters:
      created_at:
        time_for_duration: ['2023-01-01', '365 days']

  queries_after_today:
    label: Queries After Today
    aggregate_type: count
    filters:
      created_at:
        on_or_after: ['today']

Topic Arguments

default_filters:

  west_coast_transactions:
    base_view: order_items
    default_filters:
      users.state:
       is: [California, Oregon, Washington]

• default_filters: will begin the filter block for a given topic

Measure Arguments

filters:

  count_minors:
    aggregate_type: count
    filters:
      age:
        less_than: 18
        
  count_california_seniors:
    aggregate_type: count
    filters:
      age:
        greater_than_or_equal_to: 65
      state:
        is: California

• filters: will begin the filter block for a given measure

Field Reference

• Below filters: or default_filters:, one or more field references can be included with a colon
• Fields should be fully scoped if they are outside the current view (users.state above)

Filter Syntax

• Filtering is built to mirror the application's filter UI
• Values for the filter are to be included after the colon unquoted
  • Calfornia
  • 65
• If using an array, values are included inside brackets
  • [1,2,3]
  • [California, Oregon, Washington]
• Any filter argument can exist in negated form with not_
  • day_of_week: --> not_day_of_week:
  • starts_with: --> not_starts_with:

Accepted filters:

• All Types
  • is:
  • not:
    • note the negated of is: should be not: instead of not_is
• Time Filters
  • day_of_week:
  • month_of_year:
  • quarter_of_year:
  • day_of_month:
  • day_of_year:
  • day_of_quarter:
  • hour_of_day:
  • between_dates:
  • on_or_after:
  • before:
  • time_for_duration: (examples below)
  • date_offset_from_query: (advanced, only supported in filtered measures, see below)
• Numeric Filters
  • greater_than_or_equal_to:
  • less_than_or_equal_to:
  • less_than:
  • greater_than:
  • between:
• String Filters
  • starts_with:
  • ends_with:
  • contains:

time_for_duration

  filters:
    users.created_at:
      time_for_duration:
        - starting_time
        - unit_of_time_length
        
  filters:
    users.created_at:
      time_for_duration:
        - 7 days ago
        - 7 days

Time for unit duration can be useful for filtering over all sorts of time periods. To use this filter, Omni expects a starting point, using either relative time (28 days ago, 7 complete weeks ago) or a fixed date (2024-06-01) and a length of time after that point (30 days, 1 year).

date_offset_from_query

measures:
  count_signups_same_time_two_years_previously:
    aggregate_type: count
    filters:
      created_at:
        cancel_query_filter: true
        date_offset_from_query: 2 years

date_offset_from_query, rather than specifying a filter on its own, specifies a filter dynamically relative to the date filter in the query the user creates in the UI.

E.g. in the above example, if it's November 2024, and the user filters for "last month" in the UI and selects "count_signups_same_time_two_years_previously", they will see the count from October 2022.

Note the usage with cancel_query_filter. In general, date_offset_from_query must be used in concert with cancel_query_filter to make sense. Without cancel_query_filter, in our example above, the measure would be filtering for October 2024 and October 2022, which presumably would return zero, a nonsense result.

These types of measures can be especially tricky. Omni will create these measures automatically for you if you use the Period Over Period functionality in the workbook, "Flatten Pivot", and add the fields produced by "Flatten Pivot" to the workbook.

cancel_query_filter

measures:
  compare_to_users_in_california:
    aggregate_type: count
    filters:
      state:
        cancel_query_filter: true
        is: California
  users_in_all_states_all_time:
    aggregate_type: count
    filters:
      created_at:
        cancel_query_filter: true
        is: "" # (no filter)
      state:
        cancel_query_filter: true
        is: "" # (no filter)

In addition to being used in tandem with date_offset_from_query (see above) cancel_query_filter can be added as an argument to any other filter on a measure. This creates a measure that ignores the value of the filter in the user query in favor of the filter on the measure (rather than implicitly applying an AND).

E.g. if, as a user, I am querying data filtered by "New York", I can use the "compare_to_users_in_california" measure above to directly see the count in California, without changing the filter in my query. Without the cancel_query_filter, this measure would filter for users in California, within a query already filtered to New York, yielding zero.

Or, I can use the "users_in_all_states_all_time" to compare the all-time count, completely ignoring the filters on both "created_at" and "state".

Time Filter Model Examples

Using filters in the model can be tricky, so it's often faster to use the UI to build the filter and save a query view to see the syntax in YAML. Below are some examples of common time filters.

In the past 7 days:

  filters:
    users.created_at:
      time_for_duration:
        - 7 days ago
        - 7 days

In the past 7 complete days:

  filters:
    users.created_at:
      time_for_duration:
        - 7 complete days ago
        - 7 days

From 2023-04-01 to 2023-04-30:

  filters:
    users.created_at:
      between_dates:
        - 2023-04-01
        - 2023-04-30

Before 2023-04-01:

  filters:
    users.created_at:
      before: 2023-04-01

On or after 2023-04-01:

  filters:
    users.created_at:
      on_or_after: 2023-04-01

On the day 2023-04-01

  filters:
    users.created_at:
      is: 2023-04-01

Is in the quarter Q2 2023:

  filters:
    users.created_at:
      is: 2023 Q2

Is hour of day 1 (note the quotes):

  filters:
    users.created_at:
      hour_of_day: "1"

Is on Wednesdays:

  filters:
    users.created_at:
      day_of_week: Wednesday

First 3 months of 2023:

  filters:
    users.created_at:
      time_for_duration:
        - 2023-01-01
        - 3 month

Compound Filters (AND / OR / Filter From Query)

Complex use cases may require compound filter declarations (before yesterday and in the past week). This can be done using the following syntax:

  filters:
    users.created_at:
      and:
        - time_for_duration: [ 1 week ago, 1 week ]
        - before: 1 days ago

  filters:
    users.state:
      or:
        - starts_with: C
        - ends_with: A

  filters:
    users.state:
      or:
        - is: California
        - is: ""          ## this is "is empty" in the UI
        - is: null        ## this is "is null" in the UI

### This is how to declare a filter object from another query
  filters:
    users.state:
      field_name_in_query: users.state
      query_structure:
        fields: [ users.state, users.count ]
        base_view: users
        limit: 5
        sorts:
          - field: users.count
            desc: true
        topic: users

Handling True / False and Nulls (Falsey)

At times, it makes sense to treat boolean as strictly true / false, and at times the distinction to use true / false / null is important. For that reason, Omni uses a special boolean argument in filters called 'falsey' that represents false or null. Filtering with falsey works just like any other field value.

This will count error is false and error is null:

  non_error_count:
    aggregate_type: count
    filters:
      is_error:
        is: falsey

This will only count error is false:

  non_error_count:
    aggregate_type: count
    filters:
      is_error:
        is: false