Aggregation Settings
Basic overview
'Aggregation' is the process of reducing a data set's complexity by summarizing multiple values.
This typically happens when viewing a data set with multiple dimensions in a chart that displays the data using only a subset of the available dimensions.
In Valsight, you have the option to aggregate your data based on a number of options.
The aggregation type of a node defines how the data is summarized and shown.
The setting to define which aggregation type is applied can be found in the modelling section, in the node editor under "Show Aggregation Settings".
There are two aggregation setting types that influence the aggregation of your data ( 1. Display Aggregation Type, 2. Calculation Aggregation Type).
The effects of those two settings are visible when for example looking at the data in a tree view, as there the data is often shown on a peryear level. It also affects how the data is "Rolled up", if the ROLLUP or DROPLEVEL function is used.
Aggregation Types
Among the standard options you will find:
Aggregation Type 
Notes 

SUM (default) 
Sum of all values. 
AVG 
The average value. To calculate weighted averages, see below. 
MIN / MAX 
The minimal/maximal value (on that specific aggregation level), e.g. can be a minvalue per year & product 
CLOSINGSUM (Closing DateSum) 
The sum of the values at the end of a period/dimension (e.g. sum of all Q4 revenue made from 20202022). 
CLOSINGAVG (Closing DateAverage) 
The average value of all values at the end of a period/dimension (e.g. average of all Q4 revenue made from 20202022). 
OPENINGSUM (Opening DateSum) 
The sum of the values at the beginning of a period/dimension (e.g. sum of all Q1 revenue made from 20202022). 
OPENINGAVG (Opening DateAverage) 
The average value of all values at the beginning of a period/dimension (e.g. average of all Q1 revenue made from 20202022). 
NONE 
The node will not be shown aggregated. 
Weighted Average by 
You can specify a node that is used for weighting, e.g. use volume to weigh the price to get an average price of products sold. See details below. 
LAST 
The last value from data will be shown, e.g. in a year dimension with data uploaded from 202401 till 202412, the LAST aggregation will take the value from 202412. If there is only data entered until 202405, then the LAST aggregation uses this data point. The value '0' counts as a data point. 
FIRST 
The first existing value from data will be shown. The value '0' counts as a data point. 
Examples
To explain each aggregation type, imagine the following data set regarding Revenue by country and year:
Country 
Year 
Revenue 

DE 
2023 
100 
US 
2023 
200 
DE 
2024 
150 
US 
2024 
250 
SUM, AVERAGE, MIN, MAX
Using these four different types, the aggregation results would look like this:
Year 
SUM 
AVG 
MIN 
MAX 

2023 
300 
150 
100 
200 
2024 
400 
200 
150 
250 
CLOSINGSUM, CLOSINGAVG, OPENINGSUM, OPENINGAVG, LAST and FIRST
The aggregation functions CLOSINGSUM and CLOSINGAVG are like the traditional SUM and AVG with the exception of how the dimension is aggregated.
To outline the mechanics, let's consider the example from above for 2023 with quarters.
Country 
Quarter 
Year 
Revenue 

DE 
Q1 
2023 
15 
DE 
Q2 
2023 
15 
DE 
Q3 
2023 
20 
DE 
Q4 
2023 
50 
US 
Q1 
2023 
30 
US 
Q2 
2023 
30 
US 
Q3 
2023 
40 
US 
Q4 
2023 
100 
When aggregating the data on year using CLOSINGSUM or CLOSINGAVG, the aggregation will take the CLOSING value of the period (which is Q4) and calculate the SUM or AVG over all countries.
Year 
CLOSINGSUM 
CLOSINGAVG 

2023 
150 
75 
When there is no closing value (e.g. the last month in a year) there will be no value for that period.
When aggregating the data on year using OPENINGSUM or OPENINGAVG, the aggregation will take the opening value of the period (which is Q1) and calculate the SUM or AVG over all countries.
Year 
OPENINGSUM 
OPENINGAVG 

2023 
45 
22,5 
When there is no opening value (e.g. the first month in a year) there will be no value for that period.
When aggregating the data on country and year using the LAST aggregation, the data will show the last value of DE in 2023 and the last value of US in 2023.
Year 
Country 
LAST 

2023 
DE 
50 
2023 
US 
100 
When aggregating the data on country and year using the FIRST aggregation, the data will show the first value of DE in 2023 and the first value of US in 2023.
Year 
Country 
FIRST 

2023 
DE 
15 
2023 
US 
30 
Weighted Average by
The standard aggregation types only use the data of the node itself. However, typical drivers are cost ratios, prices, etc that must be weighted by a volume to make sense on an aggregated level. The weighted average aggregation setting allows you to define such a node. When using the weighted average, keep the following in mind:

The weighting node needs to have at least the levels of the node.

The weighted average is not supported for nodes using the RATIO function.

The average is calculated as 'Node' * 'Weight' / 'Weight'. When using the node in a chart, the aggregation to the requested chart dimensionality happens individually for the numerator ( 'Node' * 'Weight') and the denominator ('Weight') before the division and always using SUM as aggregation method (independent of the aggregation settings of 'Node' and 'Weight').
Two Types of Aggregation Settings
In the aggregation settings, users can choose between "Display Aggregation Type" and "Calculation Aggregation Type".
Display Aggregation Type
The display aggregation settings relate to the numbers displayed in the simulation workspace (e.g tree charts or tables).
The setting helps to individually configure charts based on relevant numbers and dependent on the use case.
Example:
Company A is selling 3 sorts of apples and needs information about the average price of an apple over all sorts sold for the year 2020.
Aggregating the data based on 'SUM' or 'AVERAGE' would not help company A as neither setting provides relevant or useful information for the request.
Therefore, the 'Display Aggregation'  setting is used. Herewith, the company can display the needed information in the workspace (e.g. for a presentation) without directly changing the calculation of the model.
Calculation Aggregation Type
The calculation aggregation settings are purely used for the background calculation of data in the model.
Changes can only be set when the display aggregation setting is set to 'None' or 'Weighted Average by'.
The settings of this option are not shown in the simulation workspace.