Expressions
Aggregation Expressions

Quantile (Aggregation) Expressions

Summary

  • Creating buckets (e.g. Quartiles, Percentiles) using the QUANT function

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 comment Quantile use cases.

QUARTILE and PERCENTILE will automatically create 4 and 10 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])
AdapTableQL: Quantile Aggregation
Fork
  • This Example demonstrates how to use the QUANT Expression Function to calculate Quantile functions
  • It contains 100 Tickers each with a Value (from 1-100 in ascending order) and an associated Type
  • 4 Calculated Columns are defined:
    • Quartile - divides the 100 Ticker Values into 4 groups - using QUARTILE function
    • Quintile - divides the 100 Ticker Values into 5 groups - using QUANT function
    • Decile - divides the 100 Ticker Values into 10 groups - using QUANT function
    • Percentile - divides the 100 Ticker Values into 100 groups (so there is therefore just 1 value per group) - using PERCENTILE function
Try It Out
  • Change some Values and note how this changes all the Quantiles it is placed in

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]))
AdapTableQL: Grouped Quantile Aggregation
Fork
  • 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 the GROUP_BY keyword
  • The column creates 3 buckets for each set of Values grouped by distinct 'Type' (with a Column Display Format)
Try It Out
  • Sort the Grid by Type to see how the Grouped by Type column creates 3 buckets for each distinct Type
PreviousCumulative ExpressionsNextObservable Expressions