Inserting Operands

An operand is a data element, with which operations are executed during expression calculation. In the expression editor operands are inserted by means of data panel and displayed in expressions in curved brackets.

Available types of operands:

• Operands Based on Dimension Selection. It is applied, if data value obtained by specified selection of dimension elements must be used in the expression.

• Operands Based on Time Series. It is available only in the Time Series Analysis tool. It is applied if time series must be used in the expression.

• Operands Based on Metadata. They are divided into:

• Operands Based on Dimension Attribute. It is applied if value of the specified attribute obtained by specified dimension selection must be used in the expression.

• Operands Based on the Dimension Element. It is applied if value of the selected dimension element must be used in the expression.

After operands are inserted, the following is available:

• Setting up calendar parameters of operands. It is also recommended to see calendar features of operands.

• Editing operands.

• Viewing a formula of calculated dimension element.

• Viewing selection. The option is available only in the desktop application. It enables the user to display selection made on inserting the operand on the data panel. To do this, select the Show Element in the List item in the operand's context menu.

Show and hide data panel

Inserting Operand Based on Dimension Selection

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

To insert operand:

1. Select the Selection radio button on the data panel. All data source dimensions will be displayed. By default, the dimension, for which an expression is created, is displayed first. Elements of this dimension are displayed as a list, elements of other dimensions are displayed as drop-down lists. For example:

TIP. If it is required to see elements of other dimensions as a list and not as a drop-down list, click the Actions button to the left of the dimension name. Select the Maximize item in the menu that opens.

2. The current data source, by dimension selection of which an operand is created, is displayed below the Selection radio button. Make sure that the necessary data source is used. To replace data source:

• With the previously used data source:

1. Click the name of the current data source. A list of all data sources previously used is displayed.

2. Select one of the data sources in the list.

• With a new data source:

1. Click the Data Source button. A list of all data sources available in the current repository is displayed.

2. Select one of the data sources in the list or use the list to search for the required source.

To cancel selection of a new data source, click again the Data Sources button.

3. Select the element in the list of elements of the dimension, for which the expression is created. It will be the main for the operand. Only one element can be selected.

TIP. To change displayed names of dimension elements, click the Actions button to the left of the dimension name. In the menu that opens select the Name item and select the attributes that will be used to create element names. The names will be changed on the data panel and in the expression.

4. By default, selection by other dimensions is not set, and the operand is calculated by all elements of these dimensions. If it is required to limit the number of elements, set selection by dimensions. Only one element can be selected.

NOTE. It is mandatory to set selection by all dimensions, except the calendar one, in the Time Series Analysis and Modeling Forecasting tools.

5. Aggregation can be set up in the desktop application for any dimension, except for the calendar one. It enables the use in the expression of not one but several dimension elements aggregated by the selected method. For details see the Setting Up Aggregation Operand by Dimension article.

6. Click the Insert into Formula button or double-click the selected element, for which an expression is created, or drag it into the expression area.

As a result, the operand based on dimension selection is inserted into the expression to the cursor position, for example:

{Commercial expenses|Hydraulic equipment manufacturers association[t]}

The operand text contains elements selected in dimensions and separated with a vertical line. If selection for the dimension is not set, it is not displayed in the operand text.

[t] at the end of the operand means full selection by calendar dimension. If the specific value is selected for calendar, it will be displayed at the end of the operand in square brackets, for example:

{Commercial expenses|Hydraulic equipment manufacturers association[2018]}

If aggregation of dimension elements was set up, the operand text will contain name of this operand with the ∑ character, for example:

{Commercial expenses|∑Accounting period[t]}

If a calculated element is created or edited in calendar dimension, then X substitution can used as operand based on dimension element. On calculating expression, this substitution corresponds to the dimension element obtained by the current selection of all dimensions except calendar one. If the whole selection by calendar dimension is required, put [t]at the end of operand, for example:

{X[t]}

If a specific value is required for calendar dimension, specify it at the end of operand in squared brackets, for example:

{X[2018]}

Example with the use of X substitution

TIP. To insert the operand used in the expression again, press CTRL+SPACE. The list of available operands and functions is displayed. Operands are located at the beginning of the list and are marked with the icon. Click the operand in the list to insert it into the expression into the cursor position. The option is available only in the desktop application.

Example of Inserting Operand Based on Dimension Selection

The example displays that a data source is changed, and an operand is inserted based on its dimension selection:

Inserting Operands Based on Time Series

The Time Series Analysis tool enables the use of operands based on the current workbook time series. In fact, an operand based on a time series is an operand with selection by all possible dimensions. Operand calculation results in time series.

To insert operand:

1. Select the Metadata radio button on the data panel. The upper part of the panel before the Sources list displays all series of the current workbook, for example:

2. Select one series.

3. Click the Insert into Formula button or double-click the selected series or drag it to the expression area.

As a result, the operand based on a time series is inserted into the expression to the cursor position, for example:

{Dollar rate (annual average), roubles per US dollar|A[t]}

[t] at the end of the operand means full selection by calendar dimension.

The example of inserting an operand based on a time series is given in the Example of Inserting Functions and Operators into Expression section.

Inserting Metadata-Based Operands

Metadata-based operands are operands that are based on a dimension attribute and operands that are based on a dimension element. These types of operands are most often used together to create conditions in expressions.

NOTE. Metadata-based operands can be inserted in all expressions, except for MDX expression for ADOMD cube dimension and expressions in the Modeling and Forecasting tool.

Select the Metadata radio button on the data panel to display metadata. The hierarchical list of all data source dimensions is displayed. A list of attributes and a list of elements are displayed for each dimension. For example:

If an expression is created in the Time Aeries Analysis tool, attributes will be divided into observation attributes and series attributes.

TIP. To change displayed metadata names, click the button and select one of the name display options. Names will be changed only on the data panel.

Inserting Operand Based on Dimension Attribute

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

To insert operand:

1. Insert the operand based on dimension selection. Basing this operand dimension attribute-based operand value will be calculated.

2. Select the Metadata radio button on the data panel.

3. Select one dimension attribute.

4. Click the Insert into Formula button or double-click the selected attribute or drag the attribute to the expression area.

As a result, the operand based on dimension attribute is inserted into the expression to the cursor position, for example:

{Commercial expenses.TERRITORIES.NAME[t]}

[t] at the end of the operand means full selection by calendar dimension. If the specific value is selected for calendar, it will be displayed at the end of the operand in square brackets, for example:

{Commercial expenses.TERRITORIES.NAME[2018]}

The operand text contains dimension name and the selected attribute separated with a dot.

Inserting Operand Based on Dimension Element

Operand calculation results in the value of the selected dimension element.

To insert operand:

1. Select the Metadata radio button on the data panel.

2. Select one dimension element.

3. Click the Insert into Formula button or double-click the selected element or drag metadata into the expression area.

As a result, the operand based on dimension element is inserted into the expression to the cursor position, for example:

Gross profit (loss)

Operand text matches the name of the selected element and is displayed without curved brackets. If the element value is text, quote it to correctly use in the expression, for example:

"Gross profit (loss)"

Example of Inserting Metadata-Based Operands

The example displays creating an expression by means of an operand based on a dimension attribute and an operand based on a dimension element. If attribute value matches the element value, the expression calculates percentage value of the element: