# Filtering data in Amazon Quick Sight
Filtering data

You can use filters to refine the data in a dataset or an analysis. For example, you can create a filter on a region field that excludes data from a particular region in a dataset. You can also add a filter to an analysis, such as a filter on the range of dates that you want to include in any visuals in your analysis.

When you create a filter in a dataset, that filter applies to the entire dataset. Any analyses and subsequent dashboards created from that dataset contains the filter. If someone creates a dataset from your dataset, the filter also is in the new dataset.

When you create a filter in an analysis, that filter only applies to that analysis and any dashboards you publish from it. If someone duplicates your analysis, the filter persists in the new analysis. In analyses, you can scope filters to a single visual, some visuals, all visuals that use this dataset, or all applicable visuals.

Also, when you create filters in an analysis, you can add a filter control to your dashboard. For more information about filter controls, see [Adding filter controls to analysis sheets](filter-controls.md).

Each filter you create applies only to a single field. You can apply filters to both regular and calculated fields.

There are several types of filters you can add to datasets and analyses. For more information about the types of filters you can add, and some of their options, see [Filter types in Amazon Quick](filtering-types.md).

If you create multiple filters, all top-level filters apply together using AND. If you group filters by adding them inside a top-level filter, the filters in the group apply using OR.

Amazon Quick Sight applies all of the enabled filters to the field. For example, suppose that there is one filter of `state = WA` and another filter of `sales >= 500`. Then the dataset or analysis only contains records that meet both of those criteria. If you disable one of these, only one filter applies.

Take care that multiple filters applied to the same field aren't mutually exclusive.

Use the following sections to learn how to view, add, edit, and delete filters.

**Topics**
+ [

# Viewing existing filters
](viewing-filters-data-prep.md)
+ [

# Adding filters
](add-a-filter-data-prep.md)
+ [

# Cross-sheet filters and controls
](cross-sheet-filters.md)
+ [

# Filter types in Amazon Quick
](filtering-types.md)
+ [

# Adding filter controls to analysis sheets
](filter-controls.md)
+ [

# Editing filters
](edit-a-filter-data-prep.md)
+ [

# Enabling or disabling filters
](disable-a-filter-data-prep.md)
+ [

# Deleting filters
](delete-a-filter-data-prep.md)

# Viewing existing filters


When you edit a dataset or open an analysis, you can view any existing filters that were created. Use the following procedures to learn how.

## Viewing filters in datasets


1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Data** at left.

1. In the **Datasets** tab, choose the dataset that you want, and then choose **Edit dataset**.

1. On the data preparation page that opens, choose **Filters** at lower left to expand the **Filters** section.

   Any filters that are applied to the dataset appear here. If a single field has multiple filters, they are grouped together. They display in order of create date, with the oldest filter on top.

## Viewing filters in analyses


Use the following procedure to view filters in analyses.

**To view a filter in an analysis**

1. From the Quick homepage, choose **Analyses**.

1. On the **Analyses** page, choose the analysis that you want to work with.

1. In the analysis, choose the **Filter** icon to open the **Filters** pane.

   Any filters applied to the analysis appear here.

   The way that a filter is scoped is listed at the bottom of each filter. For more information about scoping filters, see [Adding filters](add-a-filter-data-prep.md).

# Adding filters


You can add filters to a dataset or an analysis. Use the following procedures to learn how.

## Adding filters to datasets


Use the following procedure to add filters to datasets.

**To add a filter to a dataset**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Data** at left.

1. In the **Datasets** tab, choose the dataset that you want, and then choose **Edit dataset**.

1. On the data preparation page that opens, choose **Add filter** at lower left, and then choose a field that you want to filter.

   The filter is added to the **Filters** pane.

1. Choose the new filter in the pane to configure the filter. Or you can choose the three dots to the right of the new filter and choose **Edit**.

   Depending on the data type of the field, your options for configuring the filter vary. For more information about the types of filters that you can create and their configurations, see [Filter types in Amazon Quick](filtering-types.md).

1. When finished, choose **Apply**.
**Note**  
The data preview shows you the results of your combined filters only as they apply to the first 1,000 rows. If all of the first 1,000 rows are filtered out, then no rows show in the preview. This effect occurs even when rows after the first 1,000 aren't filtered out.

## Adding filters in analyses


Use the following procedure to add filters to analyses.

**To add a filter to an analysis**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Analyses**.

1. On the **Analyses** page, choose the analysis that you want to work with.

1. In the analysis, choose the **Filter** icon to open the **Filters** pane, and then choose **ADD**.

1. Choose the new filter in the pane to configure it. Or you can choose the three dots to the right of the new filter and choose **Edit**.

1. In the **Edit filter** pane that opens, for **Applied to**, choose one of the following options.
   + **Single visual** – The filter applies to the selected item only.
   + **Single sheet** – The filter applies to a single sheet.
   + **Cross sheet** – The filter applies to multiple sheets in the dataset.

   Depending on the data type of the field, your remaining options for configuring the filter vary. For more information about the types of filters you can create and their configurations, see [Filter types in Amazon Quick](filtering-types.md).

# Cross-sheet filters and controls


Cross-sheet filters and controls are filters that are scoped to either your entire analysis or dashboard or multiple sheets within your analysis and dashboard.

## Filters


**Creating a Cross-Sheet Filter**

