# Window Calculated Columns

Canonical page: https://www.adaptabletools.com/docs/handbook-calculated-column-window

- 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](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md).

These are an advanced form of [Aggregation Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression-aggregation/index.md), closely related to [Cumulative Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-cumulative/index.md).

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')}`          |

- 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

### Defining a Window Calculated Column

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

This `ColumnId` value is used to reference the Column in AdapTable State and in other objects (e.g. [Layouts](https://www.adaptabletools.com/docs/handbook-layouts/index.md)).

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

This is a [Window Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md) evaluated by [AdapTableQL](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) 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

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

It is a [Cell Data Type](https://www.adaptabletools.com/docs/dev-guide-aggrid-cell-data-types/index.md) value and the object's only mandatory property. Common values are `number`, `text` or `date`.

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

- `Filterable`
- `Pivotable`
- `Sortable`
- `Groupable`

```tsx [[1,4, "ColumnId"],[1,13, "ColumnId"],[1,22, "ColumnId"],[2,5, "FriendlyName"],[2,14, "FriendlyName"],[2,23, "FriendlyName"],[3,6, "Expression"],[3,15, "Expression"],[3,24, "Expression"],[4,8, "DataType"],[4,17, "DataType"],[4,26, "DataType"],[5,9, "Filterable"],[5,18, "Sortable"]]
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',
      },
    },
  ],
},
```

### 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

All four use `OVER([created_at])`

**Example: Window Calculated Columns: Series**

Previous, next, first and last in creation order

- 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

- 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

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
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',
            },
          },
        },
      ],
    },
  },
};
```

**`src/gridOptions.ts`**

```ts
import {GridOptions, themeBalham} from 'ag-grid-enterprise';

import {rowData, WebFramework} from './rowData';
import {columnDefs} from './columnDefs';

// The shared rows store Created as `Date.toDateString()` (`Fri Apr 03 2009`).
// OVER compares that text, so Friday sorts before Monday. ISO dates sort in
// the same order as the Created column, and LAG / LEAD line up with the rows.
const createdAtIso = (value: string): string => {
  const date = new Date(value);
  if (Number.isNaN(date.getTime())) {
    return value;
  }
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  return `${date.getFullYear()}-${month}-${day}`;
};

export const gridOptions: GridOptions<WebFramework> = {
  theme: themeBalham,
  defaultColDef: {
    resizable: true,
    sortable: true,
    editable: true,
    filter: true,
    floatingFilter: true,
  },
  columnDefs: columnDefs,
  rowData: rowData.map(row => ({
    ...row,
    created_at: createdAtIso(row.created_at),
  })),
  sideBar: true,
  suppressMenuHide: true,
  columnMenu: 'new',
  cellSelection: true,
  statusBar: {
    statusPanels: [
      {statusPanel: 'agTotalRowCountComponent', align: 'left'},
      {statusPanel: 'agFilteredRowCountComponent'},
      {
        key: 'Center Panel',
        statusPanel: 'AdaptableStatusPanel',
        align: 'center',
      },
    ],
  },
};
```

### 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

**Example: Window Calculated Columns: Rank**

Rank across the Grid, and within each Language

- 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

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

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Window Rank 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',
            'language',
            'github_stars',
            'stars-rank',
            'stars-rank-by-language',
          ],
          ColumnSorts: [{ColumnId: 'github_stars', SortOrder: 'Desc'}],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'stars-rank',
          FriendlyName: 'Stars Rank',
          Expression: "RANK{OVER([github_stars], 'DESC')}",
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'stars-rank-by-language',
          FriendlyName: 'Rank within Language',
          Expression: "RANK{GROUP_BY([language]), OVER([github_stars], 'DESC')}",
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
  },
};
```

See [Window Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md) for more information