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, making it easy for the user to 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

You will need two tables:

1. A summary table constructed from a numeric or numeric - multi variable set, which shows the average or row sample size:

   

   If you need to construct a numeric variable from a nominal/ordinal variable, see How to Calculate an Average Value from Categorical Data in Displayr.

2. A summary table that shows the Average or Row Sample Size from Data > Statistics > Right in Properties :

   

Method 1 - Use the Select Matching Rows rule

You will sort the separate table that shows only the statistic that you want to sort by (see Item 1 in Requirements) first, and then use another rule to link the sort order to your larger summary table.

1. Select the table that shows only the Average or Row Sample Size statistic.
2. From Properties , go to Data > Rules > Sort/Reorder > Sort Rows (or Columns if need be) rule to sort the table in the order you wish.
3. Select the larger summary table (Item 2 in Requirements).
4. From Properties , select the Data > Rules > Rows/Columns > Select Matching Rows (or Select Matching Columns) rule. 
5. In the Select rows using dropdown, select the table that is sorted the way you wish (the table from step 2 above). You can get the table's name from General > Name in Properties .
6. Set Use selected input's > rows in order to use the rows of the new table for matching instead (or columns, if columns were selected in Step 2).
7. 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.
8. Click OK to sort the larger table in the order needed.
9. OPTIONAL: Right-click the table that contains only the statistic used to sort and select Hide to hide from exports.

Method 2 - Use a Custom rule

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

1. Select the table you want to apply the rule to.
2. In Properties , 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 panel 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) {
            return { label: label, value: label == "NET" ? -Infinity : values[ind][0] };
    });
    sort_objects.sort(function(a, b) {
        return ascending_order ? a.value - b.value : b.value - a.value;
    });
    sort_objects.reverse().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