Sort FAQs

This article introduces the problems you may encounter when using various sort methods and the corresponding solutions.

Problem

An error will occur when you preview a template or click the column header for sorting if you sort value-type fields that include empty fields.

For example, If the Order ID column is set to change the sort order by clicking the column header, the formula entered in Sort After Expansion is if($a=1,$$$,-$$$), which sorts the data based on the value of the current column. 

At this time, if an error as shown in the following figure appears when you preview the template and click the Order ID title, see the following solution.

Solution

Assign a value to the empty field in the sort formula before sorting data.

The formula is if($a=1,if(len($$$)=0,min(ds1.select(Order ID))-1,$$$),if(len($$$)=0,-(min(ds1.select(Order ID))-1),-$$$)), which assigns a value (minimum value of Order ID - 1) to the empty field for it to be sorted.

The following table describes the formula.

Preview the report after modifying the formula, and the effect is shown in the following figure.

You can download the example template. Sort value-type data that contain empty fields.cpt

Problem

You may want to horizontally expand the Month column when making a monthly report. However, if the expanded headers are not pure numbers, such as Month 1 and Month 2, sorting by the original value will lead to disorder. 

Solution

Use the formula INT(REPLACE($$$,'Month','')) to replace the character Month with an empty string, and then convert it to an INT-type string.

The string values are converted into integer-type data after that and will be sorted in ascending or descending order according to the set sort rule if you conduct a sort then.

Problem

The data have been pre-processed in the database table, where the value of an empty field is set to --, which may cause strings in the data. After you perform a sort after expansion, -- is placed above zero and negative values if data are sorted in ascending order and is between zero and negative values if data are sorted in descending order.

Solution

You are advised to process string values using SQL statements first, and you can choose to set them to 0.

Problem

Each time you click a column header to sort, the page is refreshed, which may take a long time if there is a large amount of data on the page.

Solution

Use JavaScript to sort data without refreshing the page.

If there is no frozen row, see JS Sort Rows Without Refreshing the Page.

Problem

An error occurs, saying "Comparison method violates its general contract!" The template cannot be previewed.

Cause

There is an excessive amount of data and Sort After Expansion is set.

Solution

Sort After Expansion can cause logical conflicts if the amount of data is large, resulting in an error, saying "Comparison method violates its general contract!" You are advised to use the advanced sort.

However, the advanced sort cannot be used directly if the data has been summarized. You are advised to summarize the data using SQL statements before using the advanced sort.

Problem

Data in the cells and the data source are sorted, but arranged in disorder on the category axis of a chart.

Cause

It is preliminarily analyzed that the priority of arrangement is given to the category that exists in all series, and then the category that exists in some series during the chart creation to avoid breakpoints in the middle.

Solution

Complete the categories in each series.