Create query filters

To limit the records included in query results, such as based on the region or time period, you can define its filters in Builder mode. You can filter the results based on:

• A source column's data
• A dataset's tag value
• A calculation applied to multiple columns' data

Note: In SQL Editor, define the query's filters as SQL syntax. To reference a parameter in the SQL, enter its name after a colon (:), such as :ReportingPeriod.

Filter by a source column's data

To filter a query's results based on a source column's data:

1. From the Sources left panel, add the source to the query, and move its column to the Filters tab.
2. From the Field Properties panel, adjust the type or format of the filter's values as necessary, and click Apply.
   
   
   • In Type, select the data type to use with the filter's values. For example, to filter numerical Text data as whole numbers, select Integer.
   • For a Date or Timestamp filter, in Function, define how to format its values:
     • To truncate the date or timestamp, such as to just its Day or Quarter, choose the unit to display.
     • To use a format other than the default ISO 8601, select Custom Format, and select the new format. To set your own, select Custom, define how to format values, and click Apply.
3. On the Filters tab, select the filter's value field.
4. On the Filter Properties panel, set the filter criteria:
   • To use a static value, enter it in Value.
   • To use a parameter value, click Select parameter , and select the query or global parameter to use. To add a new parameter to the query, select Create new parameter.
   • To derive the value from a calculation, enable Calculated, and compose the calculation as Structured Query Language (SQL).
5. On the Filters tab, select the filter's operator.

   Operator	Details
   Equal To, =, Not Equal To, or !=	For a Text, Decimal, or Integer, matches records based on whether their column has the value exactly
   On Date or Not On Date	For a Date or Timestamp, matches records based on whether their column has the date exactly
   > or <	For a Decimal or Integer, matches records based on whether their column has a larger or smaller value, respectively
   After Date or Before Date	For a Date or Timestamp, matches records based on whether their column has a later or earlier date, respectively
   >= or <=	For a Decimal or Integer, matches records based on whether their column has the value exactly, or a larger or smaller value, respectively
   After or On Date or Before or On Date	For a Date or Timestamp, matches records based on whether their column has the date exactly, or a later or earlier date, respectively
   Like	For Text, matches records when their column has a similar value, based on wildcards. For example: %gland% return records that contain “gland” in any form, such as England, England’s, and gland. _ngland returns records with “ngland” in that exact placement, such as England, but not Rengland.
   Is Null or Is Not Null	Matches records based on whether their column has any value
   Is In List or Is Not In List	Matches records based on whether their column has a value from a list of options. If you select this operator, under List Options, enter each option on its own line. For a Date or Timestamp, enter each option in the YYYY-MM-dd format. Note: Select Is In List or Is Not In List to use a multi-select parameter as the filter's criteria.

6. Click Save .

Filter by a calculation applied to multiple columns

To filter the results based on a calculation applied to multiple columns, set up a formula with an expression that includes:

• Operators, such as {1} + {2} to calculate the sum of columns' values
• Functions, such as concat({1}, {2}) to generate a string of columns' values

1. From the Sources left panel, add the sources to include in the calculation to the query.
2. Move Calculation from Sources to the Filters tab.
3. From the Field Properties panel, under Included Columns, add the source columns to use in the calculation.
4. Under Calculation, enter the formula or expression to calculate using SQL operators and functions. In the formula, represent the included columns as their assigned numerical token, such as {1} + {2}.

   Note: To quickly add a column's numerical token to the calculation, click its name under Included Columns.

5. Click Apply.
6. On the Filters tab, select the filter's value, and then, from the Filter Properties panel, select the data type to use with the filter's values, such as Integer or Decimal.
   
7. Define the criteria of the records to include, and click Apply:
   • To include records based on a static value, enter it in the Value field.
   • To use a parameter value, click Select parameter , and select the query or global parameter to use. To add a new parameter to the query, select Create new parameter.

Filter by a source table's tags

With Wdata tables, your organization can set up tags to track details about datasets, such as Version, Source, or Scenario. To filter a query's results based on this detail:

1. From the Sources left panel, add the table as a query source.
2. Select which of the table's tags to filter by:
   
   • Move the tag to filter from under Tags to the Filters tab.
   • Move Tags to the Filters tab, and then select the tag's name from the Field Properties panel and click Apply.
3. On the Filters tab, select the filter's operator:
   
   

   Operator	Details
   Equal To or Not Equal To	Matches records based on whether the tag has an exact value
   Like or Not Like	With a value that contains wildcard characters, matches records based on whether the tag has a similar value. For example: %gland% return records that contain “gland” in any form, such as England, England’s, and gland. _ngland returns records with “ngland” in that exact placement, such as England, but not Rengland.
   Is Null or Is Not Null	Matches records based on whether the tag has any value
   Is In List or Is Not In List	Matches records based on whether the tag has one of multiple values

4. Select the filter's value field, and, from the Filter Properties panel, specify and apply the tag values to filter by:
   • To use the tag's configured values, select them in Value.
   • To use a parameter value, click Select parameter , and select the query or global parameter to use. To add a new parameter to the query, select Create new parameter.
   • To derive the value from a calculation, enable Calculated, and compose the calculation as Structured Query Language (SQL).
   • To use another value, such as a custom value with wildcards for a Like operator, enter it in Value.
5. Click Apply and Save .

Adjust the filters' formula

By default, the query matches records that meet all of its filters' criteria. To match records that meet a combination of the filters, use AND and OR operators in the formula bar on the Filters tab to define the filters' cumulative criteria:

• To group filters together, wrap them in parentheses, such as ({1} AND {2}) OR ({3} AND {4}).
• To match records that meet both of two filters or groups, separate them with an AND, such as {1} AND {2}.
• To match records that meet either of two filters or groups, separate them with an OR, such as {2} OR {3}.