Sorting by Aggregates

You may want to sort groups by the summary, or aggregate, calculation of each group. You can do this using Top/Bottom filters, which are available in version 2017.1.

Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. You can supply any arbitrary cell in the report in the Top/Bottom filter. So if you set the number of displayed values to "infinity", then no values are excluded, but the ordering still takes effect.

To sort by a summary calculation:

  1. Ensure that your report has the appropriate aggregate formula in a group footer cell. The cell should return a numeric value in the report output, which you want to sort the groups by.

  2. From the ReportOptions.png Report Options menu, click Filter.png Filters. Then click the Top/Bottom tab.

  3. Select the Limit the report to the top or bottom values of a defined dataset check box.

  4. From the Top/Bottom list, select Bottom to sort in ascending order, or Top to sort in descending order.

  5. In the # field, type 2147483647.

    Why this number? We cannot enter "infinity", so instead we want to enter an arbitrarily large number like 99999. This is the largest number that can fit without causing a report error.

  6. From the Value list, select the group footer cell with the aggregate formula.

  7. If there is a For Each group, click DropdownDelete.png to remove it.

Keep in mind that this is not a Sort from the sort menu. You cannot use this to 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.

Hidden Article Information

Article Author
Exago Development
created 2017-05-17 17:21:22 UTC
updated 2019-05-16 20:29:55 UTC

formulas, Group Header, Group Footer, aggregate, sort, group, sorting, grouping, summary, aggregates, summaries, sort on aggregates,
Have more questions? Submit a request