# Observable Calculated Columns

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

- Observable Calculated Columns display a live value derived from **change history** (and time)
- Each cell uses an [Observable Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression-observable/index.md) (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](https://www.adaptabletools.com/docs/adaptable-ql-expression-observable/index.md).

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).

- 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{}`                                   |

See [Observable Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-observable/index.md) for the full catalogue and windows (e.g. `WITHIN`, `LAST_N` etc.)

### Defining an Observable Calculated Column

There are 5 properties that you need to provide when defining an Observable 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.

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.

This is an [Observable Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression-observable/index.md) evaluated by [AdapTableQL](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md).

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

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

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

```tsx [[1,4, "ColumnId"],[1,13, "ColumnId"],[1,23, "ColumnId"],[2,5, "FriendlyName"],[2,14, "FriendlyName"],[2,24, "FriendlyName"],[3,6, "Type"],[3,15, "Type"],[3,25, "Type"],[4,7, "Expression"],[4,16, "Expression"],[4,26, "Expression"],[5,9, "DataType"],[5,18, "DataType"],[5,28, "DataType"],[6,19, "CellDataType"]]
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',
      },
    },
  ],
},
```

**Example: Observable Calculated Columns**

Calculated Columns from change history

- 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

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
  rowData.ts            [template]
  styles.css            [template]
  tickingDataHelper.ts  [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: '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',
          },
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo} from '@adaptabletools/adaptable';
import {tickNumericData} from './tickingDataHelper';

export const onAdaptableReady = ({adaptableApi}: AdaptableReadyInfo) => {
  tickNumericData(adaptableApi, 500, ['github_stars']);
};
```

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

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

**Example: Observable: Day High and Day Low**

Day High and Day Low of a ticking column

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

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
  rowData.ts            [template]
  styles.css            [template]
  tickingDataHelper.ts  [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: '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},
          },
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo} from '@adaptabletools/adaptable';
import {tickNumericData} from './tickingDataHelper';

export const onAdaptableReady = ({adaptableApi}: AdaptableReadyInfo) => {
  // Tick Github Stars often so Day High / Day Low move visibly during the demo
  tickNumericData(adaptableApi, 400, ['github_stars']);
};
```