# Kex examples

> Checked Kex read queries ordered by task — totals, filters, top-N, periods, windows, ratios, several value types, plans and references — plus forms that look right but fail.

Every query on this page runs against the base template. Reporting & KPIs is plan 25, the Financial Plan is plan 30 and Masterdata is plan 29. Plan ids differ between tenants, so take them from `list_plans`. Start with the [Kex quick card](kex-card.html) for the rules; this page is for finding a pattern to copy.

## Totals and grouping

**One total.** `only` stops Kex from returning a row per dimension combination.

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

**Per month.**

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

**Per company, with names.**

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

**Per report row, with the row names.** A property of a property is reached with another dot.

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

**Per quarter.** Integer division needs `floor`, or the label becomes `Q1.333`.

```kex
Quarter = left(YearMonth, 4) & "-Q" & string(floor((int(right(YearMonth, 2)) - 1) / 3) + 1), sum(PnL) only
where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
from plan 25
```

**Average.**

```kex
average(PersonnelSalary) only from plan 30
```

## Filters

**A period range.** Period codes compare as text, so `"202501"` to `"202512"` works.

```kex
sum(PnL) only where YearMonth >= "202501" && YearMonth <= "202512" && Account.Reportrow == "PL010" from plan 25
```

**A list of members.**

```kex
sum(PnL), Company only where Company in {"2000", "3000"} && YearMonth.Year == "2025" from plan 25
```

**A name pattern.** `*` matches any text.

```kex
sum(PnL), Company only where YearMonth.Year == "2025" && Account.Reportrow == "PL010" && Company.$name =~ "*Norge*" from plan 25
```

**The last three months, counted from today.**

```kex
sum(PnL) only where YearMonth >= yyyymm(today() - months(3)) from plan 25
```

**Dates.** Single quotes are date constants.

```kex
use plan 30
Employee, Employee.$name, PersonnelEmploymentStartDate where PersonnelEmploymentStartDate >= '2026-01-01'
```

**Leave out the "not specified" member.** Most dimensions have a member `"0"` that collects unassigned values.

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

## Top-N and ranking

**The three largest customers.**

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

**The three highest salaries in one company.**

```kex
use plan 30
sum(PersonnelSalary), Employee, Employee.$name only where Employee.Company == "2000" order by sum(PersonnelSalary) desc limit 3
```

**A rank column.**

```kex
use plan 25
var R = sum(PnL), YearMonth where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
YearMonth, R, Rank = count(R) over all previous rows ordered by (R desc)
```

## Conditions on aggregates

**`having` with an alias.** An alias with a space needs brackets.

```kex
[Net sales] = sum(PnL), Company only
where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
having [Net sales] > 20000000
from plan 25
```

**Months with a loss.**

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

## Counting

**Members with a value.** Aggregate to the members first, then count the rows. `distinctcount(Customer), Company` fails, because the count has no value type to group by.

```kex
use plan 25
var C = sum(Actuals), Company, Customer where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
Customers = count(C), Company where C != 0
```

**Employees with a level above zero in a month.**

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

## Windows

Always aggregate to the period in a `var` first. A window on the raw value type runs once per row of every dimension and gives wrong figures.

**Running total.**

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

**Running total per company.**

```kex
use plan 25
var R = sum(PnL), Company, YearMonth where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
Company, YearMonth, R, Running = sum(R) over all previous rows ordered by (YearMonth) partitioned by (Company)
```

**Rolling three-month average.** The bound includes the current row, so `2 previous rows` is three months.

```kex
use plan 25
var R = sum(PnL), YearMonth where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
YearMonth, R, Avg3m = average(R) over 2 previous rows ordered by (YearMonth)
```

**Change from the previous month.**

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

**Year over year.** The variable must also hold last year's months, or `lag(R, 12)` is empty.

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

**Share of the total.**

```kex
use plan 25
var R = sum(PnL), YearMonth where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
YearMonth, Share = R / sum(R) over all rows
```

**Total per company on every row.**

```kex
use plan 25
var R = sum(PnL), Company, YearMonth where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
Company, YearMonth, R, CompanyTotal = sum(R) partitioned by (Company)
```

## Ratios and several value types

**Two parts of the same value type.** Use `if` inside the sums.

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

**Two aggregated variables combined.**

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

**Two plans in one script.** Each `var` names its own plan. `main` and `include` decide how the variables are joined.

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

**A constant.**

```kex
scalar TaxRate = 0.206
sum(PnL) * TaxRate only where YearMonth.Year == "2025" from plan 25
```

**Several result sets in one call.** Each `evaluate` gives a named result set, and the last statement is `main`.

```kex
use plan 25
var Revenue = sum(PnL), Company where Account.Reportrow == "PL010" && YearMonth.Year == "2025"
var Cost = sum(PnL), Company where Account.Reportrow == "PL080" && YearMonth.Year == "2025"
evaluate Revenue
evaluate Cost
```

## Members and properties

**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"
```

**All members of a dimension.**

```kex
KPI, KPI.$name from plan 25
```

**All columns of a few rows**, to see what a value type holds. `X.**` also shows internal columns such as `$edittime`.

```kex
PnL.* where Company == "2000" from plan 25 limit 3
```

**Names instead of codes in the result.**

```kex
sum(PnL), Company, Company.$name only
where YearMonth.Year == "2025" && Account.Reportrow == "PL010"
from plan 25
apply dim formatting
```

## References and history

**A stored budget.** A reference is read with `from reference` and the plan set by `use plan`. List the references with the `list_references` tool. They are not a dimension.

```kex
use plan 25
sum(PnLMarket), ReportRow only
where YearMonth.Year == "2026" && ReportRow in {"PL010", "PL080"}
from reference "Budget 2026"
```

**Actual against budget.**

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

**The data as it was a week ago.** `from history` reads the history tables, so it returns nothing in a tenant whose history has been cleared.

```text
sum(PnL) only where YearMonth.Year == "2025" from plan 25 from history today() - days(7)
```

## Checks

**Fail if a row would be counted twice.** Output options come last.

```kex
sum(PnL), YearMonth only where YearMonth.Year == "2025" from plan 25 check duplication
```

## Forms that look right but fail

| Form | What happens | Use instead |
|---|---|---|
| `sum(X) over all previous, YearMonth` | Runs, but the window runs per raw row without order, so the figures are wrong | Aggregate in a `var`, then `sum(R) over all previous rows ordered by (YearMonth)` |
| `lag(sum(X), 12)` | Runtime error: *Column … is invalid in the select list* | `var R = sum(X), YearMonth …` then `lag(R, 12) ordered by (YearMonth)` |
| `sum(R) over all rows ordered by (…) partitioned by (…)` | Syntax error | `sum(R) partitioned by (Company)` |
| `check duplication` before `where` | Syntax error | Put it last |
| `scalar var X = 1` | Runs and does nothing | `scalar X = 1` |
| `distinctcount(Customer), Company` | *Unresolved dimension 'Company'* | Count the rows of an aggregated `var` |
| `from reference "…"` without `use plan` | *Unknown identifier* | Start the script with `use plan` |
| `sum(PnL), Company from plan 25 where …` | *Syntax error at 'where'* | `from plan` after `where` and `order by` |
| `Company == '2000'` | *Unexpected input*: single quotes are dates | `Company == "2000"` |
