Quantile Calculated Columns

Summary

Quantile Calculated Columns allow 'buckets' of values to be created
Each cell is placed into a specific bucket based on its value relative to others in the group

Calculated Columns are able to display Quantile Expressions.

These are an advanced form of Aggregation Expressions that perform quantile aggregations.

The Calculated Colunn is evaluated by placing each cell's value into a different bucket based on its value relative to others in the group.

Quantile Aggregation leverage the QUANT Expression Function.

This receives a value to evaluate (typically a column name) and the number of buckets to create.

Developer Guide

Defining a Quantile Calculated Column

There are 5 properties that you need to provide when defining a Quantile Calculated Column:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'quartile',
      FriendlyName: 'Quartile',
      Query: {
        AggregatedScalarExpression:
          'QUANT([value], 4)',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
        Filterable: true,
      },
    },
    {
      ColumnId: 'quartile-by-type',
      FriendlyName: 'Quartile by Type',
      Query: {
        AggregatedScalarExpression:
          'QUANT([value], 4, GROUP_BY([type]))',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
        Sortable: true,
      },
    },
  ],
},

Id for the Calculated Column

This ColumnId value is used to reference the Column in AdapTable State and in other objects (e.g. Layouts).

Column Friendly Name (if required)

This value is used to refer to the Column in AdapTable UI. It only needs to be provided if the ColumnId value is unsuitable.

The Quantile Expression

This is a Quantile Expression evaluated by AdapTableQL by placing each row's value into a bucket relative to other rows.

It is wrapped inside a Query property under the AggregatedScalarExpression key and uses the QUANT function — a column reference, a number of buckets, and optionally a GROUP_BY clause.

Data Type

The Data Type of the Calculated Column should be provided in CalculatedColumnSettings.

It is a Cell Data Type value and the object's only mandatory property. Common values are number, text or date.

Calculated Column Settings (Optional)

Additional Settings for the Calculated Column (other than mandatory DataType) can be provided, including:

Filterable
Pivotable
Sortable
Groupable

Quantile Calculated Columns

Fork Fork

This Example demonstrates how to use the QUANT Expression Function to calculate Quantile functions
It contains 100 Tickers each with a Value and a Type
The provided Value is from 1 to 100 in ascending order (in order to demonstrate how Quantiles work)
4 Calculated Columns are provided in the Initial Adaptable State - each using QUANT:
- Quartile - divides the 100 Ticker Values into 4 groups
- Quintile - divides the 100 Ticker Values into 5 groups
- Decile - divides the 100 Ticker Values into 10 groups
- Percentile - divides the 100 Ticker Values into 100 groups (so there is therefore just 1 value per group)
A 5th Calculated Column illustrates how to group Quantiles by levering the GROUP_BY keyword:
- Grouped by Type - creates 3 bucket for each set of Values in each group of a 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 dinstinct Type
Change some Values and note how this changes all the Quantiles it is placed in

import {AdaptableOptions} from '@adaptabletools/adaptable';

export const adaptableOptions: AdaptableOptions = {
  primaryKey: 'Id',
  adaptableId: 'Quantile Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'Id',
            'Ticker',
            'Value',
            'Type',
            'Quartile',
            'Quintile',
            'Decile',
            'Percentile',
            'TypeGroup',
          ],
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'Quartile',
          Query: {
            AggregatedScalarExpression: 'QUANT([Value], 4)',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'Quintile',
          Query: {
            AggregatedScalarExpression: 'QUANT([Value], 5)',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'Decile',
          Query: {
            AggregatedScalarExpression: 'QUANT([Value],  10)',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'Percentile',
          Query: {
            AggregatedScalarExpression: 'QUANT([Value], 100)',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'TypeGroup',
          Query: {
            AggregatedScalarExpression: 'QUANT([Value], 3, GROUP_BY([Type]))',
          },
          FriendlyName: 'Grouped by Type',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-TypeGroup',
          Scope: {
            ColumnIds: ['TypeGroup'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              Prefix: 'Bucket: ',
            },
          },
        },
      ],
    },
  },
};

Find Out More

See Quantile Aggregation Scalar Expressions for more information

PreviousCumulative Calculated Columns NextReferencing Calculated Columns