Docs

Logic

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

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

Per month.

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

Per company, with names.

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.

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.

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.

average(PersonnelSalary) only from plan 30

Filters#

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

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

A list of members.

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

A name pattern. * matches any text.

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

The last three months, counted from today.

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

Dates. Single quotes are date constants.

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.

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

Top-N and ranking#

The three largest customers.

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.

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

A rank column.

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.

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

Months with a loss.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

All members of a dimension.

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.

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

Names instead of codes in the result.

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.

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

Actual against budget.

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.

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.

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"