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:

• Based on dimension elements selection. It is used if the formula must use the data value obtained by the specified dimension elements selection.

• Based on dimension attributes. It is used if the formula must use the value of the specified attribute obtained by the specified dimension selection.

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:

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

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

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

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:

• All. All elements will be selected. The option is available if an operand executed aggregation.

• Selected. Default value. The elements selected by the user will be selected.

• From Parameter. Element value is set by a parameter selected in this dialog box. One can select own parameters of calculation block or calculation algorithm parameters added to the block. Parameters must have the Selection type.

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

2. 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:

• Checkbox is deselected. Default value. The operand point used in the formula depends on the date, on which calculation is executed.

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.

• Checkbox is selected. All operand values in the formulas are presented as an array, and not as a point. When inserting this type of operand to the equation use the handler that transforms operand to one value. The handler can be a statistical, mathematical, or custom function.
  For example, the minimum {X} operand value should be used in the formula. To do this, select the Row as Array checkbox and use the function for getting minimum: Min({X}).

Determine calendar aggregation settings for the operand:

• Aggregation by Level. Select the checkbox if operand calendar frequency must be increased. In the corresponding drop-down list select the frequency, which should contain operand data.
  For example, the formula has annual frequency, and the formula operand has monthly frequency. To correctly use the operand in the formula, select the Aggregation by Level checkbox and select the Formula or Year frequency.

• Running Total. Select the checkbox if the operand must be calculated with running total. In the corresponding drop-down list select the operand, from which running total will be calculated.

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.

8. 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:

• If aggregation is set up for operand, the operand text starts with the ∑ character, for example:

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

• If two elements are aggregated, they are specified via comma. For example:

{ ∑ 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] }

• If the Set Up Element Selection dialog box specifies that all dimension elements are selected for aggregation, the All text is displayed instead of the list of selected elements. For example:

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

• If filtering is set up for aggregated dimension, the operand text for filtered dimension is added with the Filter word, and filtered elements are enclosed in round brackets. For example:

{ ∑ 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:

• If the calendar frequency that is different from formula calendar frequency is selected in the Level drop-down list, it is displayed before the t character separated with a period. For example, semi-annual frequency is selected:

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

• If the specific value is specified in the Date box, it will be displayed at the end of the operand in square brackets. For example, 2018 year is specified:

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

• If the series point, on which operand data is taken, is fixed in the Index box, this point will be displayed at the end of the operand in square brackets. For example, on the third point from the series start:

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

• If the offset that is other than zero is set in the Offset box, its size is specified in square brackets together with [t]. For example, the -1 value is specified in the Offset box:

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

• AND. Default value. Dimension elements will be filtered if all filtering conditions are satisfied.

• OR. Dimension elements will be filtered if one of the filtering conditions is satisfied. Conditions check is executed in serial mode.

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.

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

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

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

• Equal. Use this condition to determine whether the attribute selected in the Attribute column is equal to the value specified in the Value column.

• Not Equal. Use this condition to determine whether the attribute selected in the Attribute column is not equal to the value specified in the Value column.

• Greater Than. Use this condition to determine whether the attribute selected in the Attribute column is greater than the value specified in the Value column.

• Greater Than or Equal. Use this condition to determine whether the attribute selected in the Attribute column is greater than or equal to the value specified in the Value column.

• Less Than. Use this condition to determine whether the attribute selected in the Attribute column is less than the value specified in the Value column.

• Less Than or Equal. Use this condition to determine whether the attribute selected in the Attribute column is less than or equal to the value specified in the Value column.

• Between. Use this condition to determine whether the attribute selected in the Attribute column is in the range of values specified in the Value column. This condition type is available if the attribute of the data type is selected: integer, real, date and time.

• In. Use this condition to determine whether the attribute selected in the Attribute column is included in one of the values specified in the Value column.

• Contains. Use this condition to determine whether the attribute selected in the Attribute column contains the value specified in the Value column.

• Begins With. Use this condition to determine whether the attribute selected in the Attribute column begins with the value specified in the Value column.

• Ends With. Use this condition to determine whether the attribute selected in the Attribute column ends with the value specified in the Value 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.

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

• Value. Attribute value is set directly using a specific value.

• Parameter. Attribute value is set dynamically via a parameter or dimension attribute contained in the parameter. This type of compared value is unavailable if the Between condition type is selected.

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

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

9. 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:

• If the parameter takes a single value, elements with empty values will be excluded from calculation.

• If the parameter takes a multiple value, elements with empty values will be excluded from calculation.

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

10. 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:

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

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

6. 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:

2. Change operand settings.

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