Aggregation Expressions
Summary
- Aggregation Expressions evaluate against a set of rows and return a single value
- Aggregation Functions always use curly braces:
SUM{[PnL]}— never parentheses - The return value is either scalar (used in Calculated Columns) or boolean (used in Alerts e.g. Limits Management)
- Scope can be narrowed with
GROUP_BY(a group of rows) andWHERE(a Standard Boolean Expression) - Excel-style Filtered Aggregations (
SUMIF,COUNTIF,AVGIF) bake the filter into the Function itself - The value aggregated can be a calculation (e.g.
SUM{[Price] * [Quantity]}) - Aggregations can be combined with each other, and with values in the current row (e.g. % of group, z-scores)
- 3 specialist forms of Aggregations are also available: Cumulative, Quantile and Window
Aggregation Expressions are Expressions that run against aggregated data.
The Expression evaluates to a single value for each row, but that value is derived by aggregating multiple rows.
Why use Aggregation Expressions?
- Aggregates every row: each trade can show Desk's total, its share of that total, or how unusual its PnL is (z-score)
- No need to group the Grid:
GROUP_BYsplits the aggregation, while the Grid stays flat (or is grouped any other way) - Conditions on totals: fire an Alert when
SUM{[PnL]} < '-500K', or when any Desk's Notional breaches its limit - Filter inside the Aggregation:
WHEREand Excel-styleSUMIF/COUNTIF/AVGIFaggregate just the rows you want - Combine freely: mix Aggregations with each other and with row values -
SUM{[PnL]} / SUM{[Notional]} * 100 - Always live: every value recalculates as the data ticks
Note
- AG Grid's aggregations fill group and total rows, and its formulas can only sum a fixed range of rows
- This means that you cannot create queries such as "every trade in this row's Desk"
- This demo uses a set of trades and 5 Aggregated Calculated Columns:
% of Desk- each trade's share of its Desk's Notional:[notional] / SUM{[notional], GROUP_BY([desk])} * 100P&L Z-Score- how unusual each P&L is:([pnl] - AVG{[pnl]}) / STD_DEVIATION{[pnl]}Desk Net Notional- Buy minus Sell per Desk, usingWHERE:SUM{[notional] WHERE [side] = 'Buy', GROUP_BY([desk])} - SUM{[notional] WHERE [side] = 'Sell', GROUP_BY([desk])}Open P&L-SUMIF{[pnl], [status] = 'Open'}Open Trades-COUNTIF{[id], [status] = 'Open'}
- A Format Column highlights any P&L more than 2 standard deviations from the average (
ABS([pnlZScore]) > 2)
- See trade 9 (
NVDA, P&L 25,000) highlighted as an outlier - Change trade 9's
P&Lto 500 - the highlight disappears, and every Z-Score,Open P&Lvalue updates - Change a trade's
Sideand watchDesk Net Notionalchange for every trade in that Desk
Important
- Write Aggregation Functions with curly braces (as opposed to parentheses used with Standard Functions)
// Aggregation — sum of the PnL column across rows - contains curly braces
SUM{[PnL]};
// Standard — add 3 numbers in the current row - contains parentheses
SUM(1, 2, 3);| Type | Returns | Available in Modules |
|---|---|---|
| Scalar | Any value | Calculated Column |
| Boolean | True or False | Alerts |
Find Out More
AdapTable also provides 3 specialist Aggregation Expressions: Cumulative, Quantile and Window
Scalar Aggregation Expressions
Scalar Aggregation Expressions compute one value from many rows.
Note
- By default they compute against the whole grid
- See Grouped Aggregation to limit the set of rows
They need an Aggregation Function and the Column being aggregated — written with curly braces.
The Aggregation Functions available are:
| Function | Description | Example |
|---|---|---|
SUM | Returns the sum of non-empty values in a numeric column. | SUM{[numCol]} |
SUMIF | Returns sum of a numeric column over the rows that match the predicate (an ordinary boolean expression). | SUMIF{[notional], [currency] = "USD"} |
AVG | Returns the arithmetic mean of non-empty values in a numeric column. | AVG{[numCol]} |
AVGIF | Returns arithmetic mean of a numeric column over the rows that match the predicate (an ordinary boolean expression). | AVGIF{[price], [region] = "EMEA"} |
MIN | Returns the minimum value in a numeric or date column. | MIN{[numCol]} |
MAX | Returns the maximum value in a numeric or date column. | MAX{[numCol]} |
COUNT | Returns the count of non-empty values in a column. | COUNT{[numCol]} |
COUNTIF | Returns count of non-empty values in a column over the rows that match the predicate (an ordinary boolean expression). | COUNTIF{[id], [status] = "Open"} |
PRODUCT | Returns the product of non-empty values in a numeric column. | PRODUCT{[numCol]} |
PERCENTAGE | Returns each row's value as a percentage of a column sum (×100). | PERCENTAGE([numCol]) |
MEDIAN | Returns the median of values in a numeric column. | MEDIAN{[numCol]} |
MODE | Returns the most frequent value in a number, text, or date column. | MODE{[numCol]} |
DISTINCT | Returns how many distinct values appear in a column. | DISTINCT{[numCol]} |
ONLY | Returns the column value when every non-empty row shares the same value; otherwise returns null. | ONLY{[numCol]} |
STD_DEVIATION | Returns the population standard deviation of values in a numeric column. | STD_DEVIATION{[numCol]} |
ABSOLUTE_DIFF | Returns the absolute difference between each row value and a relative aggregation (AVG, MEDIAN, MODE, MIN, MAX, LAG, LEAD, FIRST, or LAST). | ABSOLUTE_DIFF([numCol], MAX{[numCol]}) |
PERCENT_DIFF | Returns the percentage difference between each row value and a relative aggregation (AVG, MEDIAN, MODE, MIN, MAX, LAG, LEAD, FIRST, or LAST). | PERCENT_DIFF([numCol], MAX{[numCol]}) |
Note
- Cumulative, Quantile and Window Functions are listed on those pages
- Developers can add Custom Aggregation Functions to supplement this list
// Return sum of all PnL cells
SUM{[PnL]};AVG can take an optional WEIGHT parameter to create a Weighted Average:
// Return the Weighted Average for the Price Column (using Index column for weighting)
AVG{[Price], WEIGHT([index])};Aggregating a Calculation
The value being aggregated does not have to be a single Column.
It can be any Standard Expression, which is evaluated for each row and the results are then aggregated:
// Total traded value - sum of Price x Quantity across all rows
SUM{[Price] * [Quantity]};// Largest PnL move (up or down) on each Desk
MAX{ABS([PnL]), GROUP_BY([Desk])};Hint
This avoids having to create a separate Calculated Column just to hold the value being aggregated
- This Demo contains 2 Calculated Columns that use Aggregated Scalar Expressions
Popularity Ratio- displays a Percentage of
PopularityColumn Popularityis itself a Calculated Column that sumsGitHub StarsandGitHub Watchers
- displays a Percentage of
Total Stars- shows a Sum of the GitHub Stars column
- because
GROUP_BYis not added, is the same value in each row
- Add a
GROUP_BYto the Total Stars Expression to Sum according to Language
Boolean Aggregation Expressions
Boolean Aggregation Expressions (see Boolean Aggregation Expressions) are the same aggregation, then compared to a threshold so the result is true / false.
Hint
- This is ideal for Limits Management and related scenarios
- For instance where you want to check that a sum comprising multiple cell values has not been exceeded
Compare with =, !=, >, <, >= or <=. Thresholds can use K / M / B shortcuts (in quotation marks):
// Is total value of the 'PnL' column in all rows > 5 Million
SUM{[PnL]} > '5M';- This demo contains 3 Alerts that fire when an Aggregated Boolean Expression is triggered
- if the lowest
Github Watchersvalue is under 50 - when the total of all the
Github Starsrows is over 1.1 million ('1.1M') - when the total of all
Open Issuesrows is over 9,800 ('9.8k')
- if the lowest
- In the first row change the value of:
Github Watcherscolumn to 45 and see theInfoAlert be firedGithub Starscolumn to 279429 and see theSuccessAlert be firedOpen Issuescolumn to 1220 and see theWarningAlert be fired
Grouped Aggregation
Use GROUP_BY to apply the Aggregation Function to a group of rows, rather than the entire grid.
Note
The GROUP_BY clause is always only used together with an Aggregation Function
// Return Sum of all Pnl cells for rows which have this row's Currency value
SUM{[PnL], GROUP_BY([Currency])};GROUP_BY can receive multiple Columns:
// Return Sum of all Pnl cells for rows which have this row's Currency and Counterparty values
SUM{[PnL], GROUP_BY([Currency], [Counterparty])};Deep Dive
How Grouped Aggregation Works
- This Demo contains 4 Calculated Columns that use Aggregated Scalar Expressions with Grouping
Watchers By Lang- Sum of the GitHub Watchers column, grouped by LanguageAvg. Lang Popularity- Average of Github Stars popularity, grouped by languageOpen/Closed Issues % by Lang- Percentage of Open and Closed issues, grouped by languageLang Count- Count of each language, also grouped language
Expand to see the Aggregated and Grouped Scalar Expressions
Filtered Aggregations
AdapTableQL also provides 3 Excel-style filtered aggregations — SUMIF, COUNTIF and AVGIF.
Each takes 2 arguments:
- a Column to aggregate
- a Standard Boolean Expression (the predicate) that selects which rows contribute
// Sum PnL only for rows where Currency is USD
SUMIF{[PnL], [Currency] = "USD"};// How many rows have Status = Open
COUNTIF{[Id], [Status] = "Open"};// Average Price for rows in EMEA (and positive notional)
AVGIF{[Price], [Region] = "EMEA" AND [Notional] > 0};| Function | Meaning |
|---|---|
SUMIF | Sum of a numeric Column over rows that match the predicate |
COUNTIF | Count of non-empty values over rows that match the predicate |
AVGIF | Arithmetic mean of a numeric Column over matching rows |
They are functionally equivalent to putting a WHERE clause inside the braces of the base Aggregation:
SUMIF{[PnL], [Currency] = "USD"};
// is equivalent to:
SUM{[PnL] WHERE [Currency] = "USD"};Note
- Unlike
SUM/AVG/COUNT, these*IFforms do not takeGROUP_BY,WEIGHTorCUMULATIVEclauses - Use the base Aggregation with
WHEREinside the braces when you need a filter together with any of these
WHERE Clause
A WHERE clause restricts which rows are aggregated.
It is written as described in the Guide to Writing Expressions — a Standard Boolean Expression that each row must satisfy.
WHERE can be placed in 2 positions:
| Position | Filters | Example |
|---|---|---|
| Inside the braces | That single Aggregation only | SUM{[PnL] WHERE [Side] = 'Buy'} |
| After the Expression | Every Aggregation in the Expression | SUM{[PnL]} > '5M' WHERE [Currency] = 'USD' |
Filtering a Single Aggregation
Put WHERE straight after the value being aggregated (inside the braces) to filter just that Aggregation:
// Total PnL of all Buy trades
SUM{[PnL] WHERE [Side] = 'Buy'};It can be combined with other clauses such as GROUP_BY:
// Total PnL of Buy trades in this row's Currency
SUM{[PnL] WHERE [Side] = 'Buy', GROUP_BY([Currency])};This makes it possible for each Aggregation in an Expression to have a different filter:
// Net position - Buy Notional minus Sell Notional
SUM{[Notional] WHERE [Side] = 'Buy'} - SUM{[Notional] WHERE [Side] = 'Sell'};Filtering the Whole Expression
It is also possible to filter the entire Expression (i.e. every Aggregation), rather than a single Expression.
This is accomplished by putting the WHERE clause at the end of the Expression:
// Are all PnL values in rows with Dollar Currency over 5M
SUM{[PnL]} > '5M' WHERE [Currency] = 'USD' // Average PnL of Open trades (both SUM and COUNT only see Open rows)
SUM{[PnL]} / COUNT{[Id]} WHERE [Status] = 'Open'- This demo contains 2 Alerts that fire when an Aggregated Boolean Expression is triggered.
- Both are similar to the Aggregation Boolean Expressions in the demo above but with a
WHEREclause added:- when the total of
Github Starsis over 900,000 - only for rows whereLicenceis 'MIT License' - when the total of
Open Issuesis over 6,000 - only for rows whereLanguageis 'JavaScript'
- when the total of
- In the first row change the value of:
Github Starscolumn to 279429 and see theSuccessAlert be fired (becauseLicencein first row is "MIT License")Open Issuescolumn to 1220 and see theWarningAlert be fired (becauseLanguagein first row is "JavaScript")
Combining Aggregations
Aggregation Functions return ordinary values, so they can be used anywhere in an Expression.
They can be combined together with other Aggregations, and / or with values from the current row.
With Row Values
Mixing an Aggregation with the current row's values returns a different result for each row:
// This trade's share (%) of its Desk's total Notional
[Notional] / SUM{[Notional], GROUP_BY([Desk])} * 100;// How far this Price is from the Instrument's average Price
[Price] - AVG{[Price], GROUP_BY([Instrument])};// z-score - how many standard deviations this PnL is from the average PnL
([PnL] - AVG{[PnL]}) / STD_DEVIATION{[PnL]};In a Boolean Aggregation Expression (e.g. in an Alert) this compares each row to its group:
// Rows whose PnL is more than 3 times their Desk's average PnL
[PnL] > AVG{[PnL], GROUP_BY([Desk])} * 3;With Other Aggregations
Aggregations can be combined with arithmetic:
// Return on Notional (%) across the whole Grid
SUM{[PnL]} / SUM{[Notional]} * 100;Boolean Aggregation Expressions can join several conditions with AND / OR:
// Alert when total Notional exceeds 10M AND there are more than 50 trades
SUM{[Notional]} > '10M' AND COUNT{[Id]} > 50;// Alert when any single Exposure is over 2M OR total PnL falls below -500K
MAX{[Exposure]} > '2M' OR SUM{[PnL]} <
Hint
The demo at the top of this page combines Aggregations with row values, WHERE, SUMIF and COUNTIF
FAQ
When should I use SUMIF instead of WHERE?
SUMIF{[PnL], [Currency] = "USD"} and SUM{[PnL] WHERE [Currency] = "USD"} give the same result - use whichever reads better.
Use WHERE inside the braces if you also need GROUP_BY, WEIGHT or CUMULATIVE, and WHERE at the end of the Expression when one filter should apply to every Aggregation in it.
Is there a SUMIFS or AVERAGEIF?
No. AdapTableQL ships SUMIF, COUNTIF and AVGIF only.
Combine conditions with AND / OR in the predicate (e.g. SUMIF{[PnL], [Currency] = "USD" AND [Region] = "EMEA"}).
Excel’s AVERAGEIF maps to AVGIF.
Does Aggregation take Pagination into account? No. All the data in the AG Grid data source is used when aggregating and not solely what is on the current page.