In this article we will have an overview on how to add and edit the aggregation values.
Click on GroupBy (1) and drag the header of the column you would like to group by. You can also drag additional columns to create subgroups. NOTE: To undo the GroupBy, just click on the column header and drag upwards until you see the ‘X’ next to the mouse, then drop it. For this example, we are grouping by Account Code, with a subgroup of Price Currency (2).
Once you have grouped, click on Add/Edit Views (3) and open the ‘Aggregation’ tab (4). Make sure that the selected view (5) is the one you are seeing in your report.
On Selected columns (6), you will find any available column in your report. On the right, you will see a first column for the grand total (7) – the first row of your report (8)-, followed on the right by another column with the main group we selected on step 1 -in this case, Account Code-, and a column per subgroup -in this example there is just a column for the only subgroup we added, Price Currency-.
Based on the aggregates you choose for each selected column (9), different information will be displayed in the given grouped rows.
For instance, let’s set up the aggregations for the column ‘MV-Current (Base)’.
In the Grand Total field, we choose Sum. In the Account Code field, we will select Min. Finally, in the Price Currency field, we use Average.
*In the end of this article you will find a list of the available aggregations and their meaning.
Once we click OK, the report will show in ‘MV-Current (Base)’ column:
- The sum of all the MV-Current (Base) values in the Grand Total row.
- The minimum value within each Account Code on each account code row.
- The average value within each Price Currency subgroup.
Here the final result:
ANOTHER WAY OF DOING THIS
Click on the name of the column, and drag it to the GroupBy section. You can also do the opposite operation. Drag it from the GroupBy to the Column header. In the next example, we use test column. As soon as we drop this column on the GroupBy, it will disappear from the Column Header.
IF YOU WANT TO CHANGE THE VALUE THAT THE COLUMN WILL DISPLAY
Go to Add/Edit views.Click on Aggregation and select a column.The column does not need to be added to the Aggregation Hierarchy.
The value that the column will display can be changed.
If the value under “Grand Total”, is changed, then it will affect all the columns to the right and they will all show the same value.
If the value under “Fund” is changed, it will also change all the values to the right, but it will not affect the values on the left, and so on with every column.
So every column can have its own aggregation rules.
“The Direction”, ascending or descending, and the “by”, for example: “Fund”, can also be changed under sort.
RULES CAN ALSO BE SET UP AUTOMATICALLY
Go to “Configuration”. Click on “Applications”, a window will appear.
SPARK AGGREGATES AND DEFINITIONS
SUM: sum all underlying numeric cells.
UNIQUE: show the value of the underlying cells ONLY if they are all the same, nothing otherwise.
AVERAGE / AVERAGE2: average all underlying numeric cells, skipping empty (null) cells.
MIN: show the minimum value of the underlying cells.
MAX: show the minimum value of the underlying cells.
COUNT: show the number of underlying cells.
WTDAVGx: show the weighted average between the cells of the column and the column defined as WtgAvgx in the report’s Add/Edit Columns page.
SUMRATIOS: column formula must be in the form [[COLUMN_A]] / [[COLUMN_B]]. It shows the SUM of [[COLUMN_A]] divided by the SUM of [[COLUMN_B]].
AVG2RATIOS: column formula must be in the form [[COLUMN_A]] / [[COLUMN_B]]. It shows the AVERAGE2 of [[COLUMN_A]] divided by the AVERAGE2 of [[COLUMN_B]].
SUMWEIGHT: column formula must be in the form [[COLUMN_A]] / AUM_FUNC([[COLUMN_C]]) or [[COLUMN_A]] / AUM_RT_FUNC([[COLUMN_B]], [[COLUMN_C]]). It shows the SUM of [[COLUMN_A]] divided by AUM_FUNC([[COLUMN_C]]) or AUM_RT_FUNC([[COLUMN_B]], [[COLUMN_C]]) in all aggregation levels except the Grand Total, where it shows the SUM of [[COLUMN_A]] divided by AUM_FUNC(TOTAL) or AUM_RT_FUNC([[COLUMN_B]], TOTAL).
AVGSUM: average all underlying numeric cells.
SUMDISTINCT: show the sum of all distinct underlying numeric cells.
HARMEAN: harmonic mean of all underlying numeric cells.
WTDHARMEANx: show the weighted harmonic mean of the column and the column defined as WtgAvgx in the report’s Add/Edit Columns page.