In this article:

Inserting Operand Based on Dimension Selection

Setting Up Filtering of Operand Aggregated Values

Inserting Operand Based on Dimension Attributes

Changing Operand

Inserting Functions

Generating Calculation Formula

A calculation formula is generated in the formula area and is used to convert data from source to consumer.

Operands, functions, arithmetic operations, numbers, comparison characters and round brackets can be used in a formula.

An operand is a data element from data source that is the object of operations during formula calculation. A function is a sequence of operations executed with an operand on formula calculation.

Operands and functions can be inserted on the side panel. Numbers, comparison characters, and round brackets can be entered from the keyboard.

Available types of operands:

Calendar frequency of operands must match the formula's calendar frequency. If a formula requires operands with the calendar frequency that is different from the formula's calendar frequency, use aggregation. They enable the user to cast the calendar frequency of operands to the formula's calendar frequency.

Example of using aggregation function

Inserting Operand Based on Dimension Selection

Operand calculation results in the data value obtained by specified selection of dimension elements.

To insert an operand based on dimension selection:

  1. Go to the Operands tab on the side panel.

  2. Select in the drop-down list the Value from Source operand type.

  3. Select the data source used by the operand in the drop-down list. Dimension elements of the selected data source will be displayed. For example:

  1. If required, in the drop-down list select the aggregation method if the operand must execute aggregation.

Aggregation enables the use of several values of operand dimension aggregated by the selected method in the formula. Aggregation can be set up by any operand dimension, except the calendar one.

TIP. After the operand is created, aggregation calculation can be set up by the dimension values that correspond with specific rules. For details see the Setting Up Filtering of Operand Aggregated Values section.

  1. If required, select the Show Linked Dimensions checkbox if the operand must contain linked dimensions of data source. Dimension links can be set when calculation block data sources are being selected.

  2. Select the element, based on which the operand is created, for each dimension. If an operand must execute aggregation, select several elements to be aggregated.

To change element display type by existing dimension attributes select the Display Settings item in the context menu of the list of elements and select one of the attributes. Hidden attributes cannot be used. By default, the formula area and the list of elements on the side panel display element names. When element display type changes, display type of corresponding operands in formula changes.

To select all dimension elements, select the Select All item in the context menu of the list of elements.

To deselect all dimension elements, select the Deselect All item in the context menu of the list of elements.

To expand the hierarchy of the list of elements, select the Expand All item in the context menu of the list of elements.

To collapse the hierarchy of the list of elements, select the Collapse All item in the context menu of the list of elements.

If a data source contains controlling dimensions, when the controlling dimension element is selected, the elements are updated in controlling dimensions.

To set up a method for selecting elements, click the Set Up Element Selection button. The Set Up Element Selection dialog box opens:

Set element selection option:

  1. If calendar settings are required for the operand that are different from the formula, select the calendar dimension:

NOTE. The dimension is absent, if calendar is used as a regular dimension in data consumer.

Determine operand calendar settings:

    1. Select operand calendar frequency in the Level drop-down list. The Formula element is selected by default, and operand calendar frequency matches with the formula calendar frequency.

NOTE. If operand calendar frequency differs from calculation formula calendar frequency, after the formula is calculated, data will be calculated by formula calendar frequency.

    1. Select a method for getting calendar data:

      • Date. It is used if it is required to fix the date, on which operand data is taken. For example, data for 2018.

      • Index. It is used if it is required to fix the row point, on which operand data is taken. For example, the third point from row start.

      • Offset. Default value. It is used if all operand data must be offset forward or backward by the specific number of points in the time period relative to the entire formula. For example, the formula uses data starting from 2000, and the operand with the offset will use data starting from 2003.

Set a method for sending operand to calculation using the Row as Array checkbox:

For example, the formula has annual frequency and is calculated from 2000 to 2018. The 2000 operand value will be used to calculate the formula for 2000, the 2001 operand value will be used to calculate the formula for 2001, and so on.

Determine calendar aggregation settings for the operand:

