# Kex quick card

> The rules and examples an agent needs before writing its first Kex query: clause order, plans, filters, aggregation, joins and windows, with checked examples from the base template.

This card is short on purpose, so it can sit in an agent's context at all times. The full language is described in [Kex](kex.html). The function reference is generated from the product at `/api/documentation/kex` on each tenant host.

## Before the first query

1. **Find the plan and the names.** Plan ids differ between tenants, so call `list_plans`, then `list_value_types` and `list_dimensions` with the plan id. Use the ids (`PnL`, `YearMonth`), not the display names.
2. **Read the tenant's own guide if it has one.** Many tenants describe their model and common questions in MCP resources (`list_mcp_resources`). It says which value type answers which question.
3. **Validate, then run.** Call `validate_script` before `execute_read_script`. Read the error, fix that exact thing and validate again. Do not rewrite the query from scratch.

## The shape of a query

Clauses must come in this order. Every clause except the first is optional.

```text
<select list> [only]
[main X | include X ...]
[where <condition>]
[group by ... | having ... | order by ...]
[from plan <id> | from reference "<name>"]
[limit <n>]
```

**`from plan` comes after `where` and `order by`, not before them.** This is the most common error. Kex is not SQL: there is no `SELECT`, and there is no `FROM` at the top.

## Kex is not SQL

| SQL habit | Kex |
|---|---|
| `SELECT sum(x) FROM t WHERE ...` | `sum(x) where ... from plan 25` |
| `=` for comparison | `==` (and `!=`) |
| `'text'` | `"text"` (single quotes are dates: `'2026-05-17'`) |
| `AND`, `OR`, `NOT` | `&&`, `\|\|`, `not` |
| `GROUP BY Company` | list the dimension: `sum(x), Company`. Grouping is implicit |
| `count(DISTINCT x)` | `distinctcount(x)` |
| `avg(x)` | `average(x)` |
| `LIKE 'A%'` | `=~ "A*"` |
| `IN ('a', 'b')` | `in {"a", "b"}` |
| `x IS NULL` | `x == null` |
| `CASE WHEN c THEN a ELSE b END` | `if(c, a, b)` |
| `TOP 5` / `LIMIT 5` first | `limit 5` last |

Keywords are lowercase. Write dimension codes as text in double quotes, even when they look like numbers: `Company == "2000"`, `YearMonth == "202606"`, `Employee == "2007"`. `YearMonth.Year == "2025"` filters a whole year.

## Plans

- `from plan 25` at the end of a statement reads that statement from plan 25. A value type that is not in the default plan gives *Unknown identifier* until you add it.
- `use plan 25` on the first line sets the plan for the whole script.
- In a script with several statements, each `var` or `scalar` can end with its own `from plan`, so one script can combine plans.

## Aggregation

- The dimensions you list are the grouping: `sum(PnL), Company` gives one row per company.
- **A value type on its own keeps all its dimensions.** `sum(PnL)` without `only` returns one row per combination of every dimension. For a single total, write `sum(PnL) only`.
- `only` turns off automatic projection: `sum(PnL), Company only` gives exactly one column per listed item.
- Filter on an aggregate with `having`, not `where`: `having sum(PnL) < 0`.

## Combining value types

Two value types with different dimensions are joined on the dimensions they share. **Aggregate each one to the dimensions you want first**, in a `var`, then combine the variables. If you skip this, the smaller value type is repeated on every row of the larger one. Costs per consultant get subtracted once per customer, for example.

```kex
var Revenue = sum(PnL), Company where Account.Reportrow == "PL010" && YearMonth.Year == "2025" from plan 25
var Personnel = sum(PnL), Company where Account.Reportrow == "PL080" && YearMonth.Year == "2025" from plan 25
Company, Share = -sum(Personnel) / sum(Revenue)
```

An inner join drops rows that exist on only one side. Use `include optional X` (left join) or `include all X` (full join) when a missing side must still count.

## Windows

Aggregate to the period first in a `var`, then apply the window:

```kex
use plan 25
var R = sum(ConsReported), YearMonth where YearMonth.Year == "2025" && ReportRow == "PL010"
YearMonth, Revenue = R, Cumulative = sum(R) over all previous rows ordered by (YearMonth), Change = R - lag(R, 1) ordered by (YearMonth)
```

- `over all previous rows ordered by (YearMonth)` gives a running total. `over 2 previous rows` gives three months, because the bound includes the current row.
- `lag(R, 12) ordered by (YearMonth)` gives the same month last year, but only if the variable also holds last year's months.
- `partitioned by (Company)` restarts the window per company.

