How to Sort a Table by Average or Row Sample Size

Sometimes you may want to see the breakdown of different groups for a question, as well as the Average or Row Sample Size information. A useful way to present a table like this is to sort by Statistics > Right so that categories with more data are at the top, or the user can easily see the best/worst average categories while also seeing their proportional breakdown. The example below looks at the percent of Detractors, Passives, and Promoters for each brand in the row and orders the table by the brand-level NPS (the Average on the right). This article describes how to take a standard table that's not sorted in any particular way: 

And sort it by a statistic in the right column, such as Average or Row Sample Size: 

Requirements

• A native drag and drop table that has a statistic from Statistics - Right applied, such as Average or Row Sample Size.

Method 1 - Use the Select Matching Rows rule

You will create a separate table showing only the statistic to the right that you want to sort by and sort that table. Then, use the rule to link the order of that table with your original table. 

1. Create a new table that shows the statistic to sort by in the Statistics > Cells of the table, such as Average in this example. You may need to make a copy of the variable set you are using and change the Structure to Numeric or Numeric-Multi in order to show the Average in a separate table.
2. From the object inspector of the table, use the Data > Rules > Sort/Reorder > Sort Rows (or Columns if need be) rule to sort the table in the order you wish. This example uses the Average table below:
   
3. Select the original table and from the object inspector, select the Data > Rules > Rows/Columns > Select Matching Rows rule. 
4. In the Select rows using dropdown select the table that is sorted the way you wish, (the table from step 2 above).
5. Set Use selected input's > rows in order to use the rows of the new table for matching instead of the columns.
6. Set Order rows based on > selected input in order to use the order of the new table instead of retaining the order of the original table.
7. Click OK to sort your original table in the order needed.

Method 2 - Use a Custom rule

You can create a custom rule to bi-pass creating a secondary reference table. To do so:

1. Select the table you want to apply the rule to.
2. In the object inspector, go to the Data tab. 
3. In the Rules section, click + > New Custom Rule (Write Your Own JavaScript). 
4. Click the Edit JavaScript button. 
5. Copy and paste the following into the code editor and edit the INPUTS section as desired: 

form.setSummary("Sort by Statistic to Right or Below");
form.setHeading("Sort by Statistic to Right or Below");


/// INPUTS
var stat_to_sort_by = "Average"; //name of the statistic to sort the table by
var use_stats_right = true; // if false will use statistics below
var ascending_order = true; // if false will sort descending
///

if (use_stats_right & table.numberRows > 1) {
    let values = right_table.get(stat_to_sort_by);
    let sort_objects = table.rowLabels.map(function (label, ind) {
        if(ascending_order) return { label: label, value: label == "NET" ? -Infinity : values[ind][0]};
        if(!ascending_order) return { label: label, value: label == "NET" ? -Infinity : values[ind][0]*-1};
    });
    sort_objects.sort(function (a,b) {
        return a.value - b.value;
    });
    sort_objects.forEach(function(obj) {
        table.moveRowAfter(table.rowIndex(obj.label), null);
    });
}


if (!use_stats_right & table.numberColumns > 1) {
    let values = below_table.get(stat_to_sort_by);
    let sort_objects = table.columnLabels.map(function (label, ind) {
        if(ascending_order) return { label: label, value: label == "NET" ? -Infinity : values[ind][0]};
        if(ascending_order) return { label: label, value: label == "NET" ? -Infinity : values[ind][0]*-1};
    });
    sort_objects.sort(function (a,b) {
        return a.value - b.value;
    });
    sort_objects.forEach(function(obj) {
        table.moveColumnAfter(table.columnIndex(obj.label), null);
    });
}

Next

How to Recode Net Promoter Score (NPS) Variable(s)

How to Customize the Formatting of Tables in Displayr

How to Create Summary Visualizations