Quantile (Aggregation) Expressions
Summary
- Creating buckets (e.g. Quartiles, Percentiles) using the
QUANTfunction
In Quantile Expressions, each value in a column is placed into a different bucket based on its value relative to others in the group.
Note
Quantile Expressions are, like Cumulative Expressions, a special case of Aggregation Expressions
Standard Quantile Expressions
It is achieved using the QUANT Expression Function - which receives 2 arguments:
- the value (typically provided as a Column name) being evaluated
- the number of buckets to create
// Put each PnL into 10 buckets
QUANT{[PnL], 10} // Put each Price into 5 buckets
QUANT{[Price], 5}Quartile & Percentile
AdapTable provides 2 additional Expression Functions to be used in the most common Quantile use cases.
QUARTILE and PERCENTILE will automatically create 4 and 100 buckets respectively.
Note
There is no need to add an "amount" argument when using these Functions
// Automatically put each PnL into 4 buckets
QUARTILE{[PnL]} // Automatically put each Price into 100 buckets
PERCENTILE{[Price]}Sort Direction
By default bucket 1 holds the smallest values.
To make bucket 1 hold the largest values instead, add 'DESC' as an optional last parameter ('ASC' is the default and can be omitted):
// Top 25% of PnL values are in Quartile 1
QUARTILE{[PnL], 'DESC'} // Most expensive 10% of Prices are in bucket 1
QUANT{[Price], 10, 'DESC'}Note
Blank values are never placed in a bucket
Tied Values
Rows with the same value are always placed in the same bucket.
That bucket is the first one the value reaches in the sort order, e.g. with QUARTILE{[PnL], 'DESC'} the joint-largest PnL values all go into Quartile 1.
Hint
This means that, when there are many ties, some buckets may contain more rows than others (and some may be empty)
- This Example demonstrates how to use the
QUANTExpression Function to calculate Quantile functions - It contains 100 Tickers each with a
Value(from 1-100 in ascending order) and an associatedType - 4 Calculated Columns are defined:
Quartile- divides the 100 Ticker Values into 4 groups - usingQUARTILEfunctionQuintile- divides the 100 Ticker Values into 5 groups - usingQUANTfunctionDecile- divides the 100 Ticker Values into 10 groups - usingQUANTfunctionPercentile- divides the 100 Ticker Values into 100 groups (so there is therefore just 1 value per group) - usingPERCENTILEfunction
- Change some Values and note how this changes all the Quantiles it is placed in
Filtering Rows
A WHERE clause restricts which rows are placed into buckets - see WHERE Clause for full details:
// Quartiles of PnL, calculated only across Open trades
QUARTILE{[PnL] WHERE [Status] = 'Open'}Note
Rows excluded by WHERE return a blank value
Grouped Quantile Expressions
As with other Aggregation Expressions, you can use the GROUP_BY keyword to group the Quantile by type.
Note
When this happens, AdapTableQL will create a set of buckets for each distinct element returned by GROUP_BY
// Create 5 buckets (Quintile) for each Currency and place the PnL
QUANT{[PnL], 5, GROUP_BY([Currency])} // For each distinct Counterparty create 10 buckets (Decile) and place the Price
QUANT{[Price], 10, GROUP_BY([Counterparty])}- This Example is similar to the one above and contains the same 4 Calculated Columns each using
QUANT - But it adds a 5th Calculated Column -
Grouped by Type- which leverages theGROUP_BYkeyword - The column creates 3 buckets for each set of Values grouped by distinct 'Type' (with a Column Display Format)
- Sort the Grid by Type to see how the
Grouped by Typecolumn creates 3 buckets for each distinct Type