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, sorevenue 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:sum_if, average_if, count_if, unique_count_if, max_if, min_if, stddev_if, variance_if, vs, all.
Examples:
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 thevs 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:
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: