Standard and Aggregation Formulas
Formulas can be categorized into two main types: standard formulas and aggregation or cumulative formulas.
Standard Formulas
These act on individual rows and return one result for each specific row. Standard formulas typically use functions such as:
addsubtractmultiplydividecontainsif...then...else
Aggregation/Cumulative Formulas
These combine multiple rows together and return a single result for that group of rows. Common functions found in aggregation formulas include:
averagecumulative_summoving_averagestddev(standard deviation)
You can easily identify aggregation formulas by viewing the function list within the formula assistant (the ? icon). Aggregation functions are organized into their own dedicated section.
Advanced Aggregation Functions
Advanced aggregation functions are widely used in business intelligence because they provide deeper insights into your data. Some of these include:
Group aggregation functions: Apply a specific aggregate to a value and group the results by an attribute in the data.
Cumulative functions: Measure data from the start of your dataset to the current point, which is especially useful for time-based analysis.
Moving formulas: Measure data within a specific window that you define, usually based on time.
Trends AI does not include data from rows outside the search results in the aggregation. You cannot create a filter on already aggregated data
Flexible Aggregation
The group_aggregate function allows you to specify exactly which columns and filters to include or ignore in your query. See Flexible aggregation to learn more about working with this formula.
Using Division with Aggregation in a Search
Whenever your search results combine rows, your formula will be aggregated automatically. For instance, if your search contains terms like "region", "monthly", or "department", the results will be grouped by that category. Administrators can change the default aggregation settings, and you can also adjust them using the dropdown list in the column header of your search results.
For example, a typical search for sum amount would return the total giving amount for your organization. A search for average amount monthly would return the average giving amount per month. When using division in your formula alongside these groupings, you may need to adjust the order of operations to ensure the results are what you expect.
Example: Calculating Gross Margin
Suppose you want to calculate the giving amount by campus for a multi-campus church. The standard formula is: net amount / amount. If you use this as your formula without explicit aggregation, Trends AI will divide net amount by amount (gross amount) for each individual row and then total those results. This will not yield a true gross margin (which should be a value between 0 and 1).
To get the correct average gross margin, you must use a formula that totals the values before dividing: sum (net amount) / sum (amount). Now the result is correct because the formula totals all profits, totals all sales, and then performs the division.
