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