Advanced Sorting

Data Pipeline

When sorting dimensions in Chartio, by default all columns sort in ascending order following the order in which they are listed in the Dimensions box. Measures always take precedence over Dimensions, but sometimes you want to first sort something in the Dimensions box and then sort by an item in the Measures box, for example running a query that sorts by date and then by user.

Chart Editor

There are a few options to do more advanced sorting in your charts:

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 that this will disconnect you from using Interactive Mode anymore for this chart.

Sort Rows in the Pipeline

Add a Sort Rows step in the Data Pipeline which will allow you to re-sort all of the resulting columns in your query in any direction and order.

To do this, hover over the chart and click on the ellipsis in the upper right hand corner and click Edit Chart Data. Scroll down and click +Add Transformation in the Data Pipeline. Click Sort Rows.

Add a step in the Pipeline by cicking +Add Transformation

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

Add a Sort Rows step in the Pipeline

Custom Sorting

If you would like to sort your rows in your dataset or Table chart in a custom order that is not ascending or descending alpha-numeric sort, there are a couple of options:

Creating a sort column using a Case Statement

One way would be to use the Case Statement in the pipeline to create a new column, named something like ‘sort-order’. Using the Case Statement, you can assign the value of your ‘sort-order’ field a number that will define your desired sort order. Then, 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. Lastly, 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:


Related Data Pipeline Help Articles

See more