Advanced sorting in Data Explorer
When adding columns to the Measures and Dimensions fields and applying a Sort to those columns in your dataset, Measures always take precedence over Dimensions in the ORDER BY clause. Dimensions are then sorted in the order that they’re listed in the Dimensions field.
If you want to sort by the Dimensions first then sort by a Measure—for example, sorting by date then by the count of users—, you have a few options for performing this type of advanced sorting.
Switch your chart to SQL Mode and edit the SQL to change the order of the sorted columns in the ORDER BY clause.
Note: Updating the SQL query in SQL Mode will disconnect Interactive Mode and SQL Mode for the chart. In other words, making subsequent changes to your dataset in Interactive Mode after editing the SQL Mode query will no longer update the auto-generated query in SQL Mode.
Sort Rows in the Pipeline
Add a Sort Rows step in the Data Pipeline, which allows you to re-sort all of the resulting columns in your query in any direction and order.
To do this, click +Add Transformation in the Data Pipeline then click Sort Rows.
Choose the columns to reorder and the desired sorting direction (ascending or descending). Click Apply & Close when completed.
If you’d like to sort your rows in your dataset or Table chart in a custom order that’s not ascending or descending alpha-numeric sort, there are a couple of options:
Create a sort column using a Case Statement
One option is to use a Case Statement in the Pipeline to create a new column, naming it something like
sort-order. Using the Case Statement, you can assign the values of your “sort-order” column a number that will define your desired sort order.
Finally, use the Sort Rows pipeline step to sort your rows based on the “sort-order” column.
Transpose and reorder
Another quick option would be to leverage the Transpose and Reorder Columns steps. In the Pipeline, select the Transpose transformation to swap the rows for columns and columns for rows. Once transposed, use the Reorder Columns pipeline step to drag your columns (i.e., your rows) in the desired order. Finally, add one last Transpose pipeline step to revert your columns back to rows.
Check out the video below to see how to quickly use the Transpose and Reorder Pipeline steps to sort: