Docs

Logic

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

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

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:

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.

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

Per company, with names, largest first.

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

Top five customers.

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

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.

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.

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.

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.

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.

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.

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 or Agent pitfalls before trying again.

  • Kex — the full language.
  • Agent pitfalls — mistakes in Kex, writes, apps and calculations.