Setting Up Calculation Formulas

Calculation formulas determine how data from sources will be transformed before loading to data consumer.

To set up calculation formulas:

• Select the Formula Editor item in the calculation block's context menu.

• Double-click the calculation block.

NOTE. Formulas can also be set up in the control block.

The Set Up Formulas dialog box opens:

To set up a calculation formula:

1. Create a formula.

2. Select the dimension element, which values are calculated by the formula.

3. Select calculation calendar frequency.

4. Generate calculation formula.

5. Set up additional calculation and data saving parameters.

As a result, the calculation formula is set up. If required, similarly set up other formulas.

The formulas will be calculated in the order they follow on the formula bar. To change formula order, use the and buttons.

To exclude formulas from calculation, select them and select the Exclude from Calculation context menu item. If formulas are grouped in the folder, to exclude all formulas in the folder from calculation, select the same item in the folder context menu. To calculate excluded elements, select the Include to Calculation context menu item.

To delete a formula, select it and click the button on the formula bar.

NOTE. Editing formulas may result in the error related with a Windows system defect.

Creating a Formula

To create a formula, click the button on the formula bar and enter a formula name. A formula will be added to the end of the formulas list.

Rename formula

Add a formula comment

Group formulas

Selecting Calculated Element

A dimension element, which values are calculated by a formula, is selected in the breadcrumb. It contains all non-filtered data consumer dimensions.

To select a dimension element:

1. Click the dimension name in the breadcrumb. The list of dimension elements opens.

2. Select the element in the list.

Select the element for each dimension.

Instead of the element, one of block parameters can be selected for dimension. It enables dynamically change calculated element without formula modification.

To select a parameter specifying dimension value:

1. Click the button after the dimension name.

2. Select parameter in the Set Up Parameters displayed menu.

As a result, dimension value will determine selected parameter.

Selecting Calendar Frequency

Select calendar frequency for formula calculation in the Frequency Step list on the calculation options panel:

• Annual.

• Semi-annual.

• Quarterly.

• Monthly.

• Weekly.

• Daily.

If the Use Algorithm Calculation Period checkbox is selected, formula calculation is calculated at each calendar frequency step within the specified algorithm calculation period. If the checkbox is deselected, frequency step affects the shift of dates specified taking into account the number of periods.

Generating Calculation Formula

A calculation formula is generated in the formula area. A formula may contain operands, functions, arithmetic operators, numbers, comparison characters, and round brackets.

Operand is a data element from data source that is the object of operations during formula calculation. Function is a sequence of operations executed with 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 functions. 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.

1. Select the Operands radio button and Value from Source radio button on the side panel.

2. Select the data source used by the operand in the drop-down list. Tabs with selected data source dimensions will be displayed. For example:

3. If the operand must execute aggregation, select aggregation method in the Aggregation drop-down list.
   Aggregation enables the user to use several element values from 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.

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

5. Select the element, based on which the operand is created, for each dimension. If an operand must execute aggregation, select several elements, by which aggregation will be executed.
   To set up element selection option, click the button on the dimension tab. The Set Up Element Selection dialog box:

Set element selection option:

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

• Selected. The elements selected with the user will be selected. The default option.

• From Parameter. Element value is set by a parameter selected in this dialog box.

6. If calendar settings are required for the operand that are different from the formula, go to the Calendar tab:

NOTE. The tab is absent, if calendar is used as a standard 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. 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 period, from which running total is calculated.
  Calculation of the operand with running total enables the user to take into account previous period data 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.

7. 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 into 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, it is available to set up filtering of aggregated values and aggregate only by the dimension values that correspond to specific conditions.

To set up filtering:

1. Click the operand in the formulas area with the main mouse button.

2. Select the Operands radio button on the side panel and make sure that aggregation method is selected for the operand in the Aggregation drop-down list.

3. Go to the tab of the dimension, for which filtering of aggregated values is set up, and click the button in the tab title. The Advanced Filter dialog box opens:

If filtering has been already set up for the dimension, the button is displayed in the title. Clicking the button opens the Advanced Filter dialog box, in which the current dimension filtering conditions can be viewed and changed.

4. Set conditions for filtering aggregated values. For details about setting conditions, see the Setting Up Filtering by Attribute Values section.

If required, similarly set up filtering for the next dimension. As a result, filtering of operand aggregated values is set up.

Inserting Operand Based on Dimension Attributes

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

1. Select the Operands and Attribute Value radio buttons on the side panel.

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

3. If it is required for the operand to redetermine dimension element selection specified when data source and data consumer were added, select the Redetermine Selection checkbox and specify selection in the Elements list.
   To display a group of elements or alternative hierarchy in the list, use similar commands in the context menu of the list of elements.

4. Select the attribute in the Attributes list, based on which values an operand is generated.

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

A operand based on dimension attributes is inserted into 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.

Inserting Operand Based on Already Existing 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. In the formula area put the cursor to where the operand should be placed, and click the Insert as New button on the side panel.

The operand based on the already existing operand will be inserted into the formula.

Inserting Functions

