Aggregation Expressions

View as Markdown

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) and WHERE (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_BY splits 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: WHERE and Excel-style SUMIF / COUNTIF / AVGIF aggregate 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"
AdapTableQL: Combining Aggregations
Fork
  • 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])} * 100
    • P&L Z-Score - how unusual each P&L is: ([pnl] - AVG{[pnl]}) / STD_DEVIATION{[pnl]}
    • Desk Net Notional - Buy minus Sell per Desk, using WHERE: 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)
Try It Out
  • See trade 9 (NVDA, P&L 25,000) highlighted as an outlier
  • Change trade 9's P&L to 500 - the highlight disappears, and every Z-Score, Open P&L value updates
  • Change a trade's Side and watch Desk Net Notional change for every trade in that Desk
Loading demo…

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);
TypeReturnsAvailable in Modules
ScalarAny valueCalculated Column
BooleanTrue or FalseAlerts

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:

FunctionDescriptionExample
SUMReturns the sum of non-empty values in a numeric column.SUM{[numCol]}
SUMIFReturns sum of a numeric column over the rows that match the predicate (an ordinary boolean expression).SUMIF{[notional], [currency] = "USD"}
AVGReturns the arithmetic mean of non-empty values in a numeric column.AVG{[numCol]}
AVGIFReturns arithmetic mean of a numeric column over the rows that match the predicate (an ordinary boolean expression).AVGIF{[price], [region] = "EMEA"}
MINReturns the minimum value in a numeric or date column.MIN{[numCol]}
MAXReturns the maximum value in a numeric or date column.MAX{[numCol]}
COUNTReturns the count of non-empty values in a column.COUNT{[numCol]}
COUNTIFReturns count of non-empty values in a column over the rows that match the predicate (an ordinary boolean expression).COUNTIF{[id], [status] = "Open"}
PRODUCTReturns the product of non-empty values in a numeric column.PRODUCT{[numCol]}
PERCENTAGEReturns each row's value as a percentage of a column sum (×100).PERCENTAGE([numCol])
MEDIANReturns the median of values in a numeric column.MEDIAN{[numCol]}
MODEReturns the most frequent value in a number, text, or date column.MODE{[numCol]}
DISTINCTReturns how many distinct values appear in a column.DISTINCT{[numCol]}
ONLYReturns the column value when every non-empty row shares the same value; otherwise returns null.ONLY{[numCol]}
STD_DEVIATIONReturns the population standard deviation of values in a numeric column.STD_DEVIATION{[numCol]}
ABSOLUTE_DIFFReturns 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_DIFFReturns 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

// 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

AdapTableQL: Scalar Aggregation
Fork
  • This Demo contains 2 Calculated Columns that use Aggregated Scalar Expressions
    • Popularity Ratio
      • displays a Percentage of Popularity Column
      • Popularity is itself a Calculated Column that sums GitHub Stars and GitHub Watchers
    • Total Stars
      • shows a Sum of the GitHub Stars column
      • because GROUP_BY is not added, is the same value in each row
Try It Out
  • Add a GROUP_BY to the Total Stars Expression to Sum according to Language
Loading demo…

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';
AdapTableQL: Boolean Aggregation
Fork
  • This demo contains 3 Alerts that fire when an Aggregated Boolean Expression is triggered
    • if the lowest Github Watchers value is under 50
    • when the total of all the Github Stars rows is over 1.1 million ('1.1M')
    • when the total of all Open Issues rows is over 9,800 ('9.8k')
Try It Out
  • In the first row change the value of:
    • Github Watchers column to 45 and see the Info Alert be fired
    • Github Stars column to 279429 and see the Success Alert be fired
    • Open Issues column to 1220 and see the Warning Alert be fired
Loading demo…

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

AdapTableQL: Grouped Aggregation
Fork
  • This Demo contains 4 Calculated Columns that use Aggregated Scalar Expressions with Grouping
    • Watchers By Lang - Sum of the GitHub Watchers column, grouped by Language
    • Avg. Lang Popularity - Average of Github Stars popularity, grouped by language
    • Open/Closed Issues % by Lang - Percentage of Open and Closed issues, grouped by language
    • Lang Count - Count of each language, also grouped language

Expand to see the Aggregated and Grouped Scalar Expressions

Loading demo…

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};
FunctionMeaning
SUMIFSum of a numeric Column over rows that match the predicate
COUNTIFCount of non-empty values over rows that match the predicate
AVGIFArithmetic 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 *IF forms do not take GROUP_BY, WEIGHT or CUMULATIVE clauses
  • Use the base Aggregation with WHERE inside 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:

PositionFiltersExample
Inside the bracesThat single Aggregation onlySUM{[PnL] WHERE [Side] = 'Buy'}
After the ExpressionEvery Aggregation in the ExpressionSUM{[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'
AdapTableQL: Boolean Aggregations with WHERE
Fork
  • 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 WHERE clause added:
    • when the total of Github Stars is over 900,000 - only for rows where Licence is 'MIT License'
    • when the total of Open Issues is over 6,000 - only for rows where Language is 'JavaScript'
Try It Out
  • In the first row change the value of:
    • Github Stars column to 279429 and see the Success Alert be fired (because Licence in first row is "MIT License")
    • Open Issues column to 1220 and see the Warning Alert be fired (because Language in first row is "JavaScript")
Loading demo…

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.