How to Configure Filtering and Sorting in Calculations

This article describes how you can use the row and column filter and sort functionality in tables created with Calculations. This works in both Edit mode and View mode. It's common to sort or filter tables to find interesting results and work out how to clean or explain the data. While sorting and filtering make sense with larger tables, when tables are small with just a handful of columns or rows, showing the sorting and filtering controls on the table may be less relevant and can confuse some viewers.  For example, it is unlikely that anyone would want to sort or filter this table:   

In contrast, it would make sense to sort this table, as it allows you to highlight findings like which cola is the most health-conscious, innocent, traditional, etc. 

Of course, there won't be a one-size-fits-all cutoff for how large or small a Calculation should be to allow sorting and/or filtering. Here's how you can configure filtering and sorting in Calculations.

Requirements

• A table created with a Calculation in either Edit or View mode.
  • Note: Changing the cutoffs and default settings can only be done in Edit mode.

Method

Displaying the Sort and Filter Controls

To display the sort and filter controls (if available), hover your mouse over the desired row or column label.  In this example, we hover over the Health-conscious column label:


In this example, both filter and sort controls are displayed. If one or both of the controls do not appear, it is because the column/row count is beneath the default threshold. More on that below.

Changing the Default Settings

By default, the settings are as follows:

• Minimum rows for column sorts = 10
• Minimum rows for column filters = 100
• Minimum columns for row sorts = 10
• Minimum columns for row filters = 100

The defaults can be viewed and changed in the object inspector by selecting Data > Row/Column Sorting/Filtering

The settings allow you to customize when the filter and sort controls appear using a threshold you set. If the default thresholds don't meet your needs, you can change them, either for a specific Calculation or the entire document. 

In the example below, we want to sort the table using the "Traditional" column. However, the sorting control isn't available because the default minimum rows for sorting the table by column 10. In order to sort, we first need to change this setting from 10 rows to, at most, 7 rows, as the table only has 7 rows.  

To do so, do the following:

1. In the object inspector , select Data > Row/Column Sorting/Filtering.

2. Change the value in the Minimum rows for column sorts value to 7.

3. To sort, hover to the left of the column header and select either ascending or descending order. In this example, we chose descending order. Note that the filter control still does not appear on the right because the number of rows is well below the minimum of 100.
 
The results are as follows:

Changing Document Default Settings

You can use the Set as Default and Reset buttons in the Data > Row/Column Sorting/Filtering sub-menu to customize the document-level default settings for the four sorting and filtering thresholds:

• You can set the thresholds of a particular table as the document-level default by customizing that table and clicking Set as Default. 
• You can reset the thresholds of a particular table back to the document-level default by clicking Reset. Please note that if you've used Set as Default to set a new document-level default, the table you reset will revert to the custom-set default. 

Defaults for Existing Dashboards

Additional information

For existing Calculations created before January 17, 2024, they will default sort/filter to be always on. In other words, their threshold values will be set to the following:

• Minimum rows for column sorts: 0
• Minimum rows for column filters: 0
• Minimum columns for row sorts: 0
• Minimum columns for row filters: 0

This ensures the sorting/filtering experience in existing dashboards is not affected.

Technical Details

Please note there are some compatibility differences between Q and Displayr if using this feature and a version of Q earlier than version 5.17. For more information, see Compatibility Between Displayr and Q Versions.

Next

Improvements to Tables Created by Calculations - What’s Changed and Why