Select the Functions radio button on the side panel. The list of functions available to be used in the formula is displayed.

To insert a function:

1. Select the formula on the side panel.

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 or double-click the function.

3. The function will be inserted into the formula area. Press ALT+ENTER to display a tooltip with function parameters.

4. Set function parameters.

The function will be inserted into the formula.

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

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

Inserting Additional Calculation and Data Saving Parameters

Click the button on the calculation parameter panel. Additional calculation and data saving parameters will be displayed.

To set a formula calculation period tat is different from the algorithm calculation period:

1. Deselect the Use Algorithm Calculation Period checkbox.

2. Select the Consider Algorithm Calculation Period checkbox if required. The checkbox is deselected by default, a formula is calculated only by the specified period. When the checkbox is selected, a formula is calculated taking into account the intersection between formula calculation and algorithm calculation period.

NOTE. The checkbox can be used only if the Use Algorithm Calculation Period checkbox is deselected.

3. Select the method for setting formula calculation period start and end dates in the Start and the End boxes:

• Specific Date. Set the specific date in the date editor on the right. Date format in the date editor depends on the specified frequency step.

• Algorithm Calculation Start Date. Set the number of periods in the value editor on the right to shift the algorithm calculation start date. The algorithm calculation start date matches the specified calculation period start date on the Manage Period Control tab in the Calculation Algorithm Parameters or on the parameters panel. When calculating a formula, use algorithm calculation start date taking into account the shift by the specified number of periods.

• Algorithm Calculation End Date. Set the number of periods in the value editor to the right to shift algorithm calculation end date. The algorithm calculation end date matches with the specified algorithm calculation end date on the Manage Calculation Period in the Calculation Algorithm Parameters dialog box or on the parameters panel. A formula is calculated using the calculation algorithm end date taking into account the shift by the specified number of periods.

• Formula Calculation Start Date. Set the number of periods in the value editor to the right to shift formula calculation start date. The formula calculation start date matches with the specified calculation period start date on the Manage Calculation Period tab in the Calculation Algorithm Parameters dialog box or on the parameters panel. A formula is calculated using the formula calculation start date taking into account the shift by the specified number of periods.

• Formula Calculation End Date. Set the number of periods in the value editor on the right to shift the formula calculation end date. The formula calculation end date matches with the specified calculation period end date on the Manage Calculation Period tab in the Calculation Algorithm Parameters dialog box or on the parameters panel. A formula is calculated using the formula calculation end date taking into account the shift by the specified number of periods.

• Date from Parameter Value. Select the dictionary attribute, on which the parameter is based, and set the number of periods in the value editor on the right to shift the date from parameter value. The attribute value is used as a formula calculation date. When calculating a formula, use the date from parameter value taking into account the shift by the specified number of periods.

NOTE. When selecting a dictionary attribute, the drop-down list contains all dictionary attributes, except for the Name and the Identifier attributes.

• Current Coordinate Date. Select the cube dictionary, on which the calculation block data consumer is based, and set the number of periods in the value editor to the right to shift the date from the current calculated dictionary element value. The dictionary attribute value by the current calculated element is used as a formula calculation date. A formula is calculated using the attribute value by the current calculated element of the specified dictionary taking into account the shift by the specified number of periods.

The specified attributes for date shift depend on the frequency step and affect formula calculation results. For example, if the annual frequency step is set in the calculation formula with the algorithm calculation start date (from 01.01.2020) and the number of periods equal to 2, formula calculation starts from 01.01.2022. If the monthly frequency step is set in the calculation formula with the algorithm calculation start date (from 01.01.2020) and the number of periods equal to 2, formula calculation starts from 01.03.2022.

To set the period for saving calculation results:

1. Select the Set Period for Saving Calculation Results checkbox.

2. Select the method for setting calculation result saving period start and end in the Start box and the End box. Date setting methods are identical to those for setting formula calculation period start and end.

After executing the operations, the period for saving calculation results is set. Calculation results are saved only in the specified date range after algorithm calculation is completed. If a period for saving calculation results is not set, calculation results are saved from the algorithm calculation start date to the end date.

To save only non-zero values obtained after block calculation, deselect the Save Zeros checkbox.

To use empty values of operands used in formula on calculation, deselect the Replace Empty Operand Values with Zeros checkbox.

To select formula calculation method in the block, use the Pointwise Calculation checkbox. The checkbox is deselected by default, and series calculation is used, that is, all formulas are calculated at the same time by all data points. To consequently calculate formulas in each data point (first, calculate formulas in the first data point, then in the second one, and so on), select the Pointwise Calculation checkbox.

To enable the ability to show various formulas that will work on specific time periods, select the Set Several Calculation Periods checkbox. In this case, the formula area shows buttons to create and set up additional periods. Each period can have its own formula, single periods are displayed on single tabs: .

Tab headers show abbreviations: Cs - start of calculation period specified for algorithm or in calculation block settings; Ce - end of calculation period specified for algorithm or in calculation block settings.

See also:

Inserting and Setting Up Calculation Blocks