Setting Up Calculation Formulas
In this article:
Generating Calculation Formula
Inserting Operand Based on Dimension Selection
Inserting Operand Based on Dimension Attributes
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.
The Set Up Formulas dialog box opens:
To set up a calculation formula:
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 delete a formula, select it and click the button on the formula bar.
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.
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:
Click the dimension name in the breadcrumb. The list of dimension elements opens.
Select the element in the list.
Select the element for each dimension.
Selecting Calendar Frequency
Select the calendar frequency of formula calculation in the Frequency Step list on the calculation options panel.
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.
Operands and functions can be inserted on the side panel. Numbers, comparison characters, and round brackets can be entered from the keyboard.
Operands are generated based on the data from data sources and can be set based on the dimension element selection or dimension attributes.
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
Select the Operands radio button and Value from Source radio button on the side panel.
Select the data source used by the operand in the drop-down list. Tabs with selected data source dimensions will be displayed. For example:
If an operand must execute aggregation, select aggregation method in the Aggregation drop-down list.
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.
Select an element, based on which an operand is generated, 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.
If the operand requires calendar settings that are different from the entire formula, go to the Calendar tab.
In the Offset box set the number of time period points, by which the operand will be offset forward or backward relative to the entire formula.
To change frequency and fix the operand, deselect the Use Formula Calendar Level checkbox:
Determine operand calendar settings:
Select the operand calendar frequency in the Level drop-down list.
If it is required to fix the date, on which operand data will be taken, select the Date checkbox and select the date.
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 dimension selection based operand will be inserted into the formula.
Inserting Operand Based on Dimension Attributes
Select the Operands and Attribute Value radio buttons on the side panel.
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:
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.Select the attribute in the Attributes list, based on which values an operand is generated.
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 dimension attribute based operand will be inserted into the formula.
Inserting Operand Based on Already Existing Operand
Click the operand in the formula area. Operand settings are displayed on the side panel without the ability to change a data source.
Change operand settings.
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:
Select the formula on the side panel.
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.
The function will be inserted into the formula area. Press ALT+ENTER to display a tooltip with function parameters.
Set function parameters.
The function will be inserted into the formula.
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:
Deselect the Use Algorithm Calculation Period checkbox.
In the Start and End boxes select formula calculation start and end period options:
Specific Date. The specific period date specified in the date editor is located to the right.
Practice Calculation End Date. The date is equal to the formula calculation end date plus the number of periods specified in the value editor located to the right.
Formula Calculation Start Date. The date is equal to the formula calculation start date plus the number of periods specified in the value editor located to the right.
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.
See also: