Aggregation (by Series): Microsoft Excel Add-In

The method aggregates data by attribute values of time series. The method is included into the Aggregation group.

Select one or several series in the data table.
Click the Aggregation button on the Calculations ribbon tab.
Select the Aggregation option in the drop-down list of the button.

After the method is applied, the Aggregation dialog box used to set up calculation options opens:

To specify the aggregation calculation period use the Calculation Period parameters group:

Calculation Start. The method for indicating the start point of the calculation:

Specific Date. The date is specified in the date editor located at the right.
Today +/-. The date is equal to the current date plus the number of periods specified in the value editor at the right.
From Calculation End. The start date of the calculation is equal to the end date minus the number of periods specified in the value editor displayed at the right.

The Specific Date method is used by default, and start point of the calculation matches the start date of data display.

Calculation End. Method for indicating the end point of the calculation:

Specific Date. The date is specified in the date editor located at the right.
Today +/-. The date is equal to the current date plus the number of periods specified in the value editor at the right.
From Calculation Start. The end date of calculation is equal to the starting date less the number of periods specified in the value editor displayed at the right.

The Specific Date method is used by default, and the end point of the calculation matches the end date of data display.

NOTE. If the From Calculation Start calculation end option is set, it is not available to set the From Calculation End calculation start option.

To set aggregation expression, use the Expression group of parameters:

Dimension. The dimension, in which the aggregation will be executed. The drop-down list includes all dimensions of the time series database, except for mnemonics, revisions, measurement units, and calendar dimensions.
Value. Aggregation expression. All available aggregation expressions are displayed in the list that opens on using the IntelliSense technology:

Put the cursor to the Value area.
Press CTRL+SPACE.

The list of available aggregation expressions will be shown.

Attribute names in aggregation expressions are braced. For example: {BCI}+{BCA}.

Filter. Constraints on the time series, for which aggregation is to be executed. Working with the field is identical to working with breadcrumb on the Series Tree panel.
Frequency. Calendar frequency for calculating aggregation.

To set up aggregation calculation method, use the following parameters:

Method. Select an aggregation method in the drop-down list.
Weights. Specify an expression to calculate aggregation weights. The drop-down list is available when a method of weighted aggregation (for example, weighted sum) is selected. The list contains elements of the dimension selected in the Dimension box.
Ignore Series with Empty Weights. Selecting the checkbox defines whether the series with empty weights are to be ignored. If these series are ignored, the aggregation calculation takes less time. The checkbox is deselected by default, if a weighted aggregation method is used (for example, weighted sum).
Level. Selecting the checkbox determines whether to use the Level function that smoothes obtained data after the aggregation is calculated.

The checkbox is available when the following methods or their weighted analogs are selected:

Weights.
Weighted Geometric Mean.
Number of Observations.
Amount of Missing Data.
Sum.
Average.
Median.
Mode.
Percentile.
Composition relevance.

The checkbox is selected automatically and dimmed when the following methods or their weighted analogs are selected:

The average calculated based on growth rate to corresponding period.
The average calculated on percentage change.
The average calculated based on logarithms difference.
The average calculated based on logarithms difference to corresponding period.
The average calculated based on growth rate to corresponding period.
The average calculated on percentage change.
Median calculated on basis of percentage change.

To apply the Rebase function allowing to rebase data from one frequency to the other one, select the Rebase checkbox.

NOTE. The Rebase checkbox is available when the Level checkbox is available.

Set additional rebase parameters:

Frequency. Calendar frequency to calculate the Rebase function. It can be changed if aggregation frequency is different than the annual one. The Rebase function can be applied to annual or lower frequencies, but the frequency cannot be less than the aggregation calculation frequency. For example, if aggregation calculation frequency is Quarterly, the Frequency drop-down list will contain only the Annual, Semi-Annual and Quarterly options.
Basic Period. It determines the period to calculate the Rebase function. The format of period definition depends on the specified frequency. For example, if annual frequency is selected, only year can be specified; if the semi-annual frequency is selected, it is required to set half-year period and year, and so on. If the Frequency box in the Rebase group is not available, the period format depends on the Frequency box in the Expression group.

To set aggregation relevance with taking into account missing data, use the Composition Relevance group of parameters:

Threshold. It determines threshold value for calculation of aggregation relevance. Available values range: [0; 100]. If the threshold is 0, aggregation is calculated ignoring missing data. If the threshold value is 100, every missing data will be taken into account when calculating aggregation. If the value is greater than 0 but less than 100, the missing data is taken into account in accordance with selected threshold value.
Comparator. The comparator for estimating aggregation relevance. It contains the list of elements for the dimension selected in the Dimension box on setting up aggregation expression.

Relevance is calculated as follows: if the sum of values of the Comparator element, divided by the sum of all values is less than the Threshold value, aggregation will not be calculated.

Total period is a period, at which all aggregated series have data.

To calculate aggregation in the total period of data, select the Use of Total Period checkbox and set the parameter:

Minimum Number of Observations. The checkbox determines whether the minimum number of observations is limited in the total period. The checkbox is deselected by default and constraints are not imposed. If the checkbox is selected, set the minimum number of observations in the corresponding value editor. The total period must contain, by default, at least three observations.

The Use of Total Period checkbox is deselected by default, and aggregation is calculated on the model period.

To calculate aggregation considering data hierarchy, select the Hierarchy Consideration checkbox and set the following parameters:

Entire Hierarchy. Default. Hierarchy is calculated by all hierarchy levels.
First Level Only. Aggregation is calculated only one level down the hierarchy.
Consider Source Element. The checkbox is deselected by default, and the source element is not considered in aggregation. If the checkbox is selected, the source element is considered in aggregation.

NOTE. All hierarchical dimensions are used on calculating the aggregation considering hierarchy.

The Hierarchy Consideration checkbox is deselected by default, and calculation is executed without considering hierarchy.

If a formula is set up correctly, clicking the OK button closes the Aggregation dialog box. A calculated series named Aggregation and containing the calculation results is added to the data table.

Aggregation (by Series)

Setting Up Calculation Options