Calculating operand with running total helps to take into account data for previous periods in the current period. For example, there is an operand with the following values: 2015 - 3, 2016 - 5, 2017. - 9, 2018. - 4. If the Running Total checkbox is selected, and the From Calculation Start period is selected, the following operand values will be used on calculation: 2015 - 3, 2016 - 8, 2017. - 17, 2018 - 21.

  1. In the formula area put the cursor to where the operand should be placed, and click the Insert into Formula button on the side panel.

The operand based on dimensions selection is inserted to the formula. For example:

{ Financial indicators! FINANCIAL SOUNDNESS INDICATORS | As of accounting date of accounting period[t] }

The operand text starts with the data source name ended with an exclamation mark. Then it is followed by names of elements selected in dimensions.

If the Set Up Element Selection dialog box specifies that dimensions selection is set by parameter, parameter name will be enclosed in figured brackets. For example:

{ Financial indicators! LIQUIDITY INDICATORS, BUSINESS ACTIVITY INDICATORS | {Accounting date}[t] }

Aggregation settings influence the displaying of operand text:

{ ∑ Financial indicators! LIQUIDITY INDICATORS | As of accounting date of accounting period[t] }

{ ∑ Financial indicators! LIQUIDITY INDICATORS, BUSINESS ACTIVITY INDICATORS | As of accounting date of accounting period[t] }

If three or more elements are aggregated, only the first and the last elements are specified via the … character. For example:

{ ∑ Financial indicators! LIQUIDITY INDICATORS...ADDITIONAL PARAMETERS | As of accounting date of accounting period[t] }

{ ∑ Financial indicators! All | As of accounting date of accounting period[t] }

{ ∑ Financial indicators! Filter(LIQUIDITY INDICATORS...ADDITIONAL PARAMETERS) | As of accounting date of accounting period[t] }

[t] at the end of the operand means full selection by calendar dimension that is used by default. The [t] character is added or changed depending on specified calendar settings:

{ Financial indicators! LIQUIDITY INDICATORS | As of accounting date of accounting period[Half-years.t] }

{ Financial indicators! LIQUIDITY INDICATORS | As of accounting date of accounting period[2018] }

{ Financial indicators! LIQUIDITY INDICATORS | As of accounting date of accounting period[3] }

{ Financial indicators! LIQUIDITY INDICATORS | As of accounting date of accounting period[t-1] }

Setting Up Filtering of Operand Aggregated Values

If data aggregation is used for operand based on dimension selection, one can set up filtering of aggregated values and aggregate only by the dimension values that correspond to specific conditions. Dimension elements can be filtered by means of applying conditions to dimension attribute values.

To set up filtering, open the Advanced Filter dialog box:

  1. Click the operand in the formula area.

  2. Go to the Operands tab on the side panel and make sure that aggregation method is selected for the operand in the Aggregation drop-down list.

  3. In the drop-down list select the dimension, for which filtering of aggregated values is set up, and click the Advanced Filter button.

After executing the operation the Advanced Filter dialog box opens:

Set conditions for filtering aggregated values:

  1. Click the Add button. A new row for setting condition will be added to the table.

  2. In the AND/OR column select one of the logical operators in the drop-down list:

To group filtering conditions, select one by one checkboxes next to conditions in the first column and click the Group button. After executing the operations the selected filtering conditions are grouped:

NOTE. The condition determined for the first rule is applied to the group of rules.

To ungroup the filtering conditions, select checkboxes next to each condition in the group and click the Ungroup button.

  1. In the Attribute column select the dimension attribute, for which filtering condition will be checked. If the dimension attribute contains multiple value, all attribute values will be compared on checking filtering condition. If at least one attribute value satisfies the condition, the dictionary element will automatically satisfy the condition.

To filter dictionary elements by linked dictionary attributes, select in the list the attribute linked with this dictionary. After this the Value column will display the list of linked dictionary elements.

  1. Select the checkbox in the NOT column if a reverse condition should be set. For example, attribute value it not equal to five.

  2. Select one of the condition types in the Condition column:

