Filters

In this page we dive into the filters metric property. We will explain what filters do, what types of filters are possible and how to use filters to define the data subset a metric relates to.

What are filters?

Metrics perform calculation on data objects or entities. Filters are the key metric property which determines what subset of the data is required in its calculation.

Filters are a set of criteria or logical conditions on the dimensions of an entity in order for that entity to affect metric results.

Review metric filters to quickly understand what data is - and isn't - included in the metric.

Modify or add filters to change and refine the specific group or segment the metric relates to.

Types of filters

There are several types of filters that can be used to precisely define the specific subset of the data for the metric.

Type	Description	Example
Entity dimensions	Filter the data based on the value of different dimensions (columns) of the entity (object table).) conditions.	$stage_name = 'Closed Won' or $src__amount > 1000
Joined dimensions	Filter data based on dimensions of related object properties.	$owner_user.region = 'EPAC'
Period relative	Filter the data by comparing date dimensions to the metric period.	$close_date <= $period.end
Metric value	Filter the data based on entities' value in other metrics.	$metric__starting_pipeline_amount > 1000

Using filters

Filters are used in much the same way as SQL query WHERE conditions:

• Create condition statements on different value types (number, string, etc.)
• Use multiple conditions to define the specific slice of data
• Metric will only consider the data where all conditions apply

Use standard SQL

Currently, metric filters support standard SQL column data types: String, Number, Boolean, Date, Timestamp.

Type	Description	Example
String	Filter objects based on string conditions.	$stage_name = 'Closed Won'
Boolean	Filter objects based on boolean (true/false) conditions.	$is_won = true
Number	Filter objects based on numerical conditions.	$probability >= 90
Null	Filter objects based on null conditions.	$lead_source is null
Timestamp	Filter objects based on timestamp conditions.	$close_date > '2023-01-01'
Metric	Filter objects based on whether it is connected to another metric with a value.	$metric__filter_null is not null

Use multiple filters

Within the filters metric property, you can list multiple filter conditions with the - sql: prefix.

Combining conditions with "AND"

In order to define And conditions between multiple filters use seperate - sql: conditions.

Below is the metric schema for the Won New Business Opportunities metric that counts the number of opportunities where both the stage_name is Closed Won AND the amount_in_usd is above $5000:

- name: won_new_business_opportunities
  entity: opportunity
  operation: count
  filters:
    - sql: $stage_name = 'Closed Won'
    - sql: $amount_in_usd > 5000

These conditions are translated to simple WHERE conditions in SQL:

where
      ("opportunity"."stage_name" = 'Closed Won')
      and ("opportunity"."amount_in_usd" > 5000)

Including several conditions with "OR"

To include OR conditions, add OR within the same - sql: condition.

Below is the same metric schema for the Won New Business Opportunities metric, but now we will use OR to count all opportunities that have amount_in_usd above $5000 or a probability above 90:

- name: won_new_business_opportunities
  entity: opportunity
  operation: count
  filters:
    - sql: $stage_name = 'Closed Won'
    - sql: $amount_in_usd > 5000 or $probability >= 90

These conditions are translated to simple WHERE conditions in SQL:

where
      ("opportunity"."stage_name" = 'Closed Won')
      and (
        "opportunity"."amount_in_usd" > 5000
        or "opportunity"."probability" >= 90
      )

Using "Source" or "Normalized" dimensions

It's possible to filter entities by their Source dimensions or by their Normalized dimensions.

To filter by any Source dimension simply use the src__ prefix when calling that dimension. For example, we can filter by the original Source amount dimension of the opportunity by using the src__amount dimension, as in this example:

- name: won_new_business_opportunities
  entity: opportunity
  operation: count
  filters:
    - sql: $stage_name = 'Closed Won' ## Normalized dimension
    - sql: $src__amount > 5000 ## Source dimension

Filter examples

This section includes example metrics that use different filter types.

Entity dimensions

Use multiple entity dimension filters to find the number of won, new business opportunities that are valued at more than $1000:

- name: new_won_opportunities_above_10000
  entity: opportunity
  operation: count
  filters:
    - sql: $is_won = true
    - sql: $src__type = 'New Business'
    - sql: $amount > 1000

Joined dimensions

Filter by the value of dimensions in joined tables to find the number of accounts with any won opportunities:

- name: won_accounts
  entity: account
  operation: count
  filters:
    - sql: $opportunities.is_won = true

Period relative

Compare date dimensions to the period in order to count all opportunities with a close_date within each period:

- name: opportunities_in_the_period
  entity: opportunity
  operation: count
  filters:
    - sql: $close_date <= $period.end and $close_date > $period.start

Metric value

Use metrics as filters in other metrics to quickly add additional conditions or perform different operations on the same data. In this example, we use the new_won_opportunities_above_10000 to build a more specific metric counting all new won opportunities valued at over 10000, in the EMEA region.

- name: new_won_opportunities_above_10000_in_EMEA
  entity: opportunity
  operation: count
  filters:
    - sql: $metric__new_won_opportunities_above_10000 is not null
    - sql: $region = 'EMEA'