Calculation rules
Introduction¶
Calculation rules allow you to run complex calculation operations and can be written with syntax similar to MD syntax.
Note: operation only available from version SP25, which can only be viewed and saved with the ESG licence.
The function is only available if function "0004167 - Calculation rules" is enabled for the user.
Each calculation rule is based on the definition of operands, with their own logical dimensions and script dimensions that use similar syntax to MD syntax. Sets of calculation rules can be associated with an "Insert with calculation rule" DTP operation.
Script syntax¶
The syntax used to express each script is based on the Custom MD calculations syntax. With the following conventions:
- The script always starts with the notation: TGKML 1.0 and is made up of several statements.
- In a script, statements are separated by ";".
- In every statement, for the assignment use: ":=".
- The statement must have one assignment only and must finish in ";".
- 2 cubes may be used: [TARGET] and [SOURCE], which indicate the write dataset and the read dataset, respectively.
- The cube is always mandatory for every operand and must be indicated as [cube].[operand].
- [TARGET] must only be present to the left of the assignment ([TARGET].[operand] and represents the output operand).
- Only an output operand can be present to the left of the assignment.
- [SOURCE] must only be present to the right of the assignment ([SOURCE].[operand] and represents an input operand).
- To insert a comment, use "//": any characters following the // symbol until the end of the row will be ignored. Comments can be inserted at any point in the script.
- An assignment is always of the following type: [TARGET].[operand] :=
, where the expression may be a simple assignment like: [SOURCE].[operand], constant, or an expression that includes mathematical operations or language-specific operations (fixed point, bound, filter, etc.). - Arithmetic operations can be used: +, -, *, / and the expression may contain mathematical operations with scalars.
- Parentheses ( ) can be used to indicate the expression's execution order.
Logical dimensions window¶
This window contains a list of the logical dimensions associated with the operands. By default, the list is sorted by the script operand code column. Every operand, whether input or output, can be associated with several logical dimensions (e.g. GENDER, GEOGRAPHICAL_AREA, etc.) and each logical dimension must be mapped to a dataset field.
For every statement, the calculation identifies the information of the logical dimensions defined for the operands of which it is made up (input/output).
| If... | Then... |
|---|---|
| the input operands if which it is made up have the same logical dimensions. | the operands are uniform. |
| at least one of the input operands' logical dimensions is different. | the operands are non-uniform or irregular. |
| no logical dimensions are shared. | the operands are separate. |
| an operand does not have rows defined in the logical dimensions table. | the operand has no logical dimensions. |
Functionalities supported by the calculation rules¶
A list of the functions that can be run through the calculation rules is provided below.
| Element | Description |
|---|---|
| Copy | Simple copy of the input operand onto the output operand (both with uniform logical dimensions and non-uniform logical dimensions). |
| Arithmetic expressions (addition, subtraction, product, division) | between input operands (with homogeneous, inhomogeneous and disjointed logical dimensions). If the outcome of the formula is 0, the row is produced in output in any case. |
| Override on output operand | Override on the output operand for one or several logical dimensions. |
| Read from Fixed Point | Read from fixed point: - Of a value of the logical dimension - On several different logical dimensions separated by commas - .All - .NotNull - .Null |
| Filter on logical dimensions of input operands | Filter on the logical dimensions of the input operands of a value or list of values separated by commas. |
| Bound on input operands. | |
| Temporary calculations '@' | Saving temporary calculation using the notation [TARGET].@MyTemp := … |
| DiscardZeroes | |
| NotNode | |
| RoundNode | |
| TotalAmount | |
| UnaryMinusNode | |
| Max_FunctionNode | |
| Min_FunctionNode | |
| Sum_FunctionNode |
Additional features¶
| Element | Description |
|---|---|
| Abs (absolute value function) | gives the result without sign of the expression passed as input. The syntax to be used is ... := Abs (espr) |
| If then else | allows the conditional construct to be used to evaluate an expression and drive the output: it is up to the user to manage the homogeneity of the dimensions used. The syntax to be used is ... := if (cond) then espr1 [else espr2] end; |
| Median | expresses the median of the set of values. The syntax to be used is ... := Median( |
| Average (average function) | arithmetic mean of the values. The syntax to be used is ... := Average( |