Observable Calculated Columns

View as Markdown

Summary

Observable Calculated Columns display a live value derived from change history (and time)
Each cell uses an Observable Expression (curly braces)
Ideal for tick counts, net / percent moves, highs and lows, previous values, and row age

Calculated Columns can include Observable Columns that display Observable Expressions.

They read AdapTable's recorded cell/row history and return a scalar that updates as data ticks.

For windowed or clock-based Expressions it returns the value as time passes.

This is contrast to Standard Columns (evaluate current-row values) or Aggregations (evaluate multiple rows).

Hint

Observable Functions always use curly braces — e.g. CHANGE_COUNT{[github_stars], WITHIN(5m)}
e.g. compare standard PERCENT_CHANGE([bid]) vs Observable PERCENT_CHANGE{[bid], WITHIN(1h)}

Observable Functions

There are many Observable Functions available.

The ones which are most commonly used in Calculated Columns are:

Function	Returns	Example
`CHANGE_COUNT`	How many times a cell has changed in the window	`CHANGE_COUNT{[github_stars], WITHIN(5m)}`
`NO_CHANGES`	True when the cell has been quiet for the window	`NO_CHANGES{[github_stars], WITHIN(20s)}`
`SINCE_CHANGE`	How long ago the cell last changed	`SINCE_CHANGE{[github_stars]}`
`CHANGED_AT`	When the cell last changed	`CHANGED_AT{[github_stars]}`
`NET_CHANGE`	Current value minus start-of-window value	`NET_CHANGE{[github_stars], WITHIN(1h)}`
`PERCENT_CHANGE`	Percent move over the window	`PERCENT_CHANGE{[github_stars], WITHIN(1h)}`
`HIGHEST` / `LOWEST`	Extreme values in the window	`HIGHEST{[github_stars], WITHIN(1d)}`
`PREVIOUS`	Value before the last change (or n ago)	`PREVIOUS{[github_stars]}`
`ROW_AGE`	How long ago this row was added (duration)	`ROW_AGE{}`

Find Out More

See Observable Expressions for the full catalogue and windows (e.g. WITHIN, LAST_N etc.)

Developer Guide

Defining an Observable Calculated Column

There are 5 properties that you need to provide when defining an Observable Calculated Column:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'ticks-5m',
      FriendlyName: 'Ticks (5m)',
      Type: 'Observable',
      Expression: 'CHANGE_COUNT{[github_stars], WITHIN(5m)}',
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'row-age',
      FriendlyName: 'Age',
      Type: 'Observable',
      Expression: 'ROW_AGE{}',
      CalculatedColumnSettings: {
        DataType: 'number',
        CellDataType: 'duration',
      },
    },
    {
      ColumnId: 'day-high',
      FriendlyName: 'Day High',
      Type: 'Observable',
      Expression: 'HIGHEST{[github_stars], WITHIN(1d)}',
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
  ],
},

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.

Type

Set Type: 'Observable' so the Wizard and validation treat the Expression as Observable.

If omitted, AdapTable can infer Observable from curly-brace Observable Functions in the Expression.

The Observable Expression

This is an Observable Expression evaluated by AdapTableQL.

It is the Expression string of the Calculated Column and must include at least one Observable Function.

Data Type (and optional Cell Data Type)

Provide DataType in CalculatedColumnSettings (number, text, date or boolean).

Hint

For duration values such as ROW_AGE{}, use DataType: 'number' with CellDataType: 'duration'
AdapTable formats cell as human duration (e.g. 12.3s, 4m 05s)

Observable Calculated Columns

Fork Fork

This demo contains 4 Observable Calculated Columns over ticking Github Stars:
- Ticks (5m) — CHANGE_COUNT{[github_stars], WITHIN(5m)}
- Net (30s) — NET_CHANGE{[github_stars], WITHIN(30s)}
- Session range — HIGHEST{[github_stars], WITHIN(1h)} - LOWEST{[github_stars], WITHIN(1h)}
- Age — ROW_AGE{} with CellDataType: 'duration' (— for rows present when the Grid loaded)
Note: the liveRefreshIntervalMs property in Expression Options is set which means that windowed values keep updating as time passes

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Observable Calculated Columns',
  expressionOptions: {
    liveRefreshIntervalMs: 1000,
  },
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'ticks-5m',
            'net-30s',
            'session-range',
            'row-age',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'ticks-5m',
          FriendlyName: 'Ticks (5m)',
          Type: 'Observable',
          Expression: 'CHANGE_COUNT{[github_stars], WITHIN(5m)}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'net-30s',
          FriendlyName: 'Net (30s)',
          Type: 'Observable',
          Expression: 'NET_CHANGE{[github_stars], WITHIN(30s)}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'session-range',
          FriendlyName: 'Session range',
          Type: 'Observable',
          Expression:
            'HIGHEST{[github_stars], WITHIN(1h)} - LOWEST{[github_stars], WITHIN(1h)}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'row-age',
          FriendlyName: 'Age',
          Type: 'Observable',
          Expression: 'ROW_AGE{}',
          CalculatedColumnSettings: {
            DataType: 'number',
            CellDataType: 'duration',
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

High / Low

HIGHEST and LOWEST are among the most common Observable Calculated Columns.

They are frequently used to track the session or daily extreme of a ticking price (or any numeric column).

Each Function returns the extreme value seen in the provided timeframe (or since the Grid loaded).

Note

Until the watched cell has changed at least once, the value is null

Observable: Day High and Day Low

Fork Fork

Github Stars ticks every 400ms (±10) and flashes on ANY_CHANGE()
Day High — HIGHEST{[github_stars], WITHIN(1d)}
Day Low — LOWEST{[github_stars], WITHIN(1d)}
Compare the live Github Stars value against the two extremes as ticks arrive
maxTimeframeSize in Expression Options is raised to 1 day, as the default (8 hours) is shorter than WITHIN(1d)

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Observable Day High Low',
  expressionOptions: {
    liveRefreshIntervalMs: 1000,
    // 1 day - the default (8 hours) is too short for WITHIN(1d)
    maxTimeframeSize: 24 * 60 * 60 * 1000,
  },
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'FlashingCell'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn', 'FlashingCell'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    FlashingCell: {
      FlashingCellDefinitions: [
        {
          Name: 'flash-github-stars',
          Type: 'Change',
          Scope: {ColumnIds: ['github_stars']},
          Rule: 'ANY_CHANGE()',
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'day-high',
            'day-low',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'day-high',
          FriendlyName: 'Day High',
          Type: 'Observable',
          Expression: 'HIGHEST{[github_stars], WITHIN(1d)}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'day-low',
          FriendlyName: 'Day Low',
          Type: 'Observable',
          Expression: 'LOWEST{[github_stars], WITHIN(1d)}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'format-stars',
          Scope: {ColumnIds: ['github_stars', 'day-high', 'day-low']},
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {FractionDigits: 0},
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

PreviousStandard Calculated Columns NextAggregated Calculated Columns