Filtered aggregation functions

You can create a filtered aggregation in the search bar.

Filters are useful for queries where the results should reflect a new, filtered value. On this page you learn about comparative versus derivative filters, and the functions, and the keywords that you can use with them.

Comparative and derivative filters

Comparative filters compare two segments of some whole against each in the Search bar. For example, a company that has locations across the United States, may want to compare total revenue in the West to the East segment. In a comparative filter, one of the segments you are comparing is filtered.

An example of a comparative filter is comparing west revenue with total revenue. In these cases, one measure is a filtered measure, for example, revenue region = west is a filtered measure.

Derivative filters add a column to your results which is derived from other columns in the same results. For example, you search for revenue and cost and want to calculate profit in your result.

Some examples of comparative and derivative filters in the real world are:

  • revenue of this_soap versus all_soaps (Comparative filter)

  • tax as a percentage of revenue (derivative)

  • count revenue as a percentage of state revenue (comparative with a derivative)

If you plan to create these types of filters, you need to understand how to create filter functions.

Use filter functions

Filter functions take two arguments, the column ( measure or attribute) to aggregate and the filter condition:

FUNCTION_NAME(condition, <column name>)

ThoughtSpot functional library will include the following functions:

  • sum_if

  • average_if

  • count_if

  • unique_count_if

  • max_if

  • min_if

  • stddev_if

  • variance_if

  • vs

  • all

Consider the following examples of these functions:

sum_if(region='west', revenue)

Aggregates only the revenue for the values that correspond to the west region.

count_if(region ='west', region)

Aggregates only the region for the values that correspond to the west region.

count_if(revenue > 100, red)

Counts the number of times red appears when revenue is greater than 100 (row-level revenue data, not aggregated).

A condition can have multiple filters, like the two conditions for region in the example sales region = west OR region = east.

You can also just type a value as a filter; here, east is the filter in the expression sales east.

If there are no rows matching the filter criteria, the condition returns a 0 (zero). When you use a filter rather than a filter function, ThoughtSpot does not include any zero values in your chart or table. Using a filter function like sum_if or count_if is the best way to include zero values in your chart or table.

A 0 can result in situations where there are logic errors in the formula, so be sure to verify your work.

Formula example

After you have aggregated with a filter, you can do further comparisons with the vs and all keywords.

Using the vs and all keywords

You can use the vs and all keywords to expand the usefulness of your comparison filters. It compares a measure across different sets of filters and or groupings. The basic format of a comparison search is:

<common search tokens> (A vs B) <common search tokens>

For example:

revenue region last 10 years vs all

Try this syntax using the superstore example data. The first vs example compares two segments with a single search token:

Search result for store state vs store region sales

The system automatically applies the sales token to both sides and groups each segment. You can use the all keyword to break out the segments and avoid grouping.

Search result for sales store state vs all yearly last 3 years

You can also provide multiple vs instances:

Search result for sales store state vs all vs yearly last 3 years

Of course, you can compare across different columns as well:

Search result for sales customer age group customer gender department sales age group vs customer gender

Other supported formats you can try:

  • sales accessory6 accessory12 vs all

  • sales monthly accessory6 vs last year

  • sales staples file caddy vs all monthly answers what the share of sales belonging to the file caddy by month

  • sales (germany ariel vs laundry) july 2017 time answers what is the category share of Germany Ariel for July 17?