> ## Documentation Index
> Fetch the complete documentation index at: https://docs.invoca.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Filtered Aggregation Functions

> Filtered aggregations let a search bar query reflect a new, filtered value using comparative or derivative filters.

You can create a filtered aggregation directly in the search bar. Filters are useful for queries where the results should reflect a new, filtered value.

## Comparative and derivative filters

Comparative filters compare two segments of a whole against each other in the **Search** bar. For example, a company with locations across the United States might compare total revenue in the West to the East segment — in a comparative filter, one of the compared segments is filtered, so `revenue region = west` is a filtered Measure.

Derivative filters add a column to your results that's derived from other columns in the same results — for example, searching for revenue and cost to calculate profit.

Some real-world examples: revenue of `this_soap` versus `all_soaps` (comparative); tax as a percentage of revenue (derivative); count revenue as a percentage of state revenue (comparative with a derivative).

## Use filter functions

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

```text theme={null}
FUNCTION_NAME(condition, <column name>)
```

Invoca's functional library includes: `sum_if`, `average_if`, `count_if`, `unique_count_if`, `max_if`, `min_if`, `stddev_if`, `variance_if`, `vs`, `all`.

Examples:

```text theme={null}
sum_if(region='west', revenue)
```

aggregates only the revenue for values corresponding to the west region.

```text theme={null}
count_if(region='west', region)
```

aggregates only the region for values corresponding to west.

```text theme={null}
count_if(revenue > 100, red)
```

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

A condition can have multiple filters, such as `sales region = west OR region = east`. You can also type a value alone as a filter — in the expression `sales east`, `east` is the filter.

If no rows match the filter criteria, the condition returns `0`. When you use a plain filter rather than a filter function, Invoca doesn't include 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.

## Using the vs and all keywords

Use the `vs` and `all` keywords to expand your comparison filters — they compare a Measure across different sets of filters and/or groupings. The basic format of a comparison search is:

```text theme={null}
<common search tokens> (A vs B) <common search tokens>
```

For example: `revenue region last 10 years vs all`. The system automatically applies the `sales` token to both sides and groups each segment. Use the `all` keyword to break out the segments and avoid grouping. You can provide multiple `vs` instances, and compare across different columns.

Other supported formats:

```text theme={null}
sales accessory6 accessory12 vs all
sales monthly accessory6 vs last year
sales staples file caddy vs all monthly
```

## Where to go next

* [Cumulative functions](/s/article/cumulative-functions)
* [Moving functions](/s/article/moving-functions)
* [Flexible aggregation functions](/s/article/flexible-aggregation-functions)
* [Group aggregation functions](/s/article/group-aggregation-functions)