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.

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

Framework: