Need Help with Proper Numeric Sorting in Quick Sight Filters

Q&A
, , ,
1

Hi Team,

I am facing an issue with sorting values in a filter field in Quick Sight.

For example, I have a field called CATEGORY_GROUP_ID_NAME which contains values like this:

Currently showing in Quick Sight (incorrect sort order):

1-RETAIL
11-MEDIUM TRUCK PARTS
12-TURF/TRAILER
2-TRUCK
20-MISC/OTHER
21-USED TIRES
22-SCRAP TIRE DISPOSAL
23-FOAM FILL/PRESSING
24-OTR PARTS
3-OTR
4-INDUSTRIAL
5-AG/FORESTRY
6-BANDAG RETREADS, REPAIRS & CASINGS
7-OTR RETREADS & REPAIRS
8-BILLED LABOR
9-MECHANICAL PARTS

But I want the filter values to appear in proper numeric order, like this:

1-RETAIL
2-TRUCK
3-OTR
4-INDUSTRIAL
5-AG/FORESTRY
6-BANDAG RETREADS, REPAIRS & CASINGS
7-OTR RETREADS & REPAIRS
8-BILLED LABOR
9-MECHANICAL PARTS
11-MEDIUM TRUCK PARTS
12-TURF/TRAILER
20-MISC/OTHER
21-USED TIRES
22-SCRAP TIRE DISPOSAL
23-FOAM FILL/PRESSING
24-OTR PARTS

As you can see, Quick Sight is currently sorting these values as text (lexicographic order) instead of numeric order, which puts 11 and 12 before 2 and 3.

We tried multiple approaches like calculated fields (splitting the number prefix and casting to integer), but haven’t found a proper solution for sorting filter values this way.

Could you please suggest how we can achieve this kind of numeric-aware sorting in Quick Sight filters?

Thanks in advance!

2

Hi @vishalbagad

Please try the following calculated field which prepends a zero to the numeric prefix if its length is 1 (i.e., for numbers 1 to 9). This approach works by extracting the numeric prefix before the dash (-) using the split function and checking its length to enable numeric-aware sorting of filter values.

Example: (Note: Replace the fields from your dataset)

ifelse(strlen(split({Category},"-",1)) = 1 ,concat('0',{Category}),{Category})

image
image522×516 41.3 KB

1 Like
3

Hi @vishalbagad,

adding spaces before string values allows to affect sorting without visible changes. It’s pretty close to what @Xclipse proposed, but it does not affect visible values.

1 Like