Kex language reference
Documentation of the Konstrukt expression language.
This is a general documentation for the complete Kex language. For information about Kex in dataset expressions, refer to the Kex Lite documentation page.
Overview
High-level structure of a query:
select expressions/columns
// Include options:
[main VALUETYPE|DIMENSION]
[include [optional|all] VALUETYPE|DIMENSION [on DIMENSION1, DIMENSION2, ...]|[on CONDITION]]
[include cross [all|optional] VALUETYPE|DIMENSION]
[default IDENTIFIER to CONSTANT_EXPRESSION]
[expand DIMENSION.PROPERTY]
// Filtering:
[where CONDITION]
// Grouping, ordering and postaggregative filtering:
[group by EXPRESSION1, EXPRESSION2, ...]
[having CONDITION]
[order by EXPRESSION1, EXPRESSION2, ...]
// Query options:
[from plan PLAN_ID|"PLAN_NAME"]
[from reference REFERENCE_ID|"REFERENCE_NAME"]
[from history 'datetime|date|time'|expression [values only]]
[limit n]
[check duplication|includes|dim values]
[apply dim formatting [FORMAT_OVERRIDE]|dim filtering]
Note: Keywords are, unlike in many other query languages, case-sensitive and must be lower case.
Example query:
// Compute gross margin and include revenue in the output
sum(Revenue),
[Gross Margin %] = sum(GrossIncome) / sum(Revenue)
// Only for specific Periods
where Period >= 202401
// And only where the gross margin is positive
having [Gross Margin %] > 0
// Limit to 100 rows
limit 100
Implicit information resolution
The Kex engine attempts to resolve any implicit information so that queries don't have to specify information explicitly for common scenarios.
Dimensions are for instance projected automatically, so the above query is equivalent to the following:
sum(Revenue),
[Gross Margin %] = sum(GrossIncome) / sum(Revenue)
Period,
Region
where Period >= 202401
having [Gross Margin %] > 0
limit 100
(assuming Revenue and GrossIncome share the two dimensions Period and Region)
Additionally, inclusions, grouping and ordering is also added implicitly. That is, the above query is actually equivalent to the following (after all implicit information has been resolved):
[sum(Revenue)] = sum(Revenue),
[Gross Margin %] = sum(GrossIncome) / sum(Revenue)
[Period] = Period,
[Region] Region
main Revenue
include Period
include Region
include GrossIncome on Period, Region
where Period >= 202401
group by Period, Region
having sum(GrossIncome) / sum(Revenue) > 0
order by Period, Region
limit 100
from plan 17
(assuming plan number 17 is the default plan)
(Note also that it's possible to reference select aliases in the having and order by clauses and
that the aliases overshadow native identifiers.)
Note: The include keyword is just a hint to tell the engine
which sources it should include in the query. They may be reordered and an include
could be rewritten to main if there is no explicit main table in the query.
The best way to force inclusion order is to explicitly specify which dimensions value tables should
be joined on using the on keyword.
Note: Select dimensions are automatically added when querying a value type
(e.g. sum(Revenue) or simply Revenue).
This behavior can be disabled by adding the only keyword, e.g. sum(Revenue) only
or Revenue only.
How the main table and dimensions are determined
The heuristics that determine the main table roughly prioritze as follows:
-
Value tables that are not tagged as being
optional,allorcrossincludes. -
Dimensions (that are not included using the
allorcrosskeywords). - Pick the table that results in the highest number of main dimensions common with other tables in the query (as long as this also satisfies all explicit joins in the query).
- Use the table that appears first if multiple tables end up with the same ranking.
Afterwards the main dimensions are determined:
- Use the dimension from the main table
-
Add any
cross-included dimensions. Note that this means thatcrossincludes are always performed beforeallincludes and that it is not possible to join on non-main dimensions in the same query.
Note: The main dimensions can be referenced without being qualified by their originating valuetype in a query.
That is, it's possible to type Region instead of Sales.Region if the unqualified version is unambiguous.
Identifiers
Valuetypes
Valuetypes are referenced by their name directly and can be used in operations as is:
sum(Revenue) * average(GrossMargin)
There are also some additional internal columns that can be queried, e.g. $edittime and $editby.
Dimensions
Dimensions are, just like valuetypes, referenced by name directly.
When used in the context of a value table it's also possible to qualify dimensions by their owning table.
This is especially useful when the same dimension has different meaning in different value tables (which, for instance,
could happen when using all or cross includes, or if tables are not based on the same
set of dimensions).
Dimensions also contain internal columns that can be queried, e.g.
$name, $edittime and $editby.
Properties
Properties are referenced through their owning dimension: Region.Country, Period.Closed.
Identifier expansions: x.* x.**
It's possible to project everything owned by a valuetype, variable or dimension using .*.
.** can also be used to discover internal columns (e.g. edit times and dimension/property names).
// Project all dimensions in 'Revenue' and all properties in 'Region'
Revenue.*, Region.*
// Discover all properties (including internal)
Region.**
Additionally, it's possible to expand in multiple steps by chaining operators and to filter to only entries that match specific names:
// Project all dimensions in 'Revenue' all properties of those dimensions
Revenue.*.*
// Include names for all dimensions as well
Region.*.**$name*
Note: The double-asterisk expansion method is mainly intended to be used for troubleshooting and column discovery. The order and contents returned by these expansion methods may change.
Note: It's not possible to reference a dimension through an owning valuetype using the expansion syntax.
I.e. the following is valid: Revenue.*region*
While the following is not: Revenue.Region
The columns of a property can be expanded however, e.g.: Region.Country.**
Queries and scripting
Scripts are composed of one or more query statements and enable storing intermediate results in variables.
Example script:
// Define a gross margin table-valued variable.
// The variable will be comprised of two result values and the dimension intersection
// of the referenced valuetypes.
// Note that the combination of dimension values of each row in a variable needs to be unique.
// This is normally accomplished by explicitly aggregating a variable.
var GrossMargin =
sum(Revenue),
[Gross Margin %] = sum(GrossIncome) / sum(Revenue)
// Evaluation query:
// - Include the cartesian product (cross) of the Period dimension
// - Project the ShowInPlan property of the Period dimension as well
// - Filter on the Locked property
// - Aggregation/grouping isn't required for the main query (but beware that the result
// may contain duplicate rows depending on the query and the data of the included tables)
GrossMargin,
Period,
Period.ShowInPlan
include cross Period
default Period.Locked to "0"
where Period.Locked != "1"
Note: Whitespace characters do not have any significance, which means that e.g. a script like
var x = 1 var y = 2 x y is valid. This results in two evaluations and is equivalent to writing:
var x = 1
var y = 2
// Evaluate the two variables (note also that the "evaluate" keyword can be omitted)
evaluate x
evaluate y
Changing plan
The use plan PLAN_ID|"PLAN_NAME" keyword can be used to specify which plan to use by default
(i.e. which plan that should be used in queries where the from plan option isn't specified).
Note: The use plan keyword is not supported in calculations.
Variables
Script variables use common Konstrukt concepts and are based on the same dimensional model. There are two main
variable classes: var and dim (valuetype/table-based variables and dimension variables).
Note: It's recommended to capitalize the initial letter when naming variables as this guarantees that the name will not collide with any future keywords added to the Kex language. (Or to use single letter variable names if the context of the variable makes it clear what it does, as single-letter keywords are unlikely to be added to the language.)
Valuetype variables: var
Valuetype variables make it possible to derive custom valuetypes that can be used in the context of a script:
// Define a variable 'x' that contains the data from the 'Revenue' valuetype
var x = Revenue
// Define variable 'y' that contains the values of 'x' and two additional computed columns
var y =
x,
x + 10,
[x plus 20] = x + 20
// Output the sum of all three columns
y + y.expr_2 + y.[x plus 20]
Note: All variables in the above example will use the same dimensions as the Revenue valuetype.
This is because all dimensions are projected automatically when a query does not explicitly select any dimensions.
The Kex engine must be able to determine the dimensions of valuetype variables as these are used to uniquely identify rows.
The most straightforward way to adhere to this is to always aggregate variable queries and to group by the dimensions that
should be included. For example:
// Define the x variable
var x = average(Revenue), Period
// Main query
x
(assuming the Revenue valuetype has e.g. dimensions Period and Region but
only the Period should be included in the projection/grouping)
Value columns
Valuetype variables must contain at least one value (non-dimension) column. Due to this the code(s) of selected dimensions will be used as values unless anything else is specified:
// This will contain the Period code as the value and dimension
var x = Period
// This will use 1 as the value (and Period as the dimension)
var x = Period, 1
// This will contain value columns [Period.$value] and [Region.$value] in addition to the dimensions
// (note that the dimension codes can be accessed using x.Period and x.Region as well however)
var x = Period, Region cross Region
Using more than one value column
Additionally, valuetype variables can contain more than one value column. The first column can always be referenced using just the variable name but all other columns must be referenced by their alias.
Aliases will be generated for all columns that do not have explicit aliases specified. For custom expressions
then the generated aliases just follow the form expr_n (e.g. expr_1, expr_2, etc).
For simple column references then source names are propagated however, using the following logic:
Dimensions
These use the dimension ID:s as aliases.
For example: Period
Dimension properties
If only one dimension is selected then aliases will be the property codes prefixed with a period.
For example: var x = Period, Period.Active is transformed into var x = Period, [.Active] = Period.Active
If multiple sources are referenced then the property codes are qualified by source.
For example: var x = Period, Period.Active, Country, Country.Region cross Country
is transformed into var x = Period, [Period.Active] = Period.Active, Country, [Country.Region] = Country.Region cross Country
Value columns (from valuetypes or table-valued variables)
If only selecting from one table then aliases will use the same name as the source columns.
For example, assuming tables x and y contain columns a and b then:
var res = x.a, x.b is transformed into var res = a = x.a, b = x.b
When referencing multiple sources then the aliases are qualified by source however, for example:
var res = x.a, y.b is transformed into var res = [x.a] = x.a, [y.b] = y.b
Adding custom dimensions: dimadd
It's possible to add derived dimensions to variables, even if the referenced valuetypes do not natively contain them. This makes it possible to join a valuetype with another valuetype even if they do not have any dimensions in common.
This is done using the dimadd keyword:
// Create a variable based on 'Revenue' with an added dimension 'Year'
var RevenueWithYear = Revenue, Region, Period, dimadd Year = left(Period, 4)
// Create a variable that computes the total sales per year
var SalesPerYear = sum(Revenue), Year
// Main query: Compare the sales with a yearly target
SalesPerYear - YearlyTarget
(assuming the YearlyTarget valuetype only contains the Year dimension)
Note: The dimadd keyword can only be used to add existing dimensions to
variables that do not currently contain them. To create custom dimensions that can be used within a
script see the section about dim variables.
Note: Use the dimset keyword to specify custom expressions
for dimensions that already exist in a variable.
Note: Custom dimensions that have been added using the dimadd keyword can
be referenced the same way as any other dimension in the query:
// Add the 'Year' dimension (and remove 'Period' by excluding it from the selection)
var RevenuePost2023 = sum(Revenue), dimadd Year = left(Period, 4)
// Filter by year
where Year > 2023
// Main query
RevenuePost2023
Setting output dimensions: dimset
The dimset keyword is similar to dimadd, except that it allows overriding
dimensions that are already available in the variable.
// Create a variable that computes the total sales per year
var RevenueWithYear = sum(Revenue), Region, dimset Year = left(Period, 4)
// Main query: Compare the sales with a yearly target
SalesPerYear - YearlyTarget
Note: Unlike dimadd expressions, the expressions defined by the dimset
keyword will only be inserted in the projection and grouping of the variable. This means that
e.g. filtering will be performed on the original dimension:
// Replace with the next year
var RevenueNextYear = sum(RevenuePerYear), dimset Year = convert(Year, int) + 1
// Filter by year. This filtering will be performed on the original year,
// rather than the year computed by the 'dimset' expression.
where Year >= 2023
// Main query
RevenueNextYear
Note: dimset expressions can reference (and override) dimensions added using the
dimadd keyword.
Scalar variables: scalar var
A scalar variable acts as a valuetype without any dimensions. These are treated as a single value (and are effectively cross-joined wherever they're used):
// Compute the total sales
scalar var SalesTotal = sum(Revenue)
// Determine how much of the total revenue was earned for each period
var StatsByPeriod = sum(Revenue) / SalesTotal * 100 & "%", Period
// Main query
StatsByPeriod
Although scalar variables do not contain any dimensions it's still possible to
use dimensions in the query that define them. If a variable query returns multiple rows then only the first row will be used.
The SalesTotal variable in the above example will contain the total sum over all rows however as dimensions are not
automatically projected for scalar variables.
Note: A scalar variable will contain exactly one row even if the query did not return any data
(in which case the value(s) of the variable will be null).
Note: If additional value columns are used when definining a scalar, they'll be attached to the variable and can be accessed using their qualifed name:
// Compute the total sales and the last time the valuetype was updated
scalar var SalesTotal = sum(Revenue), Changed=max(Revenue.$edittime)
// Main query:
// Calculate how much of the total revenue was earned for each period and show the edit time
sum(Revenue) / SalesTotal * 100 & "%", SalesTotal.Changed, Period
(which essentially means that Kex scalars can be used to represent tuples as well)
Note: The var keyword can be omitted when declaring scalar variables: scalar x = <query>.
Variable name overshadowing
Valuetypes (and valuetype variables) can be overshadowed by using the same name as an existing table:
// Redefine the 'Revenue' valuetype with an additional dimension
var Revenue = Revenue, Period, Region, dimadd Year=left(Period, 4)
// Using 'Revenue' now will include the 'Year' dimension
var ProjectedRevenue = sum(Revenue * 10), Year
// Main query
ProjectedRevenue
Note: This is only supported for valuetype names (and not dimensions).
Dimension variables: dim var
It's possible to define custom dimensions that can be queried directly or added to valuetypes using the
dimadd/dimset keywords.
Since the codes within a dimension must be unique dimension variables are based on the unique values in the first
column of their query:
// Create a custom 'Year' dimension based on the first 4 characters in 'Period'
dim Year = left(Period, 4)
// Add the 'Year' dimension to 'Revenue'
var Revenue = Revenue.*, dimadd Year = left(Period, 4)
// Main query
Revenue
Note that it's only possible to create dimensions that do not already exist in the current plan.
Data from other plans (queried using the from plan option) cannot be referenced if the current context is missing
one or more dimensions.
If, for example, the Sales valuetype in plan 10 contains a dimension Region that does not exist in the current plan
it needs to be added using the dim keyword.
// Load sales data from plan 10
var OtherSales = sum(Sales), Region, Period from plan 10
// This fails if the current plan is missing e.g. the 'Region' dimension
evaluate OtherSales
// Create the dimension
dim Region = "WEST"
// Now the 'OtherSales' variable can be used
// Show "WEST" sales
evaluate OtherSales
// Overwrite the dimension and show "EAST" sales instead
dim Region = "EAST"
evaluate OtherSales
Note: In the above example the OtherSales variable contains all values from the original plan
and are only filtered out when the Region dimension is referenced. This means that a query like count() main OtherSales group by null
will disregard the custom Region dimension while e.g. count() main OtherSales include Region group by null will show the
count of all "current" rows.
It is possible to validate that all dimension values exist in the current plan using the check dim values keyword:
var OtherSales = sum(Sales), Region, Period from plan 10 check dim values
It is also possible to force evaluation by simply reassigning the variable:
var OtherSales = sum(Sales), Region, Period from plan 10
var OtherSales = OtherSales
Or by applying the dim filtering query option:
var OtherSales = sum(Sales), Region, Period from plan 10 apply dim filtering
This is rarely necessary however as the automatic lazy filtering is often sufficient and preferred from a performance perspective.
Note: It's also possible to "import" a dimension from a different plan, e.g. dim Region = Region from plan 10.
Note also that when using the from plan N option the query uses the dimensions that are available in plan N and custom
dimensions will be ignored. This differs from how custom valuetypes (defined using only the var keyword) are handled as these
are overridden in all plans.
// Add the missing 'Region' dimension
dim Region = "WEST"
// Override the 'Sales' valuetype
var Sales = max(Sales), Period, dimadd Region = Region
// Query sales from another plan. Note that the dimensions use the properties from plan 10
// in this case and that expressions/conditions also use the 'Region' values of the other plan.
var OtherSales = max(Sales) where Region.IsActive == 1 from plan 10
// Evaluate the 'OtherSales' variable in the current plan.
// This effectively applies the Region == "WEST" filter.
OtherSales
// Query sales from this plan. Note that the 'Region' dimension does not contain any properties in this case.
Sales.*.**
Additionally, a dimension can always be included using the cross keyword. This does not require
the dimadd/dimset keywords as it creates a cartesian product of the tables:
// Create a custom 'Year' dimension based on the first 4 characters in 'Period'
dim Year = left(Period, 4)
// Compute the product of 'Year' and 'Revenue' (omitting the Period)
var RevenueByRegionAndYear = sum(Revenue), Region, Year
include cross Year
// Main query
RevenueByRegionAndYear
Note: The var keyword can be omitted when declaring dimension variables: dim x = <query>.
Re-assigning variables: set
A new value can be assigned to an existing variable using the set keyword. This works for all types of variables
as long as the new value has the same shape as the original variable.
// Calculate revenue per year
var RevenuePerYear = sum(Revenue), dimadd Year=left(Period, 4)
// Increase the revenue a bit
set RevenuePerYear = RevenuePerYear + 100
set RevenuePerYear *= 1.1
// Main query
RevenuePerYear
Assign-update (union) operators: +=, -=, etc
Most operators can be used to update variables with single value columns, these include
+=, -=, *=, &= and ??=.
When used on a table-valued variable the assign-update operators effectively compute the union of the left-hand side and the right-hand side. I.e. to combine two variables:
var TotalRevenue = sum(Revenue), Period
// Add other revenues
set TotalRevenue += OtherRevenue
Where the last statement is equivalent to:
set TotalRevenue =
sum(if (TotalRevenue == null, OtherRevenue, TotalRevenue + (OtherRevenue ?? 0)),
Period = autodim(Period)
main TotalRevenue
include all OtherRevenue on Period
Note: When assign-updating table-based variables the sources referenced in the right-hand side query must be based on the same dimensions as the target variable, or they need to be explicitly included. This is done to prevent accidental data duplication.
Note: When assign-updating dimension variables the right-hand side query must be scalar (i.e. may only reference constants and scalar variables).
Named literals (@variables)
Compile-time constants can be defined using @identifiers.
These are non-null literals that can be used as regular constants and in places where
data-dependent variables are not supported, for instance with the from plan and from reference
keywords.
Example:
@otherPlanId = "A plan name"
@multiplier = 10.0
var OtherSales =
sum(Sales) * @multiplier
from plan @planId
// ...
Note: There are some contexts in which @variables are not supported, for example
in row limits, windowing bounds or as dim formatting specifications.
Subqueries
Subqueries can be used in some specific contexts, for example the following language constructs:
any (<subquery>)
in query(<subquery>)
makecode() within (<subquery>)
Note: Unlike many other query languages, Kex subqueries do not have access to anything defined in the outer query (i.e. they are executed in their own isolated scope).
Static and generated data
Value tables
Table-based variables can be initialized using tuple syntax (with a header for the column names):
// Initialize a variable with some sales data
var CountrySalesPerPeriod = (Period, Country, sales) {
(202301, "US", 100.0),
(202302, "US", 102.0),
(202303, "US", 101.50),
(202303, "SV", 3.009)
}
// Output the data
CountrySalesPerPeriod
// Initialize a variable with some financial data
var BUFinancials = (BusinessUnit, sales, expenses) {
("Division 1", 100.0, 33),
("Division 2", 23, 24.0),
("Division 3", 990, 50)
}
// Output divisions by profitability
Sales = BUFinancials.sales,
Expenses = BUFinancials.expenses,
Income = BUFinancials.sales - BUFinancials.expenses
order by 3 desc
Note: The above example assumes that the dimensions Period, Country and BusinessUnit already exist.
Note: Dimension values must be non-null constants and will be converted to strings if required.
Note: If the values for a column differ in typing the values are coerced to a type that can be used to
represent the values of all rows. This means that e.g. decimal mixed with int results in a decimal
while otherwise non-compatible types will often be converted to string when mixed.
Dimension tables
Dimension variables can be initialized from static lists:
dim Periods = {202301, 202302, 202303}
dim PreviousAndCurrentYear = {year() - 1, year()}
Note: The expressions used in these lists will be converted to dimension value codes (i.e. string values) and duplicate values will be filtered out automatically.
Generating data ranges
Dimension values can also be created from ranges using the range(start: T, stop: T, step?: number | interval_function): T[] generator function.
Where:
- The start and stop values must be constants of the same type.
- The (optional) step parameter may be an integer value, a decimal value or an interval function call (e.g. days(7)) depending on the type of the range.
// "202301", "202302", "202303"
dim Periods = range(202301, 202303)
// "2023-01-01", "2023-01-08", "2023-01-15"
dim Days = range('2023-01-01', '2023-01-15', 7)
// The first of all months of 2023
dim Months = range('2023-01-01', '2023-12-01', months(1))
// "11:00:00", "12:00:00", "13:00:00"
dim Hours = range('11:00', '13:00')
// "11:00:00", "11:30:00", "12:00:00", "12:30:00", "13:00:00"
dim HalfHours = range('11:00', '13:00', minutes(30))
// "10.0", "10.25", "10.50", "10.75", "11.00"
dim Numbers = range(10.0, 11.0, .25)
// "a", "b", "c", "d", "e"
dim Suffixes = range("a", "e")
// "A", "B", "C"
dim UpperSuffixes = range("A", "C")
Note: Ranges must be strictly increasing.
Note: String-valued ranges are only supported for single characters/digits and the casing follows that of the start character.
Note: The start and stop values are both inclusive. Note however that the stop value will not be included
in the range if it's overshot by the step.
Evaluating queries and returning multiple results: evaluate
In certain contexts a script can return multiple results by using the evaluate keyword.
This could be useful to inspect intermediary results in a script.
// Compute revenue by region
var SalesByRegion = sum(Revenue), Region
// Output the result
evaluate SalesByRegion
// Output revenue multiplied by 10 and name the result
evaluate [10x Revenue] = SalesByRegion * 10
// Inspect the gross margin table (and name the evaluation 'gm')
evaluate gm = GrossMargin
// Main query
SalesByRegion * average(GrossMargin)
Note: The evaluate keyword is only required if the result should be assigned a specific name.
var x = sum(Revenue)
// Evaluate x + 1
x + 1
// Evaluate x + 2
x + 2
// Evaluate x + 3 with a specific name
evaluate [A result] = x + 3
Include options
Custom conditions
It's possible to specify a custom join condition when including a source using the on keyword.
Consider two example tables:
Sales with a Country dimension that has a Region property.
| Value | Country | Country.Region |
|---|---|---|
| 500.0 | AUS | APAC |
| 175.0 | GER | EMEA |
| 125.0 | SWE | EMEA |
RegionalCost with a Region dimension.
| Value | Region |
|---|---|
| 360.0 | APAC |
| 262.50 | EMEA |
Then the margin per region can be calculated using a custom include condition that
matches the Region dimension with the Country.Region property:
[Margin %] = (1 - average(RegionalCost) / sum(Sales)) * 100,
Region = RegionalCost.Region
include RegionalCost on RegionalCost.Region == Sales.Country.Region
Result:
| Margin % | Region |
|---|---|
| 28.0 | APAC |
| 12.50 | EMEA |
Note: Only dimension codes (i.e. no properties or e.g. the $name column) can be referenced
in the custom join condition for the table being included.
Default values
default IDENTIFIER to CONSTANT_EXPRESSION
Use this option to default any nullable identifier expressions to specific values. Can e.g. be used to specify defaults for dimension properties
or to handle nullability when including data using the optional or all keywords.
// Default the Country property code to "EN"
sum(Sales), Region.Country
default Region.Country to "EN"
// Specify separate defaults for the different columns in the property
sum(Sales), Region.Country.**
default Region.Country.$code to "EN"
default Region.Country.$name to "England"
default Region.Country.$formatted to "EN - England"
Note that specifying defaults works for any identifier expression, so it can be used to use specific defaults for optionally included valuetypes.
sum(Expenses) / sum(Sales)
include optional Expenses
default Expenses to 0
Note: It is syntactically valid to use expressions of non-matching types with the default keyword but values that
cannot be converted will cause the default to not be applied.
For instance, an expression like default Expenses to "not a number" would not have any effect.
Expanding multi-valued properties
By default multi-valued properties are string-aggregated and separated by ; when used in a query.
It is however possible to expand the values using the expand keyword.
For example (non-expanded):
sum(InputCost), Product.Categories
| sum(InputCost) | Product.Categories |
|---|---|
| 125.0 | Books;Gifts |
| 500.0 | Tools;Hammers |
Using the expand keyword:
sum(InputCost), Product.Categories
expand Product.Categories
| sum(InputCost) | Product.Categories |
|---|---|
| 125.0 | Books |
| 125.0 | Gifts |
| 500.0 | Tools |
| 500.0 | Hammers |
Note: Expanding the values of a multi-valued property will duplicate the values for all other columns.
Note: A multi-valued property will be expanded automatically when used as
the expression in the dimadd or dimset keyword.
That is, e.g. sum(InputCost), dimadd Category = Product.Categories is rewritten to
sum(InputCost), dimadd Category = Product.Categories expand Product.Categories
Note however that this is only done automatically for simple property references, meaning expansions
are not automatically added for expressions like upper(Product.Categories).
Note: Expanding a property only has an effect if the property is actually referenced,
i.e. the following variable will contain the count of Products without the expansion:
scalar x = count() expand Product.Categories
While the following counts the rows after expansion:
scalar x = count() expand Product.Categories where Product.Categories ?? 0 != null
Note: It's also possible to expand the internal $calcid column of
a valuetype to prevent pre-aggregation of values. This will be applied even if the column is not
referenced elsewhere.
Excluding calculated data
exclude calculated
Use this option to exclude calculated data (rows written by calculations) from every valuetype in the
query, keeping only plan data. It's shorthand for expanding each valuetype's $calcid and
filtering out the rows where it's set.
sum(Income) / sum(Sales)
exclude calculated
// is equivalent to
sum(Income) / sum(Sales)
expand Income.$calcid
expand Sales.$calcid
where Income.$calcid == null && Sales.$calcid == null
Query options, validations and checks
Data loading options: from
from plan PLAN_ID|"PLAN_NAME"
Use this option to specify a plan ID or plan name to load data from.
from reference REFERENCE_ID|"REFERENCE_NAME"
Use this option to load data from a specific reference (by ID or name).
Note also that the query is automatically rewritten to use a from history [...] values only filter
when this option is used on a point-in-time reference.
from history 'datetime|date|time'|expression [values only]
Use this option to load historical point-in-time data.
Use the values only keyword to only load historical data for valuetypes and to use
current values for related dimensions. Note that historical valuetype rows that reference
removed dimension values will be filtered out when this option is used. (Note also that this filtering
is performed greedily, as opposed to how filtering for non-existing dimension values is performed when using
the dimadd or dimset keywords.)
It's also possible to combine the different types of loading options, e.g. to view Sales reference data for a specific plan as it stood at the start of yesterday:
sum(Sales)
from plan "Some plan name"
from reference "Some reference"
from history today() - days(1)
Limiting query rows: limit n
Add this option to the end of a query to limit the number of rows it returns. Must be a non-negative number.
Filtering out non-existing dimension values: apply dim filtering
It's possible to automatically filter out non-existing dimension values using the apply dim filtering query option.
The filtering is applied to all expressions whose aliases match existing dimensions.
sum(Sales), Year = left(Period, 4)
apply dim filtering
Note: The above example is basically equivalent to the following:
var x = sum(Sales), dimset Year = left(Period, 4)
// Non-existing values are filtered out lazily. This means that they
// exist in the backing table, but will be filtered out when referenced:
evaluate x
Formatting dimension values: apply dim formatting
This option provides a short-hand for selecting the internal $formatted column for dimensions and properties.
The $formatted column is a computed column that shows the formatted dimension values (depending on how the dimension or property
is configured). This is formatted as "{Code} - {Name}" by default.
For instance:
// Select Sales, including all owned dimensions and properties, then order by Region
Sales.*.*
order by Region desc
apply dim formatting
Expands to (assuming some example dimensions and properties):
Sales, Period, Period.IsOpen, Region, Region.Country
order by Region desc
apply dim formatting
Which is then rewritten to:
Sales, Period.$formatted, Period.IsOpen.$formatted, Region.$formatted, Region.Country.$formatted
order by Region.$formatted desc
Note: This option is only allowed in evaluation results (and not in variables) since it is only
intended for display purposes. Care also needs to be taken when using this on large datasets, especially when filtering
(or ordering) by the computed $formatted column as it is not individually indexed.
Note: Formatting is applied regardless of aliases, meaning that e.g. a query like
sum(Sales), [Region.$name] = Region apply dim formatting will be transformed to
sum(Sales), [Region.$name] = Region.$formatted
Note: It is also possible to provide a custom format specification and override the default/configured format,
e.g.:sum(Sales), Period apply dim formatting "{name} (Code: {code})"
Validations and checks
Data duplication: check duplication
Joining multiple valuetypes that are not based on the same dimensions could result in data being duplicated/spreaded. It's possible to raise an error when this may happen to avoid unintentionally duplicating data (or to troubleshoot data issues).
Includes: check includes
Add this check to validate that all referenced value tables in the query are explicitly
listed as from options (i.e. using the main and/or include keywords).
Note: This also effectively disables a large part of the implicit include resolution.
Dimension values: check dim values
Enable this check to validate that any dimension values created as part of a table-valued variable (var)
refer to dimension codes that actually exist.
The default behavior is for non-existing dimension values to be filtered out lazily. That is, the data table that backs a variable will contain the invalid codes until the affected dimension is referenced.
// Create a variable with an invalid Period
var SalesByPeriod =
sum(Sales),
dimset Period = 1234
// Count the rows in the table
scalar Count = count() main SalesByPeriod
// Evaluate the count: This returns 1 even if there is no '1234' Period
Count
// Evaluate the whole table: This returns an empty set as the invalid Period is filtered out lazily
SalesByPeriod
Note: This is the default behavior as greedy code evaluation has a non-negligible effect on performance. It's also possible to use the behavior to automatically filter out dimension values by intentionally generating non-existing codes.
Data writing statements: write to
write [[insert|upsert] suggestion|insert|update|replacement] [to] [TARGET ID] [reference [ID|NAME]] <- [QUERY]
Keywords:
-
suggestion: Can be used in calculations to write "suggested" values instead of calculated values. The written values can be edited by the user (and will thereafter not be updated if the calculation runs again).
Note: Since the suggested values are not actually owned by the calculation they will be overwritten if multiple calculations write to the same valuetype (and the last calculation that writes suggested data will also delete any values suggested by previous calculations, unless theupsertorinsertkeywords are used). -
insert suggestion: Similar tosuggestionbut will only insert new values for rows that do not already have any data. -
upsert suggestion: Similar tosuggestionbut will only update existing values and insert new values for rows that do not already have any data. -
insert: Can be used to only inserts new data for rows that (as identified by dimension values) do not already have any data. -
update: Updates matching rows but does not add any new data or delete any non-matching rows. -
replacement: Will replace the contents of the target table with the results from the query (in effect deleting any non-matching data). This is the default behavior in calculations unless they write reference data. Note that when executed outside of a calculation thereplacementkeyword does not delete calculated data (just as e.g. theupdatekeyword does not modify calculated data). -
reference [ID|NAME]: Can be used to write reference data.
Writing valuetype data
// Reset Sales to yesterday's non-calculated values
write replacement to Sales <-
sum(Sales)
expand Sales.$calcid
where Sales.$calcid == null
from history now() - days(1)
Note: The default behavior in non-calculation contexts is to perform
an upsert (i.e an update + insert) if a behavior keyword isn't used.
Note: When executed in the context of a calculation the write statement will only affect rows with
the corresponding calculation ID, unless the suggestion or reference keyword is used.
Note also that when multiple write to statements in the same calculation write to the same valuetype
the first statement will clear any data previously written by the calculation while all subsequent statements
perform upserts.
Writing dimension data
// Get the Period for the next month on the form YYYYMM
scalar NextPeriod = yyyymm(now() + months(1))
// Open the next Period:
write to Period <-
Open = 1
where Period == NextPeriod
// Alternatively, open the next Period and close all other Periods:
write to Period <-
Period,
Open = if (Period == NextPeriod, 1, 0)
Supported columns
Code (dimension ID)
The dimension data query is expected to select one column that matches the ID of the destination dimension (either explicitly using an alias or implicitly by selecting from a table that contains the dimension).
E.g. to create or update Period 202503:
write to Period <- Period = "202503", Open = 1
Special characters
Codes may contain characters a-z, A-Z, 0-9 and the following special characters: +-_/.:%&=!()[]<>@.
When writing dimension data any rows with an unsupported code (or null) will be filtered out.
Name
The name can be specified by aliasing a column with $name.
E.g. to update the name of Period 202503:
write update to Period <- Period = "202503", $name = "May 2025"
Properties
Properties can be specified by using the property code as an alias in the query.
If a property code is equal to the dimension ID it can be prefixed with a period (.).
For example, if the Period dimension also has a Period property:
write to Period <- Period, [.Period] = 1
Note: If a property value does not exist or if a property is not specified in the query its value will not change.
Additionally, when inserting non-existing values the configured default will be used if the property is required and null otherwise.
Supported functionality
The default behavior is to perform an upsert (i.e an update + insert)
if a behavior keyword isn't used. Dimension writes do not support the suggestion or
reference keywords.
Note: Unlike valuetype data dimension data does not store duplicate rows. This means that if multiple calculations write to the same dimension value the latest calculation will overwrite previously written data. Multiple calculations may however write to different properties without interferring with each other.
Control flow: if/else if/else for
if/else if/else
It's possible to conditionally execute statements using the if, else if and else keywords.
Syntax:
if CONDITION {
[STATEMENTS]
} [else if CONDITION {
[STATEMENTS]
}, ...] [else {
[STATEMENTS]
}]
Example:
var ProjectedSales = sum(Sales)
if year() <= 2024 {
// Project lower sales for 2024 and earlier
set ProjectedSales *= 0.95
} else if year() >= 2027 {
// Project higher sales for 2027 and later
set ProjectedSales *= 1.1
}
// Write result
write to Forecast <- ProjectedSales
Note that the if keyword can also be used as an expression (see Functions > Conditional evaluation):
// Project higher sales for 2027 and later
write to Forecast <- sum(Sales * if (year() >= 2027, 1.1, 1.0))
Note: The conditions may only reference scalar variables
(or use the any keyword when referencing non-scalar variables).
for
The for keyword can be used to iterate over tables/variables, queries or numeric ranges.
Syntax:
for [scalar] ITERATOR in QUERY {
// ...
}
for ITERATOR in range(START, STOP[, STEP]) {
// ...
}
For example to calculate the total sales in the EAST region:
var TotalEastSales = 0
for x in Sales where Region == "EAST" {
set TotalEastSales += x
}
// ...
(Note that this is equivalent to a query like sum(Sales) where Region == "EAST")
To accumulate something based on a range:
var Factorial = 1
for x in range(2, 7) {
set Factorial *= x
}
// 7! = 5040
Factorial
// Which can also be calculated as:
dim r = range(1, 7)
Factorial = max(int(product(int(r)) over all previous)) only
Note: It's only recommended to use loops when there is no join-based alternative way to perform the calculation. Due to this a script may not perform more than 1000 iterations in total.
Note: Statements that result in any kind of output are not supported inside control flow statements.
This includes query evaluations (evaluate) and data writing statements (write to).
The iterator variable and scalar rows
By default the iterator/row is treated as any other table-valued variable and will contain the same dimensions
as the query that is being iterated. This means that it will be joined when used in the
for body and that the iterable query must produce unique dimension value combinations.
To treat the iterator as a scalar the scalar keyword can be used however. This will result in
the iterator being cross-included when used in the for body and makes it possible for the iterable query
to return non-unique rows. For example:
for scalar year in Period.Year {
// Will be called for each period
}
for scalar year in Period.Year group by Period.Year {
// Will be called for each year
}
Checking for data existence: any
The any keyword can be used to globally check whether data exists for a specific condition or query.
Syntax:
any (QUERY|CONDITION [from plan ID|NAME][from reference ID|NAME])
Examples:
if any (Sales) {
// There is sales data in the current plan
}
if any (Sales > 10000 from plan 10 from reference "Some reference") {
// There is high-valued sales reference data in plan 10
}
if not any (Sales, Forecast) {
// The Sales and Forecast tables do not have any dimension values in common
}
Note: The any keyword can be used in regular queries as well (and is not restricted to top-level conditionals).
When used in a query the any subquery creates it's own scope and does not have access to anything in the outer query.
Aborting a script: fatal
The fatal keyword can be used to abort a script with an error message, e.g. if a certain pre-condition hasn't been met.
// Abort the script if there are negative sales
if any (Sales < 0) {
fatal "There are negative sales. Script cannot continue."
}
Note: The message can be any expression as long as it only uses scalar variables.
Note: The fatal keyword can only be used inside control flow statements.
Text data escaping
String-valued cells that can contain multiple values (i.e. text-based valuetypes or multi-valued dimension properties)
are escaped using the ; character. To avoid ambiguity this also means that the ; character
is replaced with \; if used in a string and \ is replaced with \\.
Note that this is not applicable for regular dimension values or multi-valued properties that have been expanded using
the expand keyword.
Expressions
Constants
- Strings:
"abc" - Numbers:
123,456.789,.009 -
Dates, times and date/times:
'2023-05-17''12:34:22.777''2023-05-17 12:00''2023-05-17 12:34:22''2023-05-17 12:34:22.432'
Note: It's also possible to create interpolated strings using `:
`Five plus five: {5 + 5}`
Operators
All the common arithmetic operators are supported, that is, *, /, +, -, ^.
Additionally & can be used for string concatenation.
Coalesce operator: ??
?? can be used to replace null values. E.g. the following evaluates to "test":
null ?? (1 / 0) ?? "test"
(and is equivalent to writing coalesce(null, 1 / 0, "test"))
Date time arithmetics
Some date/time arithmetics are supported:
One week ago: now() - days(7)
End of next year: endofyear() + years(1)
Sales as they were right at the end of the previous month:
sum(SalesAmount) from history startofmonth() - milliseconds(1)
Operator precedence
In priority order (i.e. operators listed first will be evaluated first):
^*/+-&??
Conditions and comparison operators
Conditions can be used in if expressions and in the where clause.
Equal/not equal: == !=
x == y
x == null
x != y
x != null
Note: Comparisons with the null literal receive special treatment that
variable comparisons do not:
The condition x == y evaluates to false when
x and y are both null while both of these conditions
would evaluate to true: x == null, y == null.
Note: <> can be used as an alias for != but
is generally not recommended as it doesn't create a natural symmetry with the pattern matching operators.
Match pattern: =~ !~
* matches any character zero or more times.? matches any character exactly once.<*> and <asterisk> match the * character.<?> and <question-mark> match the ? character.Examples:
x =~ "K*strukt"x !~ "75??"x =~ "*<*>*" // Anything that contains a '*'
Equal/not equal to any: [not] in {...}
Examples:x in {1, 2, 3}x not in {4, 5, 6}null in {a, b, c}x not in {0, null}"test" in "abc;test;def""tag" not in Region.MultiValuedPropertyx in query(Region where Region.Country == "SWE")
Note: Similar to ==/!= comparisons, null checks can be done using the in
keyword when using the null literal. Comparing other expressions that evaluate to null will
always result in the condition being false however.
Note: When using the in query(<subquery>) syntax then
null values are filtered out from the subquery data before the match is performed.
This causes conditions like null in query(null) to always evaluate to false.
Tuples in/not in: (a, b) [not] in {(.., ..), (.., ..)}
Examples:(a, b) in {(1, 2), (3, 4)} (equivalent to (a == 1 && b == 2) || (a == 3 && b == 4))(a, b) not in {(1, null), (null, 4)}
And/or: && ||
Examples:if (x == 1 && y > 1, 1, 0)where a == 1 || a == 10
Condition evaluation order
In priority order (i.e. conditions listed first will be evaluated first):
not&&||in
Functions
Conditional evaluation
If
if (condition, when_true, [when_false]): <typeof when_true>
Case
case (condition, when_true, (condition_n, when_true_n)*, [else]): <typeof when_true>
Example:
case (
x == 1, "x is one",
x < 100, "x less than 100",
"x is something else"
)
Switch
switch (value, when_equal, then, (when_equal_n, then_n)*, [else]): <typeof then>
Example:
switch (x
1, "x is one",
2, "x is two",
"x is something else"
)
Type conversion and date manipulation
Convert
convert(expression: any, target_type: Type): <typeof target_type>
Where Type is any of:
intlongdecimaltimedatedatetimestringstring(length)
Note: When using the convert function to convert date/times the only officially
supported formats are YYYYMMDD[ HH:MM[:SS]] and YYYY-MM-DD[ HH:MM[:SS]] but other,
non-official, formats may convert successfully as well.
Note: The convert function returns null if a conversion fails.
Use direct casts if a runtime error should be thrown instead. E.g. int("123"),
string(now()) or string(now(), 4).
Date functions
dateadd(date1: datetime | date, interval_amount: number, interval_type: IntervalType): <typeof date1>
datediff(date1: datetime | date, date2: datetime | date, interval_type: IntervalType): int
Where IntervalType is any of:
'year''quarter''month''day''hour''minute''second''millisecond'
Note: The datediff function extracts the date parts of the arguments
and returns the integer difference, rather than computing the whole number of passed time units.
This means that e.g.
datediff('2025-08-26', '2025-10-01', 'month') returns 2 (10 - 8) and
datediff('2025-03-31', '2025-04-01', 'quarter') returns 1 (2 - 1).
Windowing and row navigation: over for
It's possible to navigate within a result set, e.g. to retrieve previous or following row values or to perform cumulative/running computations.
[FUNCTION_CALL] [WINDOW BOUND] [ordered by (Col1, Col1)] [partitioned by (Col1, Col2)]
Window row bounds
The window bound specifies the source row(s) that the function call is applied to and can be any of the following:
for previous rowfor next rowfor row offset [-]NUMover [all|COUNT] previous rows[-]over [all|COUNT] next rows[-]over rows between [all|COUNT] previous rows and [all|COUNT] next rowsover all rows
Note: The row/rows keyword(s) can normally be omitted for brevity.
Note: When using the over keyword to specify a single bound only
(i.e. omitting either the lower or upper part of the range using the between keyword)
then the current row is included in the range by default.
The - operator can be used to explicitly exclude the current row.
This means that:
- sum(Sales) over 3 previous rows includes the current row and three preceding rows.
- sum(Sales) over 3 previous rows- includes the three preceding rows only.
- sum(Sales) over rows between 3 previous and 1 previous also includes the three preceding rows.
- sum(Sales) over rows between 3 previous and 3 next includes 7 rows in total
(3 preceding, 3 following and the current row).
Window row bound examples
Select examples to view the expression results and hover cells to see the underlying window.
sum(Sales) ...
| Year | Sales |
|---|---|
| 2021 | 12 |
| 2022 | 10 |
| 2023 | 9 |
| 2024 | 11 |
| 2025 | 13 |
Partitioning and ordering
The partitioned by clause determines how the data rows are grouped in the aggregation
of the function call.
E.g. max(Sales) partitioned by (Region) will return the top sales for the region
of the current row.
sum(Sales) over all previous partitioned by (Region) will return the sum of sales
accumulated per region.
The ordered by clause determines the sorting of the data rows in the windowed function call.
E.g. first(Region) partitioned by (Period) ordered by (Sales desc) returns the period
that had the best sales within the current region.
Note: An ordered by clause is added implicitly (if possible and
if there isn't already one). The implicit ordering will be based on the dimensional scope of the
function call.
This means that e.g. an expression like sum(Sales) over all previous
is computed based on the dimension values of the Sales value table, regardless of
how the main query is sorted.
Nested aggregations
A windowed function is never aggregating, which means that
implicit grouping will not be added when a normally aggregating function is used with a window.
For instance, assuming Sales has a Period dimension, then the following query:
sum(Sales) is normally transformed to something similar to sum(Sales), Period group by Period.
The following, on the other hand:
sum(Sales) over all previous will be transformed to
sum(Sales) over all previous ordered by (Period asc), Period
This means that queries cannot be immediately used to define variables if they use
custom dimension expressions or if the inclusions could cause internal data duplication
(which for instance happens when an include does not use all dimensions of a value table).
It is however possible to nest a windowed aggregation in a regular aggregation to allow grouping.
Note that this, depending on the aggregation and data, could cause unexpected results as data could
be aggregated multiple times.
E.g. to create a variable that contains the end-of-quarter total (assuming there is a Quarter dimension):
var EndOfQuarterSales =
max(sum(Sales) over all previous partitioned by (startofquarter(yyyymm(Period)))),
dimset Quarter = startofquarter(yyyymm(Period))
// Note that the above is just an example and would normally be written as:
sum(Sales), Quarter = startofquarter(yyyymm(Period))
Spread expressions
The spread keyword can be used to spread data in one table over a specific target table.
Syntax:
spread (SOURCE [proportionally|evenly] over TARGET)
spread (sum|average (SOURCE) [proportionally|evenly] over TARGET)
Examples
Consider the following tables:
SourceValues
| Value | D1 |
|---|---|
| 10.0 | Code0 |
| 15.0 | Code1 |
| 20.0 | Code2 |
TargetKeys
| Value | D1 | D2 |
|---|---|---|
| 1.0 | Code1 | A |
| 2.0 | Code1 | B |
| 100.0 | Code2 | A |
| 150.0 | Code2 | B |
Then the following example spread expressions are calculated as:
spread (SourceValues over TargetKeys)
| Calculation | Value | D1 | D2 |
|---|---|---|---|
(1.0 / (1.0 + 2.0)) * 15.0 = |
5.0 | Code1 | A |
(2.0 / (1.0 + 2.0)) * 15.0 = |
10.0 | Code1 | B |
(100.0 / (100.0 + 150.0)) * 20.0 = |
8.0 | Code2 | A |
(150.0 / (100.0 + 150.0)) * 20.0 = |
12.0 | Code2 | B |
spread (average(SourceValues) over TargetKeys)
| Calculation | Value | D1 | D2 |
|---|---|---|---|
(1.0 / (1.0 + 2.0)) * (15.0 * 2) = |
10.0 | Code1 | A |
(2.0 / (1.0 + 2.0)) * (15.0 * 2) = |
20.0 | Code1 | B |
(100.0 / (100.0 + 150.0)) * (20.0 * 2) = |
16.0 | Code2 | A |
(150.0 / (100.0 + 150.0)) * (20.0 * 2) = |
24.0 | Code2 | B |
spread (SourceValues evenly over TargetKeys)
| Calculation | Value | D1 | D2 |
|---|---|---|---|
15.0 / 2 = |
7.5 | Code1 | A |
15.0 / 2 = |
7.5 | Code1 | B |
20.0 / 2 = |
10.0 | Code2 | A |
20.0 / 2 = |
10.0 | Code2 | B |
Note: If a strategy is not specified then data will be spread proportionally by default.
Note: If the type of aggregated value to maintain is not specified (using the sum
or average functions) then the sum will be spread by default.
Note: The spread keyword is essentially a syntactic sugar that is rewritten to
a query where the target becomes the main and the source becomes a secondary include.
Due to this, there are some restrictions on what type of query a spread expressions can be used in
and there is limited support for combining different types of spread expressions in the same query.
Generating value codes
The makecode() language construct can be used
to generate value codes that are unique within a result set and only use valid characters.
Syntax:
makecode(fromString? string) [within (<query>)]: string
Examples
makecode() // => "<uuid>"
makecode() within (Product where Product.Active == 1) // => "1", "2", etc
makecode("From a name?") // => "FromAName"
makecode("Product name") within (Product) // => "ProductName", "ProductName_2", etc
Note: The fromString argument must not reference any tables
or variables (i.e. it must be a constant or something like string(now())).
Note: The within query must not be aggregating or grouping
and must return a single string-valued column.
Other functions
sum
Returns the sum of a set of numbers.
sum(n: number): <typeof n>
min
Returns the minimum value from a set of values.
min(n: number): <typeof n> min(p: string): <typeof p> min(p: datetime | date | time): <typeof p>
max
Returns the maximum value from a set of values.
max(n: number): <typeof n> max(p: string): <typeof p> max(p: datetime | date | time): <typeof p>
average
Returns the average of a set of numbers.
average(n: number): decimal
count
Counts the number of non-null entries in a set of values.
count(): int count(p: any): int
product
Returns the product of a set of numbers.
product(p: number): decimal
distinctcount
Counts the number of unique non-null entries in a set of values.
distinctcount(p: any): int
concatenatex
String-aggregates a set of values.
concatenatex(p1: string, separator?: string): string
first (windowed)
Returns the first encountered value in a windowed set of rows.
first(p: any): <typeof p>
last (windowed)
Returns the last encountered value in a windowed set of rows.
last(p: any): <typeof p>
lag (windowed)
Returns the previous value in a windowed set of rows.
lag(p: any): <typeof p> lag(p1: any, offset: int): <typeof p1>
lead (windowed)
Returns the next value in a windowed set of rows.
lead(p: any): <typeof p> lead(p1: any, offset: int): <typeof p1>
concatenate
String-concatenates two or more values. Also aliased as the '&' operator.
concatenate(p1: any, p2: any, pn*: any): string
left
Returns the n first characters of a string.
left(text: string, n?: int): string
right
Returns the n last characters of a string.
right(text: string, n?: int): string
substring
Returns a subset of characters of a string.
substring(str: string, startIndex: int, length?: int): string
length
Returns the number of characters in a string.
length(p: string): int
upper
Creates an upper case version of a string.
upper(p: string): <typeof p>
lower
Creates a lower case version of a string.
lower(p: string): <typeof p>
substitute
Finds and replaces one or more substrings in a string.
substitute(text: string, find: string, replace: string, extras*: (string, string)): string
repeat
Repeats a specific string a specific number of times.
repeat(text: string, count: int): string
padstart
Ensures a string has a specific length by padding the start of it with a specific character ('0' by default).
padstart(text: string, length: int, padding?: string): string
padend
Ensures a string has a specific length by padding the end of it with a specific character ('0' by default).
padend(text: string, length: int, padding?: string): string
ellipsize
Shortens a string to a specific max length, adding a '...' ellipsis if the string was shortened.
ellipsize(text: string, maxLength: int): string
now
Returns the current date and time.
now(): datetime
today
Returns the start of today's date (with the time set to 00:00 UTC).
today(): datetime
date
Creates a date from individual parts.
date(year: int, month: int, day: int): date
time
Creates a time from individual parts.
time(hour: int, minute: int, second?: int, millisecond?: int): time
datetime
Creates a date/time from individual parts.
datetime(year: int, month: int, day: int): datetime datetime(year: int, month: int, day: int, hour: int, minute: int, second?: int, millisecond?: int): datetime
startofyear
Returns the first of January using the year of a specific date (with the time set to 00:00 UTC). Uses the current date if none is specified.
startofyear(): datetime startofyear(p: datetime | date): datetime
endofyear
Returns the last of December using the year of a specific date (with the time set to 23:59:59 UTC). Uses the current date if none is specified.
endofyear(): datetime endofyear(p: datetime | date): datetime
startofquarter
Returns the start of the quarter for a specific date (with the time set to 00:00 UTC). Uses the current date if none is specified.
startofquarter(): datetime startofquarter(p: datetime | date): datetime
endofquarter
Returns the end of the quarter for a specific date (with the time set to 23:59:59 UTC). Uses the current date if none is specified.
endofquarter(): datetime endofquarter(p: datetime | date): datetime
startofmonth
Returns the start the month for a specific date (with the time set to 00:00 UTC). Uses the current date if none is specified.
startofmonth(): datetime startofmonth(p: datetime | date): datetime
endofmonth
Returns the end of the month for a specific date (with the time set to 23:59:59 UTC). Uses the current date if none is specified.
endofmonth(): datetime endofmonth(p: datetime | date): datetime
startofday
Returns the start of the day for a specific date (with the time set to 00:00 UTC). Uses the current date if none is specified.
startofday(): datetime startofday(p: datetime | date): datetime
endofday
Returns the end of the day for a specific date (with the time set to 23:59:59 UTC). Uses the current date if none is specified.
endofday(): datetime endofday(p: datetime | date): datetime
year
Returns the year for a specific date, or the current year if a date is not specified.
year(): int year(p: datetime | date): int
quarter
Returns the quarter for a specific date, or the current quarter if a date is not specified.
quarter(): int quarter(p: datetime | date): int
month
Returns the month for a specific date, or the current month if a date is not specified.
month(): int month(p: datetime | date): int
dayofmonth
Returns the day of month for a specific date, or the current day of month if a date is not specified.
dayofmonth(): int dayofmonth(p: datetime | date): int
dayofweek
Returns the day of week for a specific date, or the current day of week if a date is not specified.
dayofweek(): int dayofweek(p: datetime | date): int
week
Returns the ISO 8601 week for a specific date, or the current week if a date is not specified.
week(): int week(p: datetime | date): int
hour
Returns the hour for a specific date/time, or the current hour if a date/time is not specified.
hour(): int hour(p: datetime | time): int
minute
Returns the minute for a specific date/time, or the current minute if a date/time is not specified.
minute(): int minute(p: datetime | time): int
second
Returns the second for a specific date/time, or the current second if a date/time is not specified.
second(): int second(p: datetime | time): int
yy
When a date is specified: Returns the date as a string on the form 'yy'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yy'.
yy(p: string): date yy(p: datetime | date): string
yymm
When a date is specified: Returns the date as a string on the form 'yymm'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yymm'.
yymm(p: string): date yymm(p: datetime | date): string
yyww
When a date is specified: Returns the date as a string on the form 'yyww'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yyww'.
yyww(p: string): date yyww(p: datetime | date): string
yymmdd
When a date is specified: Returns the date as a string on the form 'yymmdd'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yymmdd'.
yymmdd(p: string): date yymmdd(p: datetime | date): string
yyyy
When a date is specified: Returns the date as a string on the form 'yyyy'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yyyy'.
yyyy(p: string): date yyyy(p: datetime | date): string
yyyymm
When a date is specified: Returns the date as a string on the form 'yyyymm'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yyyymm'.
yyyymm(p: string): date yyyymm(p: datetime | date): string
yyyyww
When a date is specified: Returns the date as a string on the form 'yyyyww'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yyyyww'.
yyyyww(p: string): date yyyyww(p: datetime | date): string
yyyymmdd
When a date is specified: Returns the date as a string on the form 'yyyymmdd'. When a string is specified: Parses the string and returns a date or null if the string is not on the form 'yyyymmdd'.
yyyymmdd(p: string): date yyyymmdd(p: datetime | date): string
mm
Formats a date on the form 'mm'.
mm(p: datetime | date): string
ww
Formats a date on the form 'ww'.
ww(p: datetime | date): string
mmdd
Formats a date on the form 'mmdd'.
mmdd(p: datetime | date): string
dd
Formats a date on the form 'dd'.
dd(p: datetime | date): string
mod
Returns the remainder after dividing a number with another number.
mod(p1: number, p2: number): number
abs
Returns the absolute value of a specific number.
abs(n: number): <typeof n>
floor
Rounds down a specific number to the nearest integer.
floor(n: number): int
ceiling
Rounds up a specific number to the nearest integer.
ceiling(n: number): int
log
Returns the logarithm of a specific number. Uses base 10 if a base is not specified.
log(n: number, base?: number): decimal
ln
Returns the natural logarithm of a specific number.
ln(n: number): decimal
exp
Returns Euler's constant (e) raised to the power of the given numeric value.
exp(n: number): decimal
round
Rounds a number to a specific precision.
round(n: number, precision: int): <typeof n>
coalesce
Returns the first non-null value from two or more arguments.
coalesce(p1: any, pn*: any): string | number | <typeof p1>
rangemax
Picks the largest (non-null) value from two or more numeric arguments.
rangemax(p1: number, p2: number, pn*: number): number
rangemin
Picks the smallest (non-null) value from two or more numeric arguments.
rangemin(p1: number, p2: number, pn*: number): number
autodim
Replaces any main dimension references with a coalesce of all potential qualified dimensions.
autodim(expr: any): <typeof expr>
typeof
Returns the Kex type for the specified argument. Can be used to debug expressions.
typeof(p: any): string
aggregationof
Returns the aggregation behavior for the specified argument. Can be used to debug function calls and other expressions.
aggregationof(p: any): string