NOTE. The set of displayed condition types depends on the attribute data type and type of compared value selected in the Attribute and Type columns.

  1. Select a type of compared value in the Type column:

  2. Select checkbox in the Ignore Empty checkbox column if it is required to filter dimension elements, which have empty values by the attribute specified in the Attribute column. For example, if the checkbox is selected in a calculation block, element with empty values will not be excluded from calculation during calculation of the algorithm containing this block.

  3. In the Value column specify the value to be compared or select in the drop-down list the parameter/dimension attribute, which sets this value.

Data type of value is set according to the attribute. If advanced filter is set for a calendar dimension, calendar dimension levels can be selected for the Calendar Block Type attribute as a value.

NOTE. If a condition is set for the level excluded from calendar dimension, the selected attribute value will be reset.

If the Key, Identifier, or Owner dimension attribute is selected, dimension elements by name can be selected as a value. Multiple selection is available.

If a dimension attribute is linked with another dictionary, linked dictionary elements can be selected as a value.

If the In condition type is selected, click the Edit button to enter value. In the Edit Value dialog box that opens click the Add button. A string to enter value is added:

To delete the value, select checkbox next to the value in the first column and click the Delete button.

After the values are entered, click the Apply button. The specified values will be displayed in the Value column in the same line and separated with a comma.

  1. Select the checkbox in the Do not Filter from Empty Values column, if parameter value in the Value column is used for comparison. If the checkbox is selected, calculation result depends on the parameter value:

NOTE. It is available only for the Parameter type of compared value.

  1. Repeat the steps to add several filtering conditions.

After executing the operations, filtering of aggregated operand values is set up.

To delete the filtering condition, select checkbox next to the condition in the first column and click the Delete button.

Inserting Operand Based on Dimension Attributes

Operand calculation results in the value of the specified attribute obtained by the specified dimension selection.

  1. Go to the Operands tab on the side panel.

  2. Select the Attribute Value operand type in the drop-down list.

  3. Select the data source dimension used by the operand in the drop-down list. The lists with selected dimension elements and attributes will be displayed. For example:

  1. If it is required for the operand to redetermine the dimension element selection specified when data consumer and data source were added, select the Redetermine Selection checkbox and set selection in the Elements tab.

To change element display type by existing dimension attributes select the Display Settings item in the context menu of the list of elements and select one of the attributes. Hidden attributes cannot be used. By default, the formula area and the list of elements on the side panel display element names. When element display type changes, display type of corresponding operands in formula changes.

To select all dimension elements, select the Select All item in the context menu of the list of elements.

To deselect all dimension elements, select the Deselect All item in the context menu of the list of elements.

To expand the hierarchy of the list of elements, select the Expand All item in the context menu of the list of elements.

To collapse the hierarchy of the list of elements, select the Collapse All item in the context menu of the list of elements.

To select elements using selection schema, select the Apply Selection Schema context menu item and select the selection schema in the drop-down list.

To search for element or attribute, start typing its partial of full name in the search field. After executing the operation the elements or attributes, which names satisfy search conditions, will be displayed.

  1. Select the attribute on the Attributes tab, based on which values an operand is generated.

  2. In the formula area put the cursor to where the operand should be placed, and click the Insert into Formula button on the side panel.

After executing the operations, the operand based on dimension attributes is inserted to the formula. For example:

{Financial indicators!BUSINESS ACTIVITY INDICATORS.Name}

The operand text starts with the data source name ended with an exclamation mark. Then it is followed by the dimension name or the selected element name if the dimension elements selection has been redetermined. Then it is followed by the selected attribute name separated with a period.

Changing Operand

To change the inserted operand:

  1. Click the operand in the formula area. Operand settings are displayed on the side panel without the ability to change a data source:

  1. Change operand settings.

  2. Click the Change Operand button on the side panel.

After executing the operations, changes are made to the settings of the selected operand.

Inserting Functions

The following function insert methods are available:

On the side panel

With key shortcuts

To get description of available functions, see the Functions Available in Expression Editor section.

NOTE. To increase performance of Foresight Analytics Platform, use the ?: conditional operator instead of the Iif function.

See also:

Setting Up Calculation Formulas | Setting Up Additional Calculation and Data Saving Parameters