Expression Editor: Information panels

To set up a formula for calculating calculated fact values, use expression editor window.

Relations Formula

Relations can be used to get values by shifting relative to the current calculation point.

The expression editor window used for setting relation formula looks as follows:

To add a relation, click the Add button. After executing the operation, a relation is added with default values. Below the added relation there are all data model dimensions displayed.

To edit settings of the selected relation, click the Edit button. The Relation Properties dialog box opens:

Set the parameters:

Name. Enter short description of the relation. The relation is named Relation by default.
Identifier. Enter unique identifier of the relation. The default identifier is ITEM.

To save relation properties, click the Apply button.

To delete the selected relation, click the Delete button.

To set up relation of the selected dimension:

Execute one of the operations:

Click the Edit button.
Double-click on the selected dimension.
Select the Relation Formula item if relation formula was set up for the selected dimension.

The Relation Formula dialog box opens:

Select how the relation formula will be set in the drop-down list:

None. Default value. The current position will be used as the dimension position.
Levels Up. Applying the relation results in the parent element shifted by the specified number of levels from the current element. Set the number of levels in the value editor. The number of levels up should not exceed the total number of levels minus one.
Elements Back. Applying the relation results in the element of the same level shifted by the specified number from the current element. Set the number in the value editor.
Specified Element. Applying the relation results in the element selected in the drop-down list.
Expression. Applying the relation results in the element, which index is calculated by the specified expression. The expression is set using the button located next to the edit box or the expression editor. An expression should be started from the current dimension element, to do this, use the T button. Next, one can specify the shift, for example, T.PREV, T.PREV.UP, T.UP.NEXT, and so on. Use the buttons to create an expression:

T. The current element. It must always be used on creating an expression.
UP. One level up.
DOWN. One level down.
NEXT. Next element.
PREV. Previous element.

To open the expression editor, click the Insert Formula button.

To calculate element index in the expression, one can use various arithmetic, logical, text, custom, and transformation functions.

After the setup is complete, the relation formula is displayed next to the dimension, for example:

The added relations are available for setting up calculated facts.

To delete the relation formula for the selected dimension, click the Clear button.

After editing the relation formula, all formulas, which use this relation, will be refreshed.

A calculated fact is a function of values of other facts, calculated facts, or relations.

The expression editor window used for setting calculated fact formula looks as follows:

Create a formula, by which the calculated fact will be calculated.

Arithmetic operations, functions, numbers, comparison characters and round brackets can be used in a formula. There are certain features:

Functions can be inserted both using the keyboard and the special panel.
Numbers can be inserted only using the keyboard.

A statement is a data element, with which actions during expression calculation are executed. In the expression editor statements can be inserted using the data panel and are displayed in curved brackets.

Statements are displayed on the statements panel in the expression editor:

The following are used as statements:

data model table fields, including calculated fields.
SQL queries added to a data model.
Calculated facts.

To insert a statement to the formula area, execute one of the operations:

Using the statements panel:

Select the statement and click the Insert into Formula button.
Double-click on the statement.
Drag the statement to the formula area.

Using the formula area:

Enter statement name in the formula area.
Click in the formula area where a statement should be inserted, press CTRL+SPACE and select the required statement on the popup panel.

To quickly search for fields, start typing part or full field name in the search string. After executing the operation the fields panel displays the fields, which names satisfy search conditions.

To set up displaying of fields, select the option in the drop-down menu of the Display Settings button:

Name. Default value. Only statement names are displayed.
Identifier. Only statement identifiers are displayed.
Name and Identifier. Statement names and identifiers are displayed in the following format: <name> (<identifier>).

To hide the statements panel, click the Hide Statements Panel button, to show the statement panel, click the Show Statements Panel button.

Features of calculated fields use in formulas:

Calculated fields that contain other calculated fields in their formulas are considered as regular calculated fields. Operations identical to operations with regular fields can be executed with these fields: editing, saving, validation, aggregation, adding to visualizers, and so on.
The saved changes of the calculated field are taken into account in calculation of other calculated field or filter based on this calculated field.
If a calculated field has become incorrect, the other calculated field or filter based on this calculated field also become incorrect. Incorrect fields will be hidden on the side panel. Visualizers that are built on data of incorrect fields become incorrect.
When the calculated field used in the formula of other calculated field or filter is deleted, a message is displayed informating that this field cannot be deleted.

A function is a sequence of operations executed with a statement during expression calculation.

A statement is a single operation executed with an operand during expression calculation.

In the expression editor functions and statements can be inserted using the functions panel or entered using the keyboard.

Functions and statements are displayed on the functions and statements panel on corresponding tabs:

To insert a function or statement to the formula area, execute one of the operations:

Using the functions and statements panel:

Select the function/statement. The description panel is displayed. Click the Insert into Formula button.
Double-click the function/statement.
Drag the function/statement to the formula area.

Using the formula area:

Enter the function/statement in the formula area.
Click in the formula area where a function or statement should be inserted, press CTRL+SPACE and select the required function/statement on the popup panel.

To quickly search for a function or statement, start typing function/statement name partially or fully in the search string. After executing the operation the functions and statements panel displays the functions/statements, which names satisfy search conditions.

To hide the functions and statements panel, click the Hide Functions and Statements Panel button, to show - click the Show Functions and Statements Panel button.

To apply the specified formula and close the Calculated Facts Formula dialog box, click the Apply button. The saved formula will be displayed in the formula bar in the Create Calculated Fact or Calculated Fact Settings dialog box.

Formula correctness is checked automatically during its setup. Error text is displayed to the errors panel where the place of error is specified. To put the cursor to the place of error click the errors panel.