About formulas and aggregation
Overview
Explains what the feature is or what its benefits are to the user or customer.
Feature
When working with formulas, it is useful to understand the difference between regular (or row-wise) formulas and aggregation formulas.
Regular and aggregation formulas
Formula | Aggregation formula |
---|---|
Acts on individual rows and returns one result per row. | Combines rows together and returns a single result for a group of rows. |
Examples: add, subtract, multiply, divide, contains, if...then...else | Examples: Average, cumulative sum, moving average, standard deviation |
Advanced aggregation formulas
-
Grouping formulas apply a specific aggregate to a value, and group the results by an attribute in the data.
- Cumulative formulas measure from the start of your data to the current point. They're often applied on time-based data.
- Moving formulas measure within a window (usually time-based) that you define.
Data from any rows that are not included in the search result will not be incorporated, and you cannot create a filter on aggregated data.
Using division with aggregation in a search
Whenever your search result combines rows, your formula will get aggregated automatically. For example, if your search contains words like "region", "monthly", or "department", the results will be grouped (aggregated) by that category. The administrator can change the default aggregation that gets applied through a configuration, and you can also change it using the dropdown list in the column header of the search result.
sum sales department
average sales monthlyWhen you're using division in your formula, and the search is aggregated like this, you may have to change the order of operations to get the result you expect. This is best understood by using a real world example.
profit / sales
sum (profit) / sum (sales)