Overview
Expected Effect
For 11.0.3 and later versions, you can use the advanced sorting function to achieve multi-column sorting. However, for versions earlier than 11.0.3, you may not know how to achieve custom sorting order for data columns with fewer records and no sorting rules. Take sorting by size as an example, as shown in the following figure.
Solution
You can use formulas to achieve multi-column sorting.
Example
This document introduces how to use the advanced sorting function with a built-in dataset to set a custom sort order.
Data Preparation
Create a general report, create a built-in dataset, and add two fields size and number. The size field ranges from XS to XXL, and needs to be shuffled; the number field can be set randomly, as shown in the following figure.
Report Design
Drag the data columns into cells A2 and B2, enter the corresponding titles in cells A1 and B1, and customize the table style, as shown in the following figure.
Advanced Sorting Setting
The left parent cell of the report is cell A2. Select cell A2, click the Cell Element icon, click Advanced, set Sort Rule to Ascend and Sort Basis to Formula, and enter the formula switch($$$,"XS",1,"S",2,"M",3,"L",4,"XL",5,"XXL",6) or INDEX($$$,"XS","S","M","L","XL","XXL").
The formula description is as follows:
$$$: the current data column
switch: If the current value is XS, then return 1; if the current value is S, then return 2, and so on.
INDEX: return the position of the current value in the sequence "XS", "S", "M", "L", "XL", and "XXL".
Finally, sort according to the size of the returned number.
Effect Display
On PC
Save the template, click Pagination Preview, and the data will be arranged in the order of XS, S, M, L, XL, XXL, as shown in the following figure.
On Mobile Terminals
The template can be previewed on both the DataAnalyst and HTML5 apps. The effect is shown in the following figure.
Template Download
You can download the template.Custom sort order.cpt
