Sorting by Aggregates

This document covers several versions of the application. Use the Viewing content for dropdown to see only the content relevant to a specific application version.

To sort groups by the summary, or aggregate, calculation of each group of an Advanced Report, use Top/Bottom filters.

Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. Any arbitrary cell in the report can be the Top/Bottom filter.

To sort by a summary calculation:

1. Ensure that your report has the appropriate aggregate formula in a group footer or header cell. The cell should return a numeric value in the report output, which you want to sort the groups by.
2. Open the Filters dialog then, click the Top/Bottom tab. To open the dialog:
   • (v2021.1+) Click the Filters  icon on the toolbar
3. Check the Limit the report to the top/bottom values check box.
4. From the Top/Bottom list:
   • select Bottom to sort in ascending order, or
   • select Top to sort in descending order.
5. Check the All checkbox to sort all of the section values by the Top N criteria.
6. From the Value list, select the group footer or header cell with the aggregate formula.
7. If there is a For Each group, click the Delete icon to remove it.

Keep in mind that this is not a Sort from the sort menu. It cannot make nested groups. This only affects the order in which a group of data is shown in the output. Because this is technically a Filter, this has precedence over the report sorts.