Understand filters

When you add a value to your search, it becomes a filter. You can define filters on tables, views and Worksheets. When you add a filter, it is applied to the table, view, or Worksheet, so the result set only shows rows that satisfy a set of parameters specified in the filter. You can also set filters that are automatically used in every search you perform using a particular data source. For example, you can exclude inactive customers records from your search result set. To avoid typing status = inactive with every search you perform, you can use a filter. The complex the filter is, the more useful it is to set on the data sources (for example, status = inactive year = 2017 rating > 0).

To add a filter from the search bar:

  1. Select the search bar and type the value or values you want to include in the search.

    Typing a value in the search bar acts as a filter.

    Filter from the search bar

    You can also use date- and time-related keywords like yesterday, after, and next month to filter your search. To see more keywords, refer to the keyword reference.

  2. Click outside of the search bar or push enter to apply your filter.

Simple filters can be applied to an Answer, while Liveboard filters can be applied to all visualizations of a Liveboard. You can find out more about Liveboard filters in the Liveboards section.

Where filters appear in ThoughtSpot

As you have seen with search, filters appear in grey boxes in the search bar.

Search bar with filters

In an Answer or a Liveboard, filters appear just under the title. For Liveboards, your filters apply to all Worksheet-based visualizations in the Liveboard.

Filters appear under the title

If you ever find that your search or Liveboard does not appear to contain all the data you want to see, check for any existing filters and remove them by selecting the X that appears when you hover over the filter in the search bar.

Filtering on NULL and empty values is a special case. You can find out more about how these values are represented and how to filter for them in About filtering on null, blank, or empty values.

Simple filters

Simple filters can be applied to searches in a few different ways. You can use the search bar or choose Filter from the column header or axis label. You can apply simple filters to your search, whether it shows a table or a chart. Your filters remain part of the search even when you change the visualization type.

When adding a filter from the More icon more options menu icon, in the column header or by selecting a chart axis, numeric columns and text columns provide you with the ability to include or exclude values, and a checkbox selector for the values. If the column contains a date, you can see a calendar selector when applying a filter. This is also where you can apply bulk filters.

Bulk filters

If you have a large Worksheet or table with thousands or millions of rows, you may want to create bulk filters. You can paste in a list of filter values to include or exclude, without having to select the box next to each value in the filter selector.

Bulk filters can be very useful when you have a very large Worksheet or table. You can use them to filter a large list of values easily. For example, this is useful if you want to only search on a list of products that your manager sent to you in an email. You can cut and paste those values into the bulk filter box to quickly generate a report or chart that includes only those items of interest.

You can create a bulk filter by pasting a list of values, separated by commas, semicolons, new lines, or tabs, into the bulk filter box. This allows you to easily search a large list of filters repeatedly.

Show unfiltered values for a filter

ThoughtSpot applies a specific logic to objects with more than one filter. For example, you may have a Worksheet-level filter, such as country=US. While searching on that Worksheet, you might want to add filters for specific cities. By default, ThoughtSpot limits your possible filter values to cities in the United States, since you have a Worksheet-level US filter. The first filter you apply on an object always limits the available filter values you see for your second and third filters, and so on. This is because if you have a country=US filter, and try to add a city=Paris,France filter, for example, this results in no data found.

When you access the filters modal in a search by selecting the filter icon filter icon, you see an option to Show unfiltered values. This option relates to objects with more than one filter. When you add a second filter to a search, ThoughtSpot only shows you values within the first filter you added. You may want to see all possible values without removing the first filter.

For example, you have a country=US filter on your search, but you want to see data for Paris, France. When you open the filters modal for city, you do not see Paris, France. You select Show unfiltered values and select Paris, France. Then you can delete the country=US filter. This is especially useful if you have many filters on an object, or if you are not sure what values the columns in your data have.

Change the filter order

If your Answer or Liveboard has multiple filters, you can arrange them in the filter bar so that they are in a logical, coherent order. This makes it easier for viewers of the Answer or Liveboard to understand and use the filters on the Answer or Liveboard. Changing the filter order has no effect on the filter precedence. Changing the filter order in the UI doesn’t change the filter order in the TML file.

To change the order of filters in the filter bar, follow these steps:

  1. For a Liveboard, select the Edit button at the upper right of the Liveboard you would like to edit. If this button is grayed out, you do not have edit privileges for the Liveboard. To resolve this, contact your administrator or the creator of the Liveboard. For an Answer, there is no specific edit mode, so you don’t need to complete this step.

  2. Select any filter from the filter bar, and drag and drop it to a new location.

  3. Select Save in the upper right of the Liveboard. For an Answer, select the more options more options menu icon image menu, and select Save.

Worksheet filters

A Worksheet filter gets applied every time that Worksheet is used. This means that for any search involving a filtered Worksheet, all Worksheet filters are applied before the search is submitted. So results are always filtered, even if the specific terms searched do not include the column(s) that are filtered.