Chartio is going away March 1, 2022
Documentation Data Pipeline FAQs

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.

Chart Editor

Ascending/descending sorting

SQL Mode

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

  1. 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.

    Add a step in the Pipeline by cicking +Add Transformation

  2. Choose the columns to reorder and the desired sorting direction (ascending or descending). Click Apply & Close when completed.

    Add a Sort Rows step in the Pipeline

Custom sorting

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.

Use a Case Statement step to create a custom sort

Finally, use the Sort Rows pipeline step to sort your rows based on the “sort-order” column.

Use your custom sort in a Sort Rows step

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.

Use transpose to flip rows to columns

Check out the video below to see how to quickly use the Transpose and Reorder Pipeline steps to sort:

Need more help?