AX1075
You can configure an Axiom query so that it returns only the top N records for the query—such as top 5, top 10, or top 50. For example, you may want to see the top 10 departments based on revenue in Q1.
In order to return the top N records based on a particular criteria, you must:
- Enter the number of records that you want returned into the field Limit query to top "n" results. This setting is only located on the Control Sheet, in the Query Details section for the Axiom query.
-
Set Sort by database columns to the column(s) that you want to use to determine the top results. This setting can be defined using either the Control Sheet or the Sheet Assistant; in the Sheet Assistant the setting is called Data Sort.)
When the Axiom query is run, it will return the first N records (in this example, 10) in the query. This means that the sort order is very important to determine which records you get. Remember that by default the sort is in ascending order. So if you specify CYA_Q1 as the sort column but do not specify an order, you will get the 10 smallest records in that column, not the 10 largest records. If you want the 10 largest records, you must specify "desc" for descending order.
If you want the results in the sheet to display sorted in a different order than the order used to specify the top N results, then you can also specify a spreadsheet sort (Sort results by these columns). This will sort the data after it has been inserted into the sheet, by the specified spreadsheet column(s).
NOTE: If the top N setting is used to limit the query, the Max row warning threshold is ignored.
Top N example
The following screenshot shows how the query might be configured to return the top 10 departments based on revenue in Q1:
- The "sum by" level is set to
dept.deptto return department-level data. - The database sort is set to sort the data by
CYA_Q1in descending order. - The data filter is set to limit the data to
acct.category='revenue'. - The top N limit is set to return the top
10records for the query.
The following screenshot shows how the results of the Axiom query might look in the sheet:
In this example, the results are sorted by the database sort level, in CYA_Q1 descending order. If you wanted the results to display in the sheet sorted by department number, then you could set Sort results by these columns to column D.
AX1075
