About formulas and aggregation

Last updated

10:21, 27 Apr 2017
Save as PDF

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

Formulas can be broken down into two types:

Table 1. 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

You can tell which formulas are aggregation formulas by looking at the Formula Assistant. Aggregation formulas have their own section.

Aggregation formulas in the Formula Assistant

Advanced aggregation formulas

Some more advanced aggregation formulas are widely used in business intelligence, since they provide better insight into data. Some of the more advanced aggregation formulas are:

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.

For example, this search would typically return a sum of total sales by department:

sum sales department

This search would return an average of sales by month:

average sales monthly

When 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.

Suppose you want to calculate the gross margin by department for a grocery store. The formula for gross margin is:

profit / sales

But if you use that as your formula, you won't get the expected calculation. Why? It's because the formula will be evaluated in this order: For each row, divide profit by sales and then total up all the results. As you can see, the results do not look like gross margin values, which should be between 0 and 1.

Aggregated search with a division formula

Instead, you'd need to use a formula that uses the order of operations you want:

sum (profit) / sum (sales)

Now the result is as expected, because the formula totals the profits for all rows, and then divides that by the total of sales for all rows, returning an average gross margin:

Aggregated search with a corrected division formula