1. Once you have [Added a filter](https://docs.amazonaws.cn/quicksight/latest/user/add-a-filter-data-prep.html#add-a-filter-data-prep-analyses), you update the scope of the filter to cross-sheet. By default, this applies to all of the the sheets in your analysis.

1. If the **Apply cross-datasets** box is checked, then the filter will be applied to all visuals from up to 100 different datasets that are applicable to all sheets in the filter scope.

1. If you want to customize the sheets that it is applied to, then choose the Cross-sheet icon. You can then view the sheets the filter is currently applied to or toggle on the custom select sheets.

1. When you enable **Custom select sheets**, you can select which sheets to apply the filter to.

1. Follow the steps at [Editing filters in analyses](https://docs.amazonaws.cn/quicksight/latest/user/edit-a-filter-data-prep.html#edit-a-filter-data-prep-analyses). Your changes will be applied to all of the filters for all of the sheets you have selected. This includes newly added sheets if the filter is scoped to your entire analysis.

**Removing a Cross-Sheet Filter**

**Deleting**

If you have no controls created from these filters, see [Deleting filters in analyses](https://docs.amazonaws.cn/quicksight/latest/user/delete-a-filter-data-prep.html#delete-a-filter-data-prep-analyses).

If you have controls created then:

****

1. Follow the instructions at [Deleting filters in analyses](https://docs.amazonaws.cn/quicksight/latest/user/delete-a-filter-data-prep.html#delete-a-filter-data-prep-analyses).

1. If you choose **Delete Filter and Controls**, the controls will be deleted from all pages. This may impact the layout of your analysis. Alternatively, you can remove these controls individually. 

**Downscoping**

If you want to remove a cross-sheet filter, you can also do this by changing the filter scope:

****

1. Follow the instructions at [Editing filters in analyses](https://docs.aws.amazon.com/quicksight/latest/user/edit-a-filter-data-prep.html#edit-a-filter-data-prep-analyses) to get to the filter. 

1. One of the edits you can make is changing the scope. You can switch to **Single sheet** or **Single visual**. You can also remove a sheet from the Cross-sheet selection.

   Or the custom sheet selection:  
![\[This is an image of Delete Filter in Quick Sight.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/cross-sheet-7.png)

1. If there are controls, you will see a modal to warn you that you will be bulk-removing controls from any of the sheets where the filter no longer applies and this can impact your layout. You can also remove the controls individually. For more information, see [Removing a Cross-Sheet Control](#cross-sheet-removing-control).

1. If you add controls to the **Top of all sheets in filter scope** then new sheets will by default be added with this new control if the filter is scoped to your entire analysis.

## Controls


### Creating a Cross-Sheet Control


**New filter control**

1. Create a cross-sheet filter. For more information, see [Filters](#filters).

1. From the three-dot menu, you can see an option that says **Add control**. Hovering over this, you will see three options:
   + **Top of all sheets in filter scope**
   + **Top of this sheet**
   + **Inside this sheet**

   If you want to add to multiple-sheets within the sheets themselves, you can do that sheet-by-sheet. Or you can add to the top and then use the option on each control to **Move to sheet**. For more information, see [Editing a Cross-Sheet Control](#cross-sheet-controls-editing-control).

**Increasing Scope of Existing Control**

1. Navigate to the existing filter in the analysis

1. Change the scope of what sheets this filter is **Applied to** to **Cross-sheet**.

1. If there is already a control created from the filter, you will see a modal, which if you check the box will bulk-add controls to the top of all the sheets in the filter scope. This will not impact the position of the already created control if it is on the sheet.

### Editing a Cross-Sheet Control


1. Go to the cross-sheet control and select the three-dot menu if the control is pinned to the top or the edit pencil icon if the control is on the sheet. You will be presented with the following options:
   + **Go to filter** (which directs you to the cross-sheet filter for you to edit or review
   + **Move to sheet** (which moves the control into the analysis pane)
   + **Reset** 
   + **Refresh** 
   + **Edit** 
   + **Remove** 

1. Choose **Edit**. This brings up the **Format Control** pane on the right side of your analysis.

1. You can then edit your control. The top section labeled **Cross-sheet settings** will apply to all controls, whereas any settings outside of this section are not applicable to all controls and only to the specific control you’re editing. For instance, **Relevant value** is not a cross-sheet control setting. 

1. You can also see the sheets that this control is on as well as the location (Top or Sheet) that the control is on for each sheet. You can do this by choosing **Sheets(8)**.

### Removing a Cross-Sheet Control


You can remove controls in two places. First, from the control:

1. Go to the cross-sheet control and select the three-dot menu if the control is pinned to the top or the edit pencil icon if the control is on the sheet. You will be presented with the following options:
   + **Go to filter** (which directs you to the cross-sheet filter for you to edit or review
   + **Move to sheet** (which moves the control into the analysis pane)
   + **Reset** 
   + **Refresh** 
   + **Edit** 
   + **Remove** 

1. Choose **Remove**

Second, you can remove controls from the filter:

1. Choose the three-dot menu on the cross-sheet filter that the cross-sheet controls are created from. You will see that instead of an option to **Add control** there is now an option to **Manage control**.

1. Hover over **Manage control**. You will be presented with the following options:
   + **Move inside this sheet** 
   + **Top of this sheet**

   These options are applicable to just the control on the sheet, depending on where the current control is. If you don’t have controls on all of the sheets within the filter scope, you will get the option to **Add to top of all sheets in filter scope**. This will not move sheet controls to the top of the sheet if you have already added them to the sheet in the analysis. You will also get the option to **Remove from this sheet** or **Remove from all sheets**.

# Filter types in Amazon Quick
Filter types

You can create several different types of filters in Quick. The type of filter you create mostly depends on the data type of the field that you want to filter.

In datasets, you can create the following types of filters:
+ Text filters
+ Numeric filters
+ Date filters

In analyses, you can create the same types of filters as you can in datasets. You can also create:
+ Group filters with and/or operators
+ Cascading filters
+ Nested filters

Use the following sections to learn more about each type of filter you can create and some of their options.

**Topics**
+ [

# Adding text filters
](add-a-text-filter-data-prep.md)
+ [

# Adding nested filters
](add-a-nested-filter-data-prep.md)
+ [

# Adding numeric filters
](add-a-numeric-filter-data-prep.md)
+ [

# Adding date filters
](add-a-date-filter2.md)
+ [

# Adding filter conditions (group filters) with AND and OR operators
](add-a-compound-filter.md)
+ [

# Creating cascading filters
](use-a-cascading-filter.md)

# Adding text filters
Text filters

When you add a filter using a text field, you can create the following types of text filters:
+ **Filter list** (Analyses only) – This option creates a filter that you can use to select one or more field values to include or exclude from all the available values in the field. For more information about creating this type of text filter, see [Filtering text field values by a list (analyses only)](#text-filter-list).
+ **Custom filter list** – With this option, you can enter one or more field values to filter on, and whether you want to include or exclude records that contain those values. The values that you enter must match the actual field values exactly for the filter to be applied to a given record. For more information about creating this type of text filter, see [Filtering text field values by a custom list](#add-text-custom-filter-list-data-prep).
+ **Custom filter** – With this option, you enter a single value that the field value must match in some way. You can specify that the field value must equal, not equal, starts with, ends with, contains, or does not contain the value you specify. If you choose an equal comparison, the specified value and actual field value must match exactly in order for the filter to be applied to a given record. For more information about creating this type of text filter, see [Filtering a single text field value](#add-text-filter-custom-list-data-prep).
+ **Top and bottom filter** (Analyses only) – You can use this option to show the top or bottom *n* value of one field ranked by the values in another field. For example, you might show the top five salespeople based on revenue. You can also use a parameter to allow dashboard users to dynamically choose how many top or bottom ranking values to show. For more information about creating top and bottom filters, see [Filtering a text field by a top or bottom value (analyses only)](#add-text-filter-top-and-bottom).

## Filtering text field values by a list (analyses only)
Filter list

In analyses, you can filter a text field by selecting values to include or exclude from a list of all value in the field.

**To filter a text field by including and excluding values**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Filter type**, choose **Filter list**.

1. For **Filter condition**, choose **Include** or **Exclude**.

1. Choose the field values that you want to filter on. To do this, select the check box in front of each value.

   If there are too many values to choose from, enter a search term into the box above the checklist and choose **Search**. Search terms are case-insensitive and wildcards aren't supported. Any field value that contains the search term is returned. For example, searching on L returns al, AL, la, and LA.

   The values display alphabetically in the control, unless there are more than 1,000 distinct values. Then the control displays a search box instead. Each time that you search for the value that you want to use, it starts a new query. If the results contain more than 1,000 values, you can scroll through the values with pagination.

1. When finished, choose **Apply**.

## Filtering text field values by a custom list
Custom filter list

You can specify one or more field values to filter on, and whether you want to include or exclude records that contain those values. The specified value and actual field value must match exactly for the filter to be applied to a given record.

**To filter text field values by a custom list**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Filter type**, choose **Custom filter list**.

1. For **Filter condition**, choose **Include** or **Exclude**.

1. For **List**, enter a value in the text box. The value must match an existing field value exactly.

1. (Optional) To add additional values, enter them in the text box, one per line.

1. For **Null options** choose **Exclude nulls**, **Include nulls**, or **Nulls only**.

1. When finished, choose **Apply**.

## Filtering a single text field value
Custom filter

With the **Custom filter** filter type, you specify a single value that the field value must equal or not equal, or must match partially. If you choose an equal comparison, the specified value and actual field value must match exactly for the filter to be applied to a given record.

**To filter a text field by a single value**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Filter type**, choose **Custom filter**.

1. For **Filter condition**, choose one of the following:
   + **Equals** – When you choose this option, the values included or excluded in the field must match the value that you enter exactly.
   + **Does not equal** – When you choose this option, the values included or excluded in the field must match the value that you enter exactly.
   + **Starts with** – When you choose this option, the values included or excluded in the field must start with the value that you enter.
   + **Ends with** – When you choose this option, the values included or excluded in the field must start with the value that you enter.
   + **Contains** – When you choose this option, the values included or excluded in the field must contain the whole value that you enter.
   + **Does not contain** – When you choose this option, the values included or excluded in the field must not contain any part of the value that you enter.
**Note**  
Comparison types are case-sensitive.

1. Do one of the following:
   + For **Value**, enter a literal value.
   + Select **Use parameters** to use an existing parameter, and then choose a parameter from the list.

     For parameters to appear in this list, create your parameters first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see [Parameters in Amazon Quick](parameters-in-quicksight.md).

     The values display alphabetically in the control, unless there are more than 1,000 distinct values. Then the control displays a search box instead. Each time that you search for the value that you want to use, it starts a new query. If the results contain more than 1,000 values, you can scroll through the values with pagination.

1. For **Null options** choose **Exclude nulls**, **Include nulls**, or **Nulls only**.

1. When finished, choose **Apply**.

## Filtering a text field by a top or bottom value (analyses only)
Top and bottom

You can use a **Top and bottom filter** to show the top or bottom *n* value of one field ranked by the values in another field. For example, you might show the top five salespeople based on revenue. You can also use a parameter to allow dashboard users to dynamically choose how many top or bottom ranking values to show.

**To create a top and bottom text filter**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Filter type**, choose **Top and bottom filter**.

1. Choose **Top** or **Bottom**.

1. For **Show top** integer (or **Show bottom** integer), do one of the following:
   + Enter the number of top or bottom items to show.
   + To use a parameter for the number of top or bottom items to show, select **Use parameters**. Then choose an existing integer parameter. 

     For example, let's say that you want to show the top three salespersons by default. However, you want the dashboard viewer to be able to choose whether to show 1–10 top salespersons. In this case, take the following actions:
     + Create an integer parameter with a default value. 
     + To link the number of displayed items to a parameter control, create a control for the integer parameter. Then you make the control a slider with a step size of 1, a minimum value of 1, and a maximum value of 10. 
     + To make the control work, link it to a filter by creating a top and bottom filter on `Salesperson` by `Weighted Revenue`, enable **Use parameters**, and choose your integer parameter. 

1. For **By**, choose a field to base the ranking on. If you want to show the top five salespeople per revenue, choose the revenue field. You can also set the aggregate that you want to perform on the field.

1. (Optional) Choose **Tie breaker** and then choose another field to add one or more aggregations as tie breakers. This is useful, in the case of this example, when there are more than five results returned for the top five salespeople per revenue. This situation can happen if multiple salespeople have the same revenue amount. 

   To remove a tie breaker, use the delete icon.

1. When finished, choose **Apply**.

# Adding nested filters
Nested filters

Nested filters are advanced filters that can be added to a Quick analysis. A Nested filter filters a field using a subset of data defined by another field in that same dataset. This allows authors to show additional contextual data without the need to filter data out if the data point doesn't meet an initial condition.

Nested filters function similarly to a correlated subquery in SQL or a market basket analysis. For example, say you want to perform a market basket analysis on your sales data. You can use nested filters to find the sales quantity by product for customers who have or have not purchased a specific product. You can also use nested filters to identify groups of customers that did not purchase a selected product or who only purchased a specific list of products.

Nested filters can only be added at the analysis level. You can't add a nested filter to a dataset.

Use the procedure below to add a nested filter to a Quick analysis.

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. Choose **Analyses**, and then choose the analysis that you want to add a nested filter to.

1. Create a new filter on the text field that you want to filter on. For more information about creating a filter, see [Adding filters in analyses](add-a-filter-data-prep.md#add-a-filter-data-prep-analyses).

1. After you create the new filter, locate the new filter in the **Filters** pane. Choose the ellipsis (three dots) next to the new filter, and then choose **Edit filter**. Alternatively, choose the filter entity in the **Filters** pane to open the **Edit filter** pane.

1. The **Edit filter** pane opens. Open the **Filter type** dropdown menu, navigate to the **Avanced filter** section, and then choose **Nested filter**.

1. For **Qualifying condition**, choose **Include** or **Exclude**. The *qualifying condition* allows you to run a not in the set query on the data in your analysis. In our sales example above, the qualifying condition determines if the filter returns a list of customers who did buy the specifc product or a list of customers who did not buy the product.

1. For **Nested field**, choose the text field that you want to filter data with. The nested field cannot be the same as the primary field selected in step 3. Category fields are the only supported field type for the inner filter.

1. For **Nested filter type**, choose the filter type that you want. The filter type that you choose determines the final configuration steps for the nested filter. Available filter types and information about their configuration can be found in the list below.
   + [Filter list](https://docs.amazonaws.cn/quicksuite/latest/userguide/text-filter-list)
   + [Custom filter list](https://docs.amazonaws.cn/quicksuite/latest/userguide/add-text-custom-filter-list-data-prep)
   + [Custom filter](https://docs.amazonaws.cn/quicksuite/latest/userguide/add-text-filter-custom-list-data-prep)

# Adding numeric filters
Numeric filters

Fields with decimal or int data types are considered numeric fields. You create filters on numeric fields by specifying a comparison type, for example **Greater than** or **Between**, and a comparison value or values as appropriate to the comparison type. Comparison values must be positive integers and can't contain commas.

You can use the following comparison types in numeric filters:
+ Equals
+ Does not equal
+ Greater than
+ Greater than or equal to
+ Less than
+ Less than or equal to
+ Between

**Note**  
To use a top and bottom filter for numeric data (analyses only), first change the field from a measure to a dimension. Doing this converts the data to text. Then you can use a text filter. For more information, see [Adding text filters](add-a-text-filter-data-prep.md).

In analyses, for datasets based on database queries, you can also optionally apply an aggregate function to the comparison value or values, for example **Sum** or **Average**. 

You can use the following aggregate functions in numeric filters:
+ Average
+ Count
+ Count distinct
+ Max
+ Median
+ Min
+ Percentile
+ Standard deviation
+ Standard deviation - population
+ Sum
+ Variance
+ Variance - population

## Creating numeric filters


Use the following procedure to create a numeric field filter.

**To create a numeric field filter**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. (Optional) For **Aggregation**, choose an aggregation. No aggregation is applied by default. This option is available only when creating numeric filters in an analysis.

1. For **Filter condition**, choose a comparison type.

1. Do one of the following:
   + If you chose a comparison type other than **Between**, enter a comparison value.

     If you chose a comparison type of **Between**, enter the beginning of the value range in **Minimum value** and the end of the value range in **Maximum value**.
   + (Analyses only) To use an existing parameter, enable **Use parameters**, then choose your parameter from the list.

     For parameters to appear in this list, create your parameters first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see [Parameters in Amazon Quick](parameters-in-quicksight.md). The values display alphabetically in the control, unless there are more than 1,000 distinct values. Then the control displays a search box instead. Each time you search for the value that you want to use, it initiates a new query. If the results contain more than 1,000 values, you can scroll through the values with pagination. 

1. (Analyses only) For **Null options** choose **Exclude nulls**, **Include nulls**, or **Nulls only**.

1. When finished, choose **Apply**.

# Adding date filters
Date filters

You create filters on date fields by selecting the filter conditions and date values that you want to use. There are three filter types for dates:
+ **Range** – A series of dates based on a time range and comparison type. You can filter records based on whether the date field value is before or after a specified date, or within a date range. You enter date values in the format MM/DD/YYYY. You can use the following comparison types:
  + **Between** – Between a start date and an end date
  + **After** – After a specified date
  + **Before** – Before a specified date
  + **Equals** – On a specified date

  For each comparison type, you can alternatively choose a rolling date relative to a period or dataset value.
+ **Relative** (analyses only) – A series of date and time elements based on the current date. You can filter records based on the current date and your selected unit of measure (UOM). Date filter units include years, quarters, months, weeks, days, hours, and minutes. You can exclude current period, add support for Next N filters similar to Last N with an added capability to allow for Anchor date. You can use the following comparison types:
  + **Previous** – The previous UOM—for example, the previous year.
  + **This** – This UOM, which includes all dates and times that fall within the select UOM, even if they occur in the future.
  + **To date *or* up to now** – UOM to date, or UOM up to now. The displayed phrase adapts to the UOM that you choose. However, in all cases this option filters out data that is not between the beginning of the current UOM and the current moment.
  + **Last *n*** – The last specified number of the given UOM, which includes all of this UOM and all of the last *n * −1 UOM. For example, let's say today is May 10, 2017. You choose to use *years* as your UOM, and set Last *n *years to 3. The filtered data includes data for all of 2017, plus all of 2016, and all of 2015. If you have any data for the future dates of the current year (2017 in this example), these records are included in your dataset.
+ **Top and bottom** (analyses only) – A number of date entries ranked by another field. You can show the top or bottom *n* for the type of date or time UOM you choose, based on values in another field. For example, you can choose to show the top 5 sales days based on revenue.

Comparisons are applied inclusive to the date specified. For example, if you apply the filter `Before 1/1/16`, the records returned include all rows with date values through 1/1/16 23:59:59. If you don't want to include the date specified, you can clear the option to **Include this date**. If you want to omit a time range, you can use the **Exclude the last N periods** option to specify the number and type of time periods (minutes, days, and so on) to filter out.

You can also choose to include or exclude nulls, or exclusively show rows that contain nulls in this field. If you pass in a null date parameter (one without a default value), it doesn't filter the data until you provide a value.

**Note**  
If a column or attribute has no time zone information, then the client query engine sets the default interpretation of that date-time data. For example, suppose that a column contains a timestamp, rather than a timestamptz, and you are in a different time zone than the data's origin. In this case, the engine can render the timestamp differently than you expect. Amazon Quick and [SPICE](spice.md) both use Universal Coordinated Time (UTC) times. 

Use the following sections to learn how to create date filters in datasets and analyses.

## Creating date filters in datasets


Use the following procedure to create a range filter for a date field in a dataset.

**To create a range filter for a date field in a dataset**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Condition**, choose a comparison type: **Between**, **After**, or **Before**.

   To use **Between** as a comparison, choose **Start date** and **End date** and choose dates from the date picker controls that appear.

   You can choose if you want to include either or both the start and end dates in the range by selecting **Include start date** or **Include end date**.

   To use **Before** or **After** comparisons, enter a date or choose the date field to bring up the date picker control and choose a date instead. You can include this date (the one you chose), to exclude the last N time periods, and specify how to handle nulls. 

1. For **Time granularity**, choose **Day**, **Hour**, **Minute**, or **Second**.

1. When finished, choose **Apply**.

## Creating date filters in analyses


You can create date filters in analyses as described following.

### Creating range date filters in analyses


Use the following procedure to create a range filter for a date field in an analysis.

**To create a range filter for a date field in an analysis**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Filter type**, choose **Date & time range**.

1. For **Condition**, choose a comparison type: **Between**, **After**, **Before**, or **Equals**.

   To use **Between** as a comparison, choose **Start date** and **End date** and choose dates from the date picker controls that appear.

   You can choose to include either or both the start and end dates in the range by selecting **Include start date** or **Include end date**.

   To use a **Before**, **After**, or **Equals** comparison, enter a date or choose the date field to bring up the date picker control and choose a date instead. You can include this date (the one you chose), to exclude the last N time periods, and specify how to handle nulls. 

   To **Set a rolling date** for your comparison, choose **Set a rolling date**.

   In the **Set a rolling date** pane that opens, choose **Relative date** and then select if you want to set the date to **Today**, **Yesterday**, or you can specify the **Filter condition** (start of or end of), **Range** (this, previous, or next), and **Period** (year, quarter, month, week, or day).

1. For **Time granularity**, choose **Day**, **Hour**, **Minute**, or **Second**.

1. (Optional) If you are filtering by using an existing parameter, instead of specific dates, choose **Use parameters**, then choose your parameter or parameters from the list. To use **Before**, **After**, or **Equals** comparisons, choose one date parameter. You can include this date in the range.

   To use **Between**, enter both the start date and end date parameters separately. You can include the start date, the end date, or both in the range. 

   To use parameters in a filter, create them first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see [Parameters in Amazon Quick](parameters-in-quicksight.md).

1. For **Null options** choose **Exclude nulls**, **Include nulls**, or **Nulls only**.

1. When finished, choose **Apply**.

### Creating relative date filters in analyses


Use the following procedure to create a relative filter for a date field in an analysis.

**To create a relative filter for a date field in an analysis**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Filter type**, choose **Relative dates**.

1. For **Time granularity**, choose a granularity of time that you want to filter by (days, hours, minutes).

1. For **Period**, choose a unit of time (years, quarters, quarters, months, weeks, days).

1. For **Range**, choose how you want the filter to relate to the time frame. For example, if you choose to report on months, your options are previous month, this month, month to date, last N months, and next N months.

   If you choose Last N or Next N years, quarters, months, weeks, or days, enter a number for **Number of**. For example, last 3 years, next 5 quarters, last 5 days.

1. For **Null options** choose **Exclude nulls**, **Include nulls**, or **Nulls only**.

1. For **Set dates relative to**, choose one of the following options:
   + **Current date time** – If you choose this option, you can set it to **Exclude last**, and then specify the number and type of time periods.
   + **Date and time from a parameter** – If you choose this option, you can select an existing datetime parameter.

1. (Optional) If you are filtering by using an existing parameter, instead of specific dates, enable **Use parameters**, then choose your parameter or parameters from the list. 

   To use parameters in a filter, create them first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see [Parameters in Amazon Quick](parameters-in-quicksight.md).

1. When finished, choose **Apply**.

### Creating top and bottom date filters in analyses


Use the following procedure to create a top and bottom filter for a date field in an analysis.

**To create a top and bottom filter for a date field in an analysis**

1. Create a new filter using a text field. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. For **Filter type**, choose **Top and bottom**.

1. Select **Top** or **Bottom**.

1. For **Show**, enter the number of top or bottom items you want to show and choose a unit of time (years, quarters, months, weeks days, hours, minutes). 

1. For **By**, choose a field to base the ranking on.

1. (Optional) Add another field as a tie breaker, if the field for **By** has duplicates. Choose **Tie breaker**, and then choose another field. To remove a tie breaker, use the delete icon.

1. (Optional) If you are filtering by using an existing parameter, instead of specific dates, select **Use parameters**, then choose your parameter or parameters from the list.

   To use a parameter for **Top and bottom**, choose an integer parameter for the number of top or bottom items to show. 

   To use parameters in a filter, create them first. Usually, you create a parameter, add a control for it, and then add a filter for it. For more information, see [Parameters in Amazon Quick](parameters-in-quicksight.md).

1. When finished, choose **Apply**.

# Adding filter conditions (group filters) with AND and OR operators
Filter conditions

In analyses, when you add multiple filters to a visual, Quick uses the AND operator to combine them. You can also add filter conditions to a single filter with the OR operator. This is called a compound filter, or filter group.

To add multiple filters using the OR operator, create a filter group. Filter grouping is available for all types of filters in analyses. 

When you filter on multiple measures (green fields marked with \$1), you can apply the filter conditions to an aggregate of that field. Filters in a group can contain either aggregated or nonaggregated fields, but not both. 

**To create a filter group**

1. Create a new filter in an analysis. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the new filter to expand it.

1. In the expanded filter, choose **Add filter condition** at bottom, and then choose a field to filter on. 

1.  Choose the conditions to filter on. 

   The data type of the field that you selected determines the options available here. For example, if you chose a numeric field, you can specify the aggregation, filter condition, and values. If you chose a text field, you can chose the filter type, filter condition, and values. And if you chose a date field, you can specify the filter type, condition, and time granularity. For more information about these options, see [Filter types in Amazon Quick](filtering-types.md).

1.  (Optional) You can add additional filter conditions to the filter group by choosing **Add filter condition** again at bottom.

1.  (Optional) To remove a filter from the filter group, choose the trash-can icon near the field name. 

1. When finished, choose **Apply**.

   The filters appear as a group in the **Filters** pane.

# Creating cascading filters
Cascading filters

The idea behind cascading any action, such as a filter, is that choices in the higher levels of a hierarchy affect the lower levels of a hierarchy. The term *cascading* comes from the way that a cascade waterfall flows from one tier to the next. 

To set up cascading filters, you need a trigger point where the filter is activated, and target points where the filter is applied. In Quick, the trigger and target points are included in visuals.

To create a cascading filter, you set up an action, not a filter. This approach is because you need to define how the cascading filter is activated, which fields are involved, and which visuals are filtered when someone activates it. For more information, including step-by-step instructions, see [Using custom actions for filtering and navigating](quicksight-actions.md).

There are two other ways to activate a filter across multiple visuals:
+ **For a filter that is activated from a widget on a dashboard ** – The widget is called a *sheet control,* which is a custom menu that you can add to the top of your analysis or dashboard. The most common sheet control is a drop-down list, which displays a list of options to choose from when you open it. To add one of these to your analysis, create a parameter, add a control to the parameter, and then add a filter that uses the parameter. For more information, see [Setting up parameters in Amazon Quick](parameters-set-up.md), [Using a control with a parameter in Amazon Quick](parameters-controls.md), and [Adding filter controls to analysis sheets](filter-controls.md).
+ **For a filter that always applies to multiple visuals ** – This is a regular filter, except that you set its scope to apply to multiple (or all) visuals. This type of filter doesn't really cascade, because there is no trigger point. It always filters all the visuals that it's configured to filter. To add this type of filter to your analysis, create or edit a filter and then choose its scope: **Single visual**, **Single sheet**, or **Cross sheets**. Note the option to **Apply cross-datasets**. If this box is checked, then the filter will be applied to all visuals from different datasets that are applicable on all sheets in the filter scope. For more information, see [Filters](cross-sheet-filters.md#filters). 

# Adding filter controls to analysis sheets
Adding filter controls

When you're designing an analysis, you can add a filter to the analysis sheet near the visuals that you want to filter. It appears in the sheet as a control that dashboard viewers can use when you publish the analysis as a dashboard. The control uses the analysis theme settings so it looks like it's part of the sheet.

Filter controls share some settings with their filters. They apply to one, some, or all of the objects on the same sheet.

Use the following sections add and customize filter controls to an analysis. To learn how to add cross-sheet controls, see [Controls](cross-sheet-filters.md#cross-sheet-controls).

**Topics**
+ [

## Adding filter controls
](#filter-controls-add)
+ [

## Pinning filter controls to the top of a sheet
](#filter-controls-pin)
+ [

## Customizing filter controls
](#filter-controls-customize)
+ [

## Cascading filter controls
](#cascading-controls)

## Adding filter controls


Use the following procedure to add a filter control.

**To add a filter control**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Analyses**, and then choose the analysis that you want to work with.

1. In the analysis, choose **Filter**.

1. If you don't already have some filters available, create one. For more information about creating filters, see [Adding filters](add-a-filter-data-prep.md).

1. In the **Filters** pane, choose the three dots to the right of the filter that you want to add a control for, and choose **Add to sheet**.

   The filter control is added to the sheet, usually at the bottom. You can resize it or drag it to different positions on the sheet. You can also customize how it appears and how dashboard viewers can interact with it. For more information about customizing filter controls, see the following sections.

## Pinning filter controls to the top of a sheet


Use the following procedure to pin filter controls to the top of a sheet.

**To pin a control to the top of a sheet**

1. On the filter control that you want to move, choose the three dots next to the pencil icon and choose **Pin to top**.

   The filter is pinned to the top of the sheet and is collapsed. You can click it to expand it.

1. (Optional) To unpin the control, expand it and hover over it at the top of the sheet until three dots appear. Choose the three dots, and then choose **Move to sheet**.

## Customizing filter controls


Depending on the data type of the field and the type of filter, filter controls have different settings available. You can customize how they appear in the sheet and how dashboard viewers can interact with them. 

**To customize a filter control**

1. Choose the filter control in the sheet.

1. On the filter control, choose the pencil icon.

   If the filter control is pinned to the top of the sheet, expand it and hover your cursor over it until the three dots appear. Choose the three dots, and then choose **Edit**.

1. In the **Format control** pane that opens, do the following:

   1. For **Display name**, enter a name for the filter control.

   1. (Optional) To hide the display name from the filter control, clear the check box for **Show title**.

   1. For **Title font size**, choose the title font size that you want to use. The options range from extra small to extra large. The default setting is medium.

The remaining steps depend on the type of field the control is referencing. For options by filter type, see the following sections.

### Date filters


If your filter control is from a date filter, use the following procedure to customize the remaining options.

**To customize further options for a date filter**

1. In the **Format control** pane, for **Style**, choose one of the following options:
   + **Date picker – range** – Displays a set of two fields to define a time range. You can enter a date or time, or you can choose a date from the calendar control. You can also customize how you want the dates to appear in the control by entering a date token for **Date format**. For more information, see [Customizing date formats in Quick](format-visual-date-controls.md).
   + **Date picker – relative** – Displays settings like the time period, its relation to the current date and time, and the option to exclude time periods. You can also customize how you want the dates to appear in the control by entering a date token for **Date format**. For more information, see [Customizing date formats in Quick](format-visual-date-controls.md).
   + **Text field** – Displays a box where you can enter the top or bottom *N* date.

     Helper text is included in the text field control by default, but you can choose to remove it by clearing the **Show helper text in control** option.

   By default, Quick visuals are reloaded whenever a change is made to a control. For Calendar and Relative date picker controls, authors can add an **Apply** button to a control that delays visual reload until the user chooses **Apply**. This allows users to make multiple changes at a time without additional queries. This setting can be configured with the **Show an apply button** checkbox in the **Control options** section of the **Format control** pane.

1. When finished, choose **Apply**.

### Text filters


If your filter control is from a text filter, for example dimensions, categories, or labels, use the following procedure to customize the remaining options.

**To customize further options for a text filter**

1. In the **Format control** pane, for **Style**, choose one of the following options:
   + **Dropdown** – Displays a dropdown list with buttons that you can use to select a single value.

     When you select this option, you can choose the following options for **Values**:
     + **Filter** – Displays all the values that are available in the filter.
     + **Specific values** – Enables you to enter the values to display, one entry per line.

     You can also choose to **Hide Select all option from the control values**. This removes the option to select or clear the selection of all values in the filter control.
   + **Dropdown - multiselect** – Displays a dropdown list with boxes that you can use to select multiple values. 

     When you select this option, you can choose the following options for **Values**:
     + **Filter** – Displays all the values that are available in the filter.
     + **Specific values** – Enables you to enter the values to display, one entry per line.

     By default, Quick visuals are reloaded whenever a change is made to a control. For Multiselect dropdown controls, authors can add an **Apply** button to a control that delays visual reload until the user chooses **Apply**. This allows users to make multiple changes at a time without additional queries. This setting can be configured with the **Show an apply button** checkbox in the **Control options** section of the **Format control** pane.
   + **List** – Displays a list with buttons that you can use to select a single value.

     When you select this option, you can choose the following options for **Values**:
     + **Filter** – Displays all the values that are available in the filter.
     + **Specific values** – Enables you to enter the values to display, one entry per line.

     You can also choose the following:
     + **Hide search bar when control is on sheet** – Hides the search bar in the filter control, so users can't search for specific values.
     + **Hide Select all option from the control values** – Removes the option to select or clear the selection of all values in the filter control.
   + **List - multiselect** – Displays a list with boxes that you can use to select multiple values. 

     When you select this option, you can choose the following options for **Values**:
     + **Filter** – Displays all the values that are available in the filter.
     + **Specific values** – Enables you to enter the values to display, one entry per line.

     You can also choose the following:
     + **Hide search bar when control is on sheet** – Hides the search bar in the filter control, so users can't search for specific values.
     + **Hide Select all option from the control values** – Removes the option to select or clear the selection of all values in the filter control.
   + **Text field** – Displays a text box where you can enter a single entry. Text fields support up to 79950 characters.

     When you select this option, you can choose the following:
     + **Show helper text in control** – Removes the helper text in text fields.
   + **Text field - multiline** – Displays a text box where you can enter multiple entries. Multiline text fields support up to 79950 characters across all entries.

     When you select this option, you can choose the following:
     + For **Separate values by**, choose how you want to separate values you enter into the filter control. You can choose to separate values by a line break, comma, pipe (\$1), or semicolon.
     + **Show helper text in control** – Removes the helper text in text fields.

1. When finished, choose **Apply**.

### Numeric filters


If your filter control is from a numeric filter, use the following procedure to customize the remaining options.

**To customize further options for a numeric filter**

1. In the **Format control** pane, for **Style**, choose one of the following options:
   + **Dropdown** – Displays a list where you can select a single value.

     When you select this option, you can choose the following options for **Values**:
     + **Filter** – Displays all the values that are available in the filter.
     + **Specific values** – Enables you to enter the values to display, one entry per line.

     You can also choose to **Hide Select all option from the control values**. This removes the option to select or clear the selection of all values in the filter control.
     + **Filter** – Displays all the values that are available in the filter.
     + **Specific values** – Enables you to enter the values to display, one entry per line.
     + **Hide Select all option from the control values** – Removes the option to select or clear the selection of all values in the filter control.
   + **List** – Displays a list with buttons that enable selecting a single value. 

     When you select this option, you can choose the following options for **Values**:
     + **Filter** – Displays all the values that are available in the filter.
     + **Specific values** – Enables you to enter the values to display, one entry per line.

     You can also choose the following:
     + **Hide search bar when control is on sheet** – Hides the search bar in the filter control, so users can't search for specific values.
     + **Hide Select all option from the control values** – Removes the option to select or clear the selection of all values in the filter control.
   + **Slider** – Displays a horizontal bar with a toggle that you can slide to change the value. If you have a ranged filter for values between a minimum and a maximum, the slider provides a toggle for each number. For sliders, you can specify the following options:
     + **Minimum value** – Displays the smaller value at the left of the slider.
     + **Maximum value** – Displays the larger value at the right of the slider.
     + **Step size** – Enables you to set the number of increments that the bar is divided into.
   + **Text box** – Displays a box where you can enter the value. When you select this option, you can choose the following:
     + **Show helper text in control** – Removes the helper text in text fields.

1. When finished, choose **Apply**.

## Cascading filter controls


You can limit the values displayed in the control, so they only show values that are valid for what is selected in other controls. This is called a cascading control.

**When creating cascading controls, the following limitations apply:**

1. Cascading controls must be tied to dataset columns from the same dataset.

1. The child control must be a dropdown or list control.

1. For parameter controls, the child control must be linked to a dataset column.

1. For filter controls, the child control must be linked to a filter (instead of showing only specific values).

1. The parent control must be one of the following:

   1. A string, integer, or numeric parameter control.

   1. A string filter control (excluding top-bottom filters).

   1. A non-aggregated numeric filter control.

   1. A date filter control (excluding top-bottom filters).

**To create a cascading control**

1. Choose **Show relevant values only.** Note that this option might not be available for all filter control types.

1. In the **Show relevant values only** pane that opens, choose one or more controls from the available list.

1. Choose a field to match the value to.

1. Choose **Update**.

# Editing filters


You can edit filters at any time in a dataset or analysis.

You can't change the field a filter applies to. To apply a filter to a different field, create a new filter instead.

Use the following procedures to learn how to edit filters.

## Editing filters in datasets


Use the following procedure to edit filters in datasets.

**To edit a filter in a dataset**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Data** at left.

1. Under the **Datasets** tab, choose the dataset that you want, and then choose **Edit dataset**.

1. On the data preparation page that opens, choose **Filters** at lower left.

1. Choose the filter that you want to edit.

1. When finished editing, choose **Apply**.

## Editing filters in analyses


Use the following procedure to edit filters in analyses.

**To edit a filter in an analysis**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Analyses** at left.

1. On the **Analyses** page, choose the analysis that you want to work with.

1. In the analysis, choose the **Filter** icon shown to open the **Filters** pane.

1. Choose the filter that you want to edit.

1. When finished editing, choose **Apply**.

# Enabling or disabling filters


You can use the filter menu to enable or disable a filter in a dataset or an analysis. When you create a filter, it's enabled by default. Disabling a filter removes the filter from the field, but it doesn't delete the filter from the dataset or analysis. Disabled filters are grayed out in the filters pane. If you want to re-apply the filter to the field, you can simply enable it.

Use the following procedures to learn how to enable or disable filters.

## Disabling filters in datasets


Use the following procedure to disable filters in datasets.

**To disable a filter in a dataset**

1. From the Quick homepage, choose **Data** at left.

1. Under the **Datasets** tab, choose the dataset that you want, and then choose **Edit dataset**.

1. On the data preparation page that opens, choose **Filters** at lower left.

1. In the **Filters** pane at left, choose the three dots to the right of the filter that you want to disable, and then choose **Disable**. To enable a filter that was disabled, choose **Enable**.

## Disabling filters in analyses


Use the following procedure to disable filters in analyses.

**To disable a filter in an analysis**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Analyses**.

1. On the **Analyses** page, choose the analysis that you want to work with.

1. In the analysis, choose the **Filter** icon to open the **Filters** pane.

1. In the **Filters** pane that opens, choose the three dots to the right of the filter that you want to disable, and then choose **Disable**. To enable a filter that was disabled, choose **Enable**.

# Deleting filters


You can delete filters at any time in a dataset or analysis. Use the following procedures to learn how.

## Deleting filters in datasets


Use the following procedure to delete filters in datasets.

**To delete a filter in a dataset**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Data**.

1. Under the **Datasets** tab, choose the dataset that you want, and then choose **Edit dataset**.

1. On the data preparation page that opens, choose **Filters** at lower left.

1. Choose the filter that you want to delete, and then choose **Delete filter**.

## Deleting filters in analyses


Use the following procedure to delete filters in analyses.

**To delete a filter in an analysis**

1. Open the [Quick console](https://quicksight.aws.amazon.com/).

1. From the Quick homepage, choose **Analyses**.

1. On the **Analyses** page, choose the analysis that you want to work with.

1. In the analysis, choose the **Filter** icon to open the **Filters** pane.

1. Choose the filter that you want to delete, and then choose **Delete filter**.