You can add filters to a worksheet to limit the data it contains. This is useful when you have underlying tables that store more data than is necessary for the types of analyses the worksheet is intended for. You can also use worksheet filters to provide data security, when you want different groups of users to be able to see different data without relying on row level security.
Note that starting in release 6.2, you can export and import Worksheets with filters using a flat
TML file, which allows for optional modification of any Worksheet properties, including filters. If you are on a release prior to 6.2, you can migrate Worksheets using a flat file format, but you cannot migrate Worksheets with filters.
Refer to Migrate and restore objects to add a filter to a Worksheet through the TML file.
To add a filter to a worksheet through the user interface:
Click Data in the top menu bar, find your worksheet, and click its name.
Click the Edit Worksheet button.
Hover over Filters on the left menu and click +.
Choose the column you want to filter on.
Select the values to include in your answer.
If you want to exclude values, click Exclude and choose values to exclude.
If there are too many values, you can use the filter search bar to find the ones you want.
Use aggregate formulas as Worksheet filters
Starting in ThoughtSpot release 6.2, you can use an aggregate formula as a Worksheet filter. This is useful when, for example, you only want your results to show a measure when the related attribute is greater than some number, or vice versa. You may only want to see
sales when the
unique customer count is greater than 1, or you may only want to see a
customer if the associated
sales is greater than 0. Rather than add that formula to every search, you can create a filter at the Worksheet level.
To add an aggregated formula to a Worksheet, follow these steps:
- Create an aggregate formula in a Worksheet.
- Add the formula to Worksheet as a filter, as shown in steps 1-7 above. Instead of choosing a column to filter on, search for and choose your aggregate formula.
Specify conditions in which the filter should apply. Here, we have chosen to only display
gross profitwhen it is greater than $1,000,000. The following conditional statements are acceptable:
is greater than,
is greater than or equal to,
is less than,
is less than or equal to,
is in, or
is not in.
- Click Done, and save the Worksheet.