Create advanced filters

Advanced filters are expression-based filters that can be used to create a custom filter at the dataset or card level. Advanced filters include access to the same operators (for example, +, ÷, >, and <>) and functions as calculated fields.

Note:

Advanced filters are not available for certain remote feature layers. If your remote feature layer does not support advanced filters, you can copy the layer to your workbook and apply an advanced filter to the copy.

Advanced filter expressions may appear incorrect when using mixed left-to-right and right-to-left characters within the same expression. For example, an expression using field names in both English and Arabic may not display properly. The results of the expression are not affected by the mixed left-to-right and right-to-left characters.

Advanced filters can be useful in situations in which the query is complicated, or if some of the information is unknown or needs to be calculated. The following are examples of how you can use advanced filters:

• You have a dataset with schools across the United States, but you want to perform analysis on schools in Dallas County, Texas. A regular filter for Dallas County returns schools in Alabama, Iowa, and Texas. An advanced filter using the expression County="Dallas" AND State="Texas" would filter the dataset so that only schools in Dallas County, Texas, are returned.
• You have a dataset with store locations, revenue, and expenses, but you want to see only the stores that are losing money. An advanced filter using the expression Revenue<Expenses would filter the dataset so that only stores with revenue less than expenses are returned.
• You have a dataset with hydrant locations, inspection dates, and inspection status over several years, but you only want to see the inspections for damaged hydrants that occurred in 2018 and after. An advanced filter using the expression InspectionDate>=DATE(2018,01,01)AND Status="Damaged" would filter the dataset so that only the damaged assessments occurring after January 1, 2018, will be shown.

  Note:

  A date function, such as DATE(), DATEVALUE(), or NOW(), must be used in an advanced filter expression using a date/time field. A date entered as a string value (for example, "01/01/2018" for January 1, 2018) will not be read as a date value and will not return results.

Apply an advanced filter at the dataset level

Use the following steps to apply an advanced filter at the dataset level:

1. Find the dataset you want to apply the filter to in the data pane.
2. Click the Dataset options button next to the dataset.
3. On the dataset options menu, click Advanced filter.
4. Click inside the Custom filter expression box to display the available fields and functions, and begin writing your expression.
5. At the bottom of the Custom filter expression box, look for the green check mark indicating that the expression is valid.

   Note:

   Your expression must include a conditional operator, such as <, =, or <>, to be valid for filtering (for example, Revenue-Expenses>100000).

6. Click Apply.

The expression is applied to the entire dataset. Any cards created using the dataset are refreshed and updated.

Apply an advanced filter at the card level

Use the following steps to apply an advanced filter at the card level:

1. Create a map, chart, or table using the dataset you want to filter.
2. Click the Card filter button on the card's toolbar.
3. In the New filter pane, click Advanced.
4. Click inside the Custom filter expression box to display the available fields and functions, and begin writing your expression.
5. At the bottom of the Custom filter expression box, look for the green check mark indicating that the expression is valid.

   Note:

   Your expression must include a conditional operator, such as <, =, or <>, to be valid for filtering (for example, Revenue-Expenses>100000).

6. Click Apply.

The expression is applied only to the card being filtered. Other cards that use the same dataset are not updated.

Note:

The active Card filter button displays the number of filters on the card, including advanced filters. When an advanced card filter is created, a result dataset will also be added to the data pane with the same filter applied to it at the dataset level. If new cards are created using the result dataset, all of the cards will reference the dataset filter on the result dataset and the card filter will be removed from the original card. Since the original card also references the result dataset, there will be no change to the data being displayed.

Remove or update an advanced filter

Use the following steps to update an advanced dataset filter:

1. Click the Dataset options button for the dataset that has an advanced filter applied.
2. Click Advanced filter in the dataset options menu.
3. Do one of the following:
   • Change the filter expression and click Apply to update the filter.
   • Click the Remove filter button to delete the filter.

Use the following steps to update an advanced card filter:

1. Click the active Card filter button .
2. Click the filter that you want to update.
3. Do one of the following:
   • Change the filter values and click Apply to update the filter.
   • Click the Remove filter button to delete the filter.

Expression examples

The following table provides examples of the types of expressions that can be used in advanced filters:

Expression	Description
DATEDIF(date_field, NOW(), "D")<=28	Filters the data to entries from the past 28 days.
number_field>AVG(number_field)	Filters the data to rows where the value of number_field is greater than average.
number_field_1>number_field_2	Filters the data to rows where the value of number_field_1 is greater than the value of number_field_2.
number_field<10 OR number_field>20	Filters the data so that the middle values (in this case, numbers between 10 and 20) are not included.
string_field IS NOT NULL	Filters the data to only include rows where the field contains non-null values.