Kex Lite
Documentation of the Konstrukt expression language used in dataset expressions.
For complete documentation on the Kex language refer to the full reference page.
Overview
Kex Lite is a subset of the complete Kex language and is used in dataset expressions.
The most notable limitation is that table-valued variables and queries are not supported. This, in turn, means
that multi-table queries and keywords like include and main are not supported.
Example Kex Lite script:
scalar TotalValue = sum($slice) where Category == "1"
// Named debug output
evaluate Data = $slice
// Anonymous debug output
evaluate TotalValue
// Return result
ValueType.Sales / TotalValue
Variables
Kex Lite supports declaring custom variables using the scalar keyword.
These variables contain a single value and can be created from custom expressions
or simple aggregating queries.
Built-in variables
There are some pre-defined variables that are available in the context of a dataset expression.
ValueType
For dataset expressions that target the valuetype dimension the ValueType
variable contains pivoted columns for each valuetype in the dataset (with aggregated values).
Example:
ValueType.Income / ValueType.Sales
For other dataset expressions the variable contains the current valuetype ID.
Example:
if (ValueType == "Sales", 500, 0)
DataType
For dataset expressions that create custom references the DataType
variable contains pivoted columns for each reference in the dataset (with aggregated values).
References are available using ID (and name for native references, unless the name conflicts with an ID or
the Plan reference).
Example:
DataType.Plan + DataType.[100] + DataType.SomeOtherExpression + DataType.[Another reference]
For other dataset expressions the variable contains the current reference type.
Examples:
if (DataType == "Plan", "Plan data", "Reference data")
if (DataType == "1", "Reference with ID 1", "Other reference or plan data")
$slice
This is a table-valued variable that contains rows available to the current dataset expression
cell context. It can be printed (using evaluate) or used to calculate aggregates.
Example:
scalar AverageEastSales = average($slice) where City.Region == "EAST"
Note: As Kex does not support mixed column types (i.e. columns that mix different types of data like strings and numbers)
the $slice variable only contains data of the same type as the dataset expression's format.
Evaluations
Variables and expressions can be evaluated using the evaluate keyword.
The output from these can be inspected by right-clicking a data cell that has been created
by a dataset expression.
Functions
Kex Lite supports most functions that are available in the complete language, with some exceptions:
- The
autodimfunction - The
spreadkeyword - The
makecodekeyword anyconditionsin queryconditions- Windowed functions (like
lagandlead) - Any function that returns or requires a
time
Additionally, control flow statements (if, for) are not supported.
Aggregation and filtering
It's possible to perform some basic aggregation and filtering of the built-in $slice variable.
Example:
scalar MaxQ1Sales = max($slice) where Month in {1, 2, 3}
Limitations:
- Only the
whereclause is supported (i.e. ordering, grouping and limiting is not supported) -
Since the query must be aggregating only constants (and other aggregations) can be used in the same query.
I.e. the following is valid:1 / (average($slice) / max($slice))
while the following is not (and requires the aggregation to be moved to a separate variable):ValueType.Sales / max($slice) - Conditions in the
whereclause only support constant values - Custom expressions cannot be aggregated (i.e. the argument must be exactly
$slice) - Windowing is not supported (i.e.
overexpressions)
Data types
Note: Data types here refers to types built into the Kex language, e.g.
string and int. Not to be confused with the DataType
variable where "data type" refers to Plan or Reference data.
Data types mostly function the same way as in the full version of Kex with the following exceptions:
- All numeric types are backed by
decimalso some rounding and truncation works differently - The
timetype is not supported