## Checked examples

All examples are from the base template, where Reporting & KPIs is plan 25 and the Financial Plan is plan 30. They are checked against a copy of the Acme demo tenant.

**Total for one year.**

```kex
sum(PnL) only where YearMonth.Year == "2025" && Account.Reportrow == "PL010" from plan 25
```

**Per company, with names, largest first.**

```kex
Revenue = sum(PnL), Company, Company.$name
where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
order by Revenue desc
from plan 25
```

**Top five customers.**

```kex
Revenue = sum(Actuals), Customer, Customer.$name
where Company == "2000" && YearMonth.Year == "2025" && Account.Reportrow == "PL010"
order by sum(Actuals) desc
from plan 25
limit 5
```

**Months with a loss (`having`).**

```kex
Result = sum(PnL), YearMonth
where YearMonth.Year == "2025" && Company == "2000"
having sum(PnL) < 0
from plan 25
```

**A ratio of two parts of the same value type.**

```kex
Company, Company.$name,
PersonnelShare = -sum(if(Account.Reportrow == "PL080", PnL, 0)) / sum(if(Account.Reportrow == "PL010", PnL, 0))
where YearMonth.Year == "2025"
from plan 25
```

**Year over year.**

```kex
use plan 25
var R = sum(ConsReported), YearMonth where YearMonth >= "202501" && YearMonth <= "202608" && ReportRow == "PL010"
YearMonth, Revenue = R, PriorYear = lag(R, 12) ordered by (YearMonth), YoY = R / lag(R, 12) ordered by (YearMonth) - 1
```

**Two plans in one script.**

```kex
var Cost = sum(PnL), Company where YearMonth >= "202601" && YearMonth <= "202606" && Account.Reportrow == "PL080" from plan 25
var Fte = sum(PersonnelEmploymentLevelMonthly) / 6, Company where YearMonth >= "202601" && YearMonth <= "202606" from plan 30
Company, Cost = sum(Cost), AvgFTE = sum(Fte), CostPerFTEMonth = sum(Cost) / sum(Fte) / 6
main Cost include Fte
```

**Count members with a condition.**

```kex
use plan 30
var H = sum(PersonnelEmploymentLevelMonthly), Company, Employee where YearMonth == "202606"
Headcount = count(H), FTE = sum(H), Company, Company.$name where H > 0
```

**Look up one member and a value keyed by it.**

```kex
use plan 30
Employee, Employee.$name, Employee.Company, PersonnelEmploymentEndDate
main Employee
include optional PersonnelEmploymentEndDate on Employee
where Employee == "2007"
```

**Actual against a budget stored as a reference.**

```kex
use plan 25
scalar Actual = sum(PnLMarket) only where YearMonth >= "202601" && YearMonth <= "202608" && ReportRow == "PL010"
scalar Budget = sum(PnLMarket) only where YearMonth >= "202601" && YearMonth <= "202608" && ReportRow == "PL010" from reference "Budget 2026"
evaluate Actual = Actual, Budget = Budget, Diff = Actual - Budget, DiffPct = Actual / Budget - 1
```

## When a query fails

| Error | Usual cause |
|---|---|
| *Syntax error at 'where'* or *at 'order'* | `from plan` is placed before `where` or `order by`. Move it to the end |
| *Syntax error at '='* | `=` instead of `==` |
| *Syntax error at '=='* later in the line | `AND`/`OR` instead of `&&`/`\|\|` |
| *Unexpected input at 'from plan'* | Text in single quotes (`'2000'`), which Kex reads as a date. Use `"2000"` |
| *Unknown identifier: 'SELECT'* | SQL syntax. Start with the select list itself |
| *Unknown identifier: 'X'* | X is in another plan (add `from plan`) or the id is misspelled (check `list_value_types`) |
| *Unresolved dimension 'D'. The dimension is not part of the main scope* | D is not a dimension of the value type you aggregate. Pick a value type that has D, or join one in |
| *Variable 'V' cannot be used as one or more dimensions are…* | The variables have different dimensions. Aggregate both to the same dimensions |
| An empty result | The code does not exist (check the dimension's members) or the period has no data. Say so rather than guessing |

After two failures with the same error, read [Kex](kex.html) or [Agent pitfalls](agent-pitfalls.html) before trying again.

## Related

- [Kex](kex.html) — the full language.
- [Agent pitfalls](agent-pitfalls.html) — mistakes in Kex, writes, apps and calculations.
