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:

(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):

(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:

  1. Value tables that are not tagged as being optional, all or cross includes.
  2. Dimensions (that are not included using the all or cross keywords).
  3. 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).
  4. Use the table that appears first if multiple tables end up with the same ranking.

Afterwards the main dimensions are determined:

  1. Use the dimension from the main table
  2. Add any cross-included dimensions. Note that this means that cross includes are always performed before all includes 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:

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:

(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:

(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:

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

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:

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

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:

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.

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:

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:

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:

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:

(Note that this is equivalent to a query like sum(Sales) where Region == "EAST")

To accumulate something based on a range:

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

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.MultiValuedProperty
x 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):

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:

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:

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:

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