How to Custom Sort Tables

There are many methods for sorting tables automatically; see How to Automatically Sort Rows and Columns. However, you may have a specific, custom order of the rows/columns you want to use. One option is to use a rule that references a Combo box, Calculation listing the order, or a separate table to base the row order on, see How to Select and Sort Rows and Columns Based on Another Item. In the rare case where one of the other solutions doesn't fit what you need, this article describes how to write your own custom Rule to specify the order of rows or columns in a table programmatically to go from an unsorted table...

... to one based on a specified order:

Requirements

• A table with rows and/or columns.
• You have read How to Create a Custom Rule and How to Modify Table Rows and Columns Using a Rule.

Note: You must apply the rule directly to the input table if you see a table icon in the Data > Data Source > Data field in the Properties of a visualization.

You will also see a warning in the Rules section of Properties if you have applied a rule directly to a non-compatible visualization, and you will need to apply it to the input table instead. For example:

Method

1. Select your table.

2. From Properties , go to Data > Rules.

3. Click Plus (+) > New custom rule (write your own JavaScript) > Edit JavaScript.

4. Paste the below into the code panel:

form.setHeading("Custom sorting"); 
form.setSummary("Custom sorting");

var labels = ['Pepsi Max','Coke Zero','Diet Pepsi','Diet Coke','Pepsi','Coke'];
var last_index = null;

for (var i = 0; i < labels.length; i++) {
   table.moveRowAfter(table.rowIndex(labels[i]), last_index);
   last_index = table.rowIndex(labels[i]);
}

• The first 2 lines set the name of the rule that appears in the Properties and the dialog window.
• We then set the order of the labels by specifying them in an array called labels. Only specify labels that match those in your table exactly. Any others not listed will be left at the end.
• The main part of the code is kept in a for..loop to run through each label in labels.
• Here, we are sorting the table rows so we use the table.moveRowAfter function which requires the index of the row to be moved and its new position.
• table.rowIndex finds the label in the row and returns its position.
• last_index gets updated with the correct position based on the new order.

5. OPTIONAL: To apply this script to columns instead, you would adjust the code within the loop as follows:

table.moveColumnAfter(table.columnIndex(labels[i]), last_index);
last_index = table.columnIndex(labels[i]);

6. Press the Play button.

7. Click Save.

8. Click Close.

9. Click OK.

Next

How to Use Rules in Displayr

How to Customize a Rule

How to Create a Custom Rule

How to Modify Table Rows and Columns Using a Rule

How to Automatically Sort Rows and Columns