Column aggregation
Last updated
Last updated
In addition to configuring the row aggregation, you can also configure the column aggregation. Like row aggregation, which only applies to sub-total and grand-total rows, column aggregation applies only to the subtotal and grand-total columns.
Let us first demonstrate these aggregations for total rows with a few examples, followed by the 'Manage Aggregation' dialog box where you can perform both row and column aggregations in tandem.
To apply column aggregation, click on the cell in the grand-total/subtotal column and select the aggregation type from the dropdown menu.
The Aggregation drop-down menu gets enabled when you click on any cell that aggregates rows and/or columns.
You can see in the above example the default aggregation is the sum of column values for the Canada region.
Let us now change it to 'Maximum'.
As a result, the grand total is populated with the maximum value of all the quarters.
Note that for the same row, the sub-totals for each quarter are also aggregated in the same way to display the maximum value.
When you have the same category repeated across all levels of the hierarchy, Inforiver will detect repeating categories and allow you to apply the same column aggregation method to all the levels in a single click.
Let us assume we want to change the column aggregation method for the sub-category Amarilla in the Canada region. Click on the grand total cell and select Aggregation. Note that only column aggregation is applicable here as it is a leaf row.
Let us now change it to 'Average'.
When you click the option 'Average (Children)', a pop-up is displayed to confirm whether you want to apply the same aggregation to all the Amarilla rows in all the regions.
Click Yes to update all Amarilla rows with the 'Average' aggregation method.
This ensures that the same aggregation is applied to all the repetitive categories/sub-categories in one go.
Click No in the above confirmation pop-up to apply the aggregation only to that specific row.
Inforiver lets you seamlessly track the various aggregations applied to the rows and columns with the 'Manage Aggregation' dialog box.
In the example below, both row and column aggregations are set to 'Sum' for the Canada region.
Let us now set the column aggregation to 'Maximum' while retaining the row aggregation to 'Sum'. To do this, click the required cell and select Aggregation -> Maximum.
The column total now has the maximum value of the four quarters.
You can track the same and also edit them with the 'Manage Aggregation' dialog box as illustrated below:
P&L or cash flow statements often require the values from the first or last periods as the column subtotals and grand totals. These aggregation types are applicable only at column level.
• First aggregation type: The values from the first period are used for the column subtotals and grand totals. In the example, the Actuals from Q1 are used as the subtotals at year level. The Actuals from 2022 (the earliest year) are used for the grand total.
• Last aggregation type: The last period is used to populate the grand totals and subtotals. The Actuals for Q4 is used to populate the yearly subtotal. The Actuals for 2023 is to populate the grand total.
