Observable Calculated Columns

Until now grid users could see the number on the row.
But they could not see the story of that number - how many times it moved, how far, whether it's gone quiet, or how long a new row has been sitting there.
Enter Observable Calculated Columns, a decisive and pivotal breakthrough for advanced DataGrid users.
Leveraging the power of AdapTableQL, they turn your grid's change history into live cells: ticks, highs & lows, net & percent moves, previous values and row age — using human-readable expressions, not a time-series service.

Danielle Nagler, CEO, AdapTable Tools

An Observable Calculated Column is a new Calculated Column type in AdapTable.

Its AdapTableQL Expression reads change history and the clock.

The cell updates when the watched value ticks, and again as time passes.

A normal Calculated Column sees the current row.

An Observable one sees what that cell has done.

Why Observable Calculated Columns are a DataGrid Game-Changer

Most grids can show the latest price, but none can answer the question the desk actually asks.
- How busy is this row? CHANGE_COUNT is a tick counter for the last 30 seconds, with no extra store
- Which way did it go? NET_CHANGE & PERCENT_CHANGE are the move over that same window
- Where has it been? HIGHEST & LOWEST keep the extremes. PREVIOUS keeps the value before last tick
- Has a row gone stale? NO_CHANGES & SINCE_CHANGE say if it's stopped; CHANGED_AT tells when it last moved
- Is this row new? ROW_AGE is the clock for a row added after the Grid loaded
The same column then sorts, filters and formats like any other number, date or boolean in the Layout.

Each Observable Expression uses an Observable Function.

We provide 2 demos below that each illustrate a group of Observable Functions:

Moves - Shows how far the GitHub Stars column has moved
Timing - Shows when a row last moved, and how old the row is

Each function is wrapped inside a dedicated Observable Calculated Column.

Note

In both demos GitHub Stars ticks on rows whose Language is JavaScript or TypeScript. HTML rows stay still, and so does the Python row added in the Timing demo
We set liveRefreshIntervalMs to 1000, so windowed values keep moving between ticks

Observing Moves

A common use of Observable Calculated Columns is to spot changes in cell values.

In the demo below the GitHub Stars column flashes on every tick.

6 Observable Calculated Columns all follow that column:

Function	Column	What the cell shows
`CHANGE_COUNT`	`Ticks (30s)`	How many times Stars moved in the last 30 seconds
`NET_CHANGE`	`Net (30s)`	Stars now, minus Stars at the start of that window
`PERCENT_CHANGE`	`Move % (30s)`	That same move, as a percent
`PREVIOUS`	`Previous`	The value before the last tick
`HIGHEST`	`High (1m)`	The high of the last minute
`LOWEST`	`Low (1m)`	The low of the last minute

Observable Calculated Columns: Moves

Fork Fork

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Observable Calculated Columns Moves',
  expressionOptions: {
    liveRefreshIntervalMs: 1000,
  },
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    FlashingCell: {
      FlashingCellDefinitions: [
        {
          Name: 'flash-github-stars',
          Type: 'Change',
          Scope: {ColumnIds: ['github_stars']},
          Rule: 'ANY_CHANGE()',
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Moves',
      Layouts: [
        {
          Name: 'Moves',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'ticks',
            'net',
            'percent',
            'previous',
            'high',
            'low',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'ticks',
          FriendlyName: 'Ticks (30s)',
          Type: 'Observable',
          Expression: 'CHANGE_COUNT{[github_stars], WITHIN(30s)}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'net',
          FriendlyName: 'Net (30s)',
          Type: 'Observable',
          Expression: 'NET_CHANGE{[github_stars], WITHIN(30s)}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'percent',
          FriendlyName: 'Move % (30s)',
          Type: 'Observable',
          Expression: 'PERCENT_CHANGE{[github_stars], WITHIN(30s)}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'previous',
          FriendlyName: 'Previous',
          Type: 'Observable',
          Expression: 'PREVIOUS{[github_stars]}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'high',
          FriendlyName: 'High (1m)',
          Type: 'Observable',
          Expression: 'HIGHEST{[github_stars], WITHIN(1m)}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'low',
          FriendlyName: 'Low (1m)',
          Type: 'Observable',
          Expression: 'LOWEST{[github_stars], WITHIN(1m)}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'format-whole-numbers',
          Scope: {
            ColumnIds: [
              'github_stars',
              'ticks',
              'net',
              'previous',
              'high',
              'low',
            ],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {FractionDigits: 0},
          },
        },
        {
          Name: 'format-percent',
          Scope: {ColumnIds: ['percent']},
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {FractionDigits: 1},
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

Try It Out

Watch Stars flash. The Ticks column climbs, Net and Move % swing, Previous lags one tick behind, and High / Low widen
Those columns stay empty until a row's first tick. HTML rows never tick, so Ticks stays at 0 and the other columns stay empty

Observing Timing

Another common Observable Calculated Column use case is to watch for when things changed (or didn't change), and display timings.

In the demo below, 4 Observable Calculated Columns display timing-related information:

Function	Column	What the cell shows
`SINCE_CHANGE`	`Since change`	How long ago Stars last moved
`CHANGED_AT`	`Changed at`	When that move happened
`NO_CHANGES`	`Quiet (15s)`	True once Stars has been still for 15 seconds
`ROW_AGE`	`Age`	How long ago this row was added

Observable Calculated Columns: Timing

Fork Fork

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Observable Calculated Columns Timing',
  expressionOptions: {
    liveRefreshIntervalMs: 1000,
  },
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'light'},
    FlashingCell: {
      FlashingCellDefinitions: [
        {
          Name: 'flash-github-stars',
          Type: 'Change',
          Scope: {ColumnIds: ['github_stars']},
          Rule: 'ANY_CHANGE()',
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Timing',
      Layouts: [
        {
          Name: 'Timing',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'since',
            'changed-at',
            'quiet',
            'age',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'since',
          FriendlyName: 'Since change',
          Type: 'Observable',
          Expression: 'SINCE_CHANGE{[github_stars]}',
          CalculatedColumnSettings: {
            DataType: 'number',
            CellDataType: 'duration',
          },
        },
        {
          ColumnId: 'changed-at',
          FriendlyName: 'Changed at',
          Type: 'Observable',
          Expression: 'CHANGED_AT{[github_stars]}',
          CalculatedColumnSettings: {DataType: 'date'},
        },
        {
          ColumnId: 'quiet',
          FriendlyName: 'Quiet (15s)',
          Type: 'Observable',
          Expression: 'NO_CHANGES{[github_stars], WITHIN(15s)}',
          CalculatedColumnSettings: {DataType: 'boolean'},
        },
        {
          ColumnId: 'age',
          FriendlyName: 'Age',
          Type: 'Observable',
          Expression: 'ROW_AGE{}',
          CalculatedColumnSettings: {
            DataType: 'number',
            CellDataType: 'duration',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'format-stars',
          Scope: {ColumnIds: ['github_stars']},
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {FractionDigits: 0},
          },
        },
        {
          Name: 'format-changed-at',
          Scope: {ColumnIds: ['changed-at']},
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {Pattern: 'HH:mm:ss'},
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

Try It Out

Since change and Changed at start after the first tick
On an HTML row, Quiet becomes true after 15 seconds
A Python row is added when demo opens; Age counts on that row (so stays empty for rows already in Grid)

Unique

AG Grid will evaluate a valueGetter against the current row.

It does not keep a change log, and it does not re-evaluate a cell because a minute has passed.

This is the gap that Observable Calculated Columns fill.

AdapTable records each cell change.

An Observable expression reads that history, plus the clock, and returns one (scalar) value.

Note

The curly-brace form is what makes a function an Observable Function:

// Percent GitHub Stars has moved in the last 30 seconds
PERCENT_CHANGE{[github_stars], WITHIN(30s)}

The "usual" form of brackets is round parentheses.

This is the same function name as above but here it is a Relative Change function (note the parentheses), used in Alerts and Flashing Cells, which reads the change that just happened:

// The percent GitHub Stars moved on the latest tick
PERCENT_CHANGE([github_stars])

Nothing else on the Grid — a valueGetter, a standard Calculated Column, an aggregation — can answer these questions in real time sitting purely in the client.

Powerful

Most exciting is how Observable Expressions can be extended to create real-life, value-added queries.

Here are some examples from the demos above (where each column is one expression):

Combine with	Example
A window	`WITHIN(30s)` and `WITHIN(1m)` are sliding `LAST_N(5)` is the last five changes, when a count of ticks is the better frame
A composition	The range of a session is `HIGHEST{[github_stars], WITHIN(1m)} - LOWEST{[github_stars], WITHIN(1m)}` Move since previous tick is `[github_stars] - PREVIOUS{[github_stars]}`
The right cell type	`SINCE_CHANGE` and `ROW_AGE` are durations (`12s`, `4m 05s`) `CHANGED_AT` is a date `NO_CHANGES` is a boolean
Same expression elsewhere	A column that shows `CHANGE_COUNT{[github_stars], WITHIN(30s)}` can sit next to an Observable Alert or Format Column that uses that count as a rule

Beyond these demos

The examples above illustrate the main set of Observable functions we use as Calculated Columns today.

However the language already goes further with additional functionalities and possibilities:

ADDED_AT — when the row was added
ROWS_ADDED and ROWS_REMOVED — how many rows arrived or left, across the Grid
ALL_ROWS() — one number for the whole column, such as every Stars tick in the last minute
WHERE and TRIGGER — a filter on which changes count, or only user edits

Those belong in the next column, the next alert, or the next style.

The history is already being recorded and the possibilities are truly exciting.

Feedback

We have included all the Observable Functions that our advanced users have requested
But we are sure that once you start using them, you will think of more use cases and requirements
So contact us at support@adaptabletools.com and tell us what those are
We'll include them in future releases so AdapTable continues to give you significant competitive advantage

Observable Calculated Columns

Why Observable Calculated Columns are a DataGrid Game-Changer

Note

Observing Moves

Observing Timing

Unique

Note

Powerful

Beyond these demos

Feedback

AdapTable Resources