> For the complete documentation index, see [llms.txt](https://docs.toucanai.cloud/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.toucanai.cloud/references/semantic-chart-reference.md).

# Semantic chart reference

{% hint style="info" %}
**Target Audience**: Makers who want the field-level reference behind semantic charts generated by AI or configured manually.
{% endhint %}

### Semantic chart config

A semantic chart is defined as a single configuration object:

```
{
  tables,
  joins?,
  extra?,
  ...chart-specific slots,
  ordering?
}
```

The chart does not rely on a separate aggregated query definition. Instead, the semantic layer reads this configuration and computes the result from it.

| Field       | Role                                                                                       |
| ----------- | ------------------------------------------------------------------------------------------ |
| `tables`    | The list of source tables used by the chart. The first table is the main source.           |
| `joins`     | Optional join definitions between tables.                                                  |
| `extra`     | Optional chart-scoped helper metrics. They are not exposed as persisted, reusable metrics. |
| chart slots | The dimensions and metrics required by the chart type.                                     |
| `ordering`  | Optional sorting, limiting, and per-group ranking rules.                                   |

***

### Metric object

A metric can carry both calculation logic and display hints.

```
{
  name,
  expression,
  label?,
  where?,
  having?,
  normalization?,
  timeShift?,
  unit?,
  format?
}
```

| Field           | Role                                                                     |
| --------------- | ------------------------------------------------------------------------ |
| `name`          | Internal metric identifier, also used when another metric references it. |
| `expression`    | The metric formula.                                                      |
| `label`         | Optional user-facing label.                                              |
| `where`         | Row filter applied before aggregation.                                   |
| `having`        | Filter applied after the metric has been computed.                       |
| `normalization` | Optional share calculation.                                              |
| `timeShift`     | Optional comparison with a previous period.                              |
| `unit`          | Display suffix such as `kg`, `users`, or `€/mo`.                         |
| `format`        | Optional numeric display format.                                         |

Two metric families exist:

* **Simple metric**: uses columns and aggregations, such as `SUM(amount)` or `SUM(revenue) / SUM(cost)`.
* **Derived metric**: uses other metric names instead of columns, such as `` `men count` / `women count` ``.

***

### Expression DSL

Metric expressions are small chart formulas, not SQL snippets.

Supported elements:

* Aggregations: `SUM`, `AVG`, `MIN`, `MAX`, `COUNT`, `COUNT_DISTINCT`
* Numeric literals: `42`, `3.14`
* Operators: `+`, `-`, `*`, `/`, `%`
* Parentheses
* Identifiers: either real column names, or metric names in the case of derived metrics.

Examples:

```
SUM(revenue)
SUM(quantity * unit_price)
SUM(revenue) / COUNT_DISTINCT(order_id)
SUM(revenue) - SUM(discount)
```

Rules:

* Every column reference must be inside an aggregation.
* Nested aggregations such as `AVG(SUM(amount))` are not supported.
* A single expression is either simple or derived. It cannot mix raw aggregations and metric references.
* SQL constructs such as `CASE`, `WHEN`, `IF`, `COALESCE`, `CAST`, `FILTER`, and `OVER` are not supported in the expression itself.
* If the column name contains spaces or special characters, use `"` or `` ` `` quotes around them.

For introductory examples, see [Configure a chart with dimensions and metrics](/build/charts/how-to/configure-a-chart-with-dimensions-and-metrics.md).

***

### Filters

Metrics support two filtering stages.

#### `where`

`where` is a **row filter**. It restricts the rows that contribute to the metric before aggregation.

Example:

```
SUM(amount) where segment = premium
```

Use this for requests such as:

* premium revenue only
* women employees only
* transactions in Q3 2024

{% hint style="warning" %}
Note that this will affect `normalization` and `timeShift.evolution`, so use it to shape the reference set. For instance, to get the share of Q3 2024 transactions relative to all 2024 transactions, the `where` should restrict to 2024 ; cf. `having`.
{% endhint %}

#### `having`

`having` is a **post-aggregation filter**. It keeps or removes rows after the metric is computed.

Use this for requests such as:

* keep only categories above a threshold
* compute all shares, then keep only the USA row

#### Condition structure

Conditions can be:

* a leaf: `{ column, operator, value }`
* a branch: `{ and: [...] }` or `{ or: [...] }`

Common operators:

* equality: `eq`, `ne`
* comparisons: `gt`, `ge`, `lt`, `le`
* sets: `in`, `nin`
* regex-style match: `matches`, `notmatches`
* null checks: `isnull`, `notnull`
* date bounds: `from`, `until`

For date columns, prefer `from` and `until` with ISO dates:

```
from: 2024-07-01
until: 2024-09-30
```

***

### Normalization

Normalization turns an aggregate into a share.

Supported types:

| Type             | Meaning                                           |
| ---------------- | ------------------------------------------------- |
| `share-of-total` | The row's contribution to the chart's grand total |
| `share-of-group` | The row's contribution within one or more groups  |

Examples:

* product category share of total revenue
* gender share within each team
* top sellers by revenue share within each region

{% hint style="warning" %}
**Normalization rule**

If a metric is already a ratio or percentage, such as margin rate or conversion rate, do not add a second normalization unless you explicitly want a share of that ratio.
{% endhint %}

Also note that a metric's `where` filter affects both the numerator and the denominator of the normalization. If you want to compute all shares and keep only one row, use `having`, not `where`.

***

### Time comparison with `timeShift`

`timeShift` compares a metric with a previous period.

Shape:

```
{
  dimension,
  granularity,
  offset,
  evolution?
}
```

| Field         | Role                                                          |
| ------------- | ------------------------------------------------------------- |
| `dimension`   | The name of a datetime dimension already present in the chart |
| `granularity` | The comparison step: year, quarter, month, week, or day       |
| `offset`      | Number of periods back, usually `-1`                          |
| `evolution`   | Optional comparison mode                                      |

`evolution` can be:

* omitted: previous period raw value
* `absolute`: current minus previous
* `percentage`: `(current - previous) / previous`

Examples:

* previous month's revenue
* month-over-month revenue change
* year-over-year growth percentage

{% hint style="warning" %}
**Time comparison rule**

Time comparison depends on a datetime dimension already present in the chart. It is not expressed as arithmetic on a manually chosen "previous period" column.
{% endhint %}

When a single metric uses both normalization and `timeShift`, normalization is applied first, then the time comparison. This means "growth of share", not "share of growth".

***

### Derived metrics and `extra.metrics`

Derived metrics let you compute one metric from other metrics by name.

Example:

```
men count
women count
men to women ratio
```

The helper metrics that should not be displayed go in `extra.metrics`. Chart-level helper metrics are scoped to a single chart and are not exposed as persisted, reusable metrics.

Typical uses:

* ratio of two differently filtered populations
* comparison of two business measures with different row filters
* advanced time-comparison patterns

#### Pass-through aliases

A visible metric can also be a simple alias of a helper metric. This is useful when you want to apply display logic or normalization to an already computed helper.

#### Share of growth vs growth of share

These are different concepts:

* **Growth of share**: one metric carries both normalization and `timeShift`
* **Share of growth**: a helper metric computes the time-shifted value first, then a visible alias normalizes that helper across a group

This distinction is especially important for period-over-period composition analysis.

***

### Dimensions

Semantic charts use two dimension types.

| Type       | Role                                                           |
| ---------- | -------------------------------------------------------------- |
| `category` | A qualitative breakdown such as team, region, product category |
| `datetime` | A time axis based on a date or timestamp column                |

Category dimension shape:

```
{
  name,
  type: "category",
  tableColumn: { tableId, column }
  transforms?
}
```

Datetime dimension shape:

```
{
  name,
  type: "datetime",
  tableColumn: { tableId, column },
  granularityValues: ["month"],
  transforms?
  format?
}
```

Typical granularities are `year`, `quarter`, `month`, `week`, and `day`.

#### Format and transforms

The optional `format` is the how it will be displayed ; use `transforms` to `convert` columns in proper dates if necessary.

```
transforms: [
  { operation: "convert", to: "datetime", format: "%Y-%m-%d" }
]
```

| `to`       | Effect                                                  |
| ---------- | ------------------------------------------------------- |
| `text`     | Parse the column as text                                |
| `integer`  | Parse the column as a whole number                      |
| `float`    | Parse the column as a decimal number                    |
| `datetime` | Parse the column as a date/timestamp; requires `format` |

When converting to `datetime`, `format` is required and uses chrono's [strftime](https://docs.rs/chrono/latest/chrono/format/strftime/index.html)/`strptime` syntax (for example `%Y-%m-%d`). Inspect the actual column values to determine the correct pattern.

***

### Ordering

`ordering` controls sorting and limiting.

```
{
  predicates,
  limit?,
  groups?
}
```

| Field        | Role                                          |
| ------------ | --------------------------------------------- |
| `predicates` | Sort rules by dimension or metric             |
| `limit`      | Maximum number of rows                        |
| `groups`     | Optional partition keys for per-group ranking |

Use cases:

* top 10 products by revenue
* top 3 sellers within each region
* ascending month order on a time series

If `groups` is set, the limit applies within each group instead of globally.

***

### Formatting

Formatting changes display, not business logic.

#### Metric formatting

* `unit`: free-text suffix such as `kg`, `users`, `€/mo`
* `format`: numeric rendering options, including decimal, currency, and percent

Common examples:

* currency with no decimals
* decimal with two digits
* unit suffix for operational metrics

Normalized metrics already render naturally as percentages. In most cases, you should not force a second percent transformation.

#### Datetime formatting

Datetime dimensions can also define a display format, for example:

* `Jan 2024`
* `Q1 2025`
* short date or long date styles

Formatting should reflect how you want values to appear, not how they are computed.

***

### Chart slots by type

| Chart type      | Required slots                  | Optional slots                                     |
| --------------- | ------------------------------- | -------------------------------------------------- |
| **Value**       | `value`                         | `variation`                                        |
| **Bar**         | `bar`, `barValue`               | `barGroup`, `lineValue`, `orientation`, `ordering` |
| **Stacked bar** | `bar`, `barValue`, `barSegment` | `ordering`                                         |
| **Line**        | `x`, `y`                        | `series`, `ordering`                               |
| **Circular**    | `dimension`, `metric`           | —                                                  |
| **Heatmap**     | `x`, `y`, `metric`              | —                                                  |
| **Table**       | `dimensions`, `metrics`         | `ordering`                                         |

Typical reading:

* line chart: one datetime dimension and one or more metrics
* bar chart: one category dimension and one metric, optionally split by a second dimension
* table: one dimension or more, plus one metric or more

For worked examples, see [Build charts with AI](/faq-and-troubleshooting/ai-assisted-creation/build-charts-with-semantic-ai.md) and [Configure a chart with dimensions and metrics](/build/charts/how-to/configure-a-chart-with-dimensions-and-metrics.md).

***

### Conclusion

The semantic chart configuration makes advanced chart behavior explicit: dimensions, formulas, filters, shares, time comparisons, and ranking all live in one configuration.

**Suggested next steps:** [Build charts with AI](/faq-and-troubleshooting/ai-assisted-creation/build-charts-with-semantic-ai.md) or [Configure a chart with dimensions and metrics](/build/charts/how-to/configure-a-chart-with-dimensions-and-metrics.md)