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