Quantile (Aggregation) Expressions

View as Markdown

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

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
Loading demo…

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])}
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
Loading demo…