Window Calculated Columns

Summary

Window Calculated Columns look up values from other rows in an ordered series
Each cell's value is derived by ordering the rows (via OVER) and referencing a row at a relative or absolute position

Calculated Columns are able to display Window Expressions.

These are an advanced form of Aggregation Expression, closely related to Cumulative Expressions.

The value of each cell derives from multiple rows, and the Window Expression orders the rows and then looks up a value from another row in that ordering.

This makes them ideal for row-relative comparisons - e.g. "the value in the previous row", "the value in the next row", or "this row's rank".

Window Functions

Window Expressions use ordered-series functions.

Each function takes an OVER clause that defines the ordering (a number or date column):

Function	Returns	Example
`LAG`	The value from a previous row (SQL `LAG`)	`LAG{[github_stars], 1, OVER([created_at])}`
`LEAD`	The value from a later row (SQL `LEAD`)	`LEAD{[github_stars], 1, OVER([created_at])}`
`FIRST`	The value from the first row in the ordering	`FIRST{[github_stars], OVER([created_at])}`
`LAST`	The value from the last row in the ordering	`LAST{[github_stars], OVER([created_at])}`
`RANK`	The 1-based rank of the row in the ordering (ties share a rank)	`RANK{OVER([github_stars], 'DESC')}`

Hint

Each function also accepts an optional GROUP_BY([col]) clause
This means the lookup (or rank) is computed within each partition rather than across the whole grid

Developer Guide

Defining a Window Calculated Column

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

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'prev-stars',
      FriendlyName: 'Previous Stars',
      Expression: 'LAG{[github_stars], 1, OVER([created_at])}',
      CalculatedColumnSettings: {
        DataType: 'number',
        Filterable: true,
      },
    },
    {
      ColumnId: 'stars-rank',
      FriendlyName: 'Stars Rank',
      Expression: "RANK{OVER([github_stars], 'DESC')}",
      CalculatedColumnSettings: {
        DataType: 'number',
        Sortable: true,
      },
    },
    {
      ColumnId: 'first-stars',
      FriendlyName: 'First Stars',
      Expression: 'FIRST{[github_stars], OVER([created_at])}',
      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.

The Window Expression

This is a Window Expression evaluated by AdapTableQL by ordering the rows and looking up a value from another row. It contains:

a window function (LAG, LEAD, FIRST, LAST or RANK)
an OVER clause that sets the ordering
optionally, a GROUP_BY clause that partitions the series

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

Series

The "Series" set of 4 Window Functions return data based on a series of rows.

LAG and LEAD return the stars of the neighbouring repo in creation order
FIRST and LAST return the stars of the earliest and latest repo

Note

All four use OVER([created_at])

Window Calculated Columns: Series

Fork Fork

This demo contains 4 Calculated Columns that read another row's Github Stars, all ordered OVER([created_at]):
- Previous Stars - LAG with offset 1, the repo created immediately before this one
- Next Stars - LEAD with offset 1, the repo created immediately after this one
- First Stars - FIRST, the earliest repo (the same value on every row)
- Last Stars - LAST, the latest repo (the same value on every row)
The Grid is sorted by Created ascending, so Previous Stars and Next Stars line up with the rows above and below

Try It Out

Compare Previous Stars and Next Stars with the Github Stars of the rows immediately above and below
First Stars and Last Stars stay the same on every row

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Window Series Calculated Columns',
  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',
            'created_at',
            'github_stars',
            'prev-stars',
            'next-stars',
            'first-stars',
            'last-stars',
          ],
          ColumnSorts: [{ColumnId: 'created_at', SortOrder: 'Asc'}],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'prev-stars',
          FriendlyName: 'Previous Stars',
          Expression: 'LAG{[github_stars], 1, OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'next-stars',
          FriendlyName: 'Next Stars',
          Expression: 'LEAD{[github_stars], 1, OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'first-stars',
          FriendlyName: 'First Stars',
          Expression: 'FIRST{[github_stars], OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'last-stars',
          FriendlyName: 'Last Stars',
          Expression: 'LAST{[github_stars], OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-created_at',
          Scope: {
            ColumnIds: ['created_at'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

Rank

Another use case for Window functions is to return a row's place in the ordering.

This is done using the RANK function.

It has 2 additional arguments:

'DESC' - reverses the order (e.g. the repo with the most stars is rank 1)
GROUP_BY - creates 1 ranking per element, e.g. GROUP_BY([language]) creates a ranking for each language

Window Calculated Columns: Rank

Fork Fork

This demo contains 2 Calculated Columns that use RANK:
- Stars Rank - every repo ranked by Github Stars, so the most stars is rank 1 (ties share a rank)
- Rank within Language - the same ranking, restarted inside each Language with GROUP_BY([language])
The Grid is sorted by Github Stars descending, so rank 1 is the top row

Try It Out

Group the Grid by Language and see Rank within Language restart for each Language

Loading demo…

Find Out More

See Window Expressions for more information

PreviousQuantile Calculated Columns NextReferencing Calculated Columns