Sorting number strings in table

Question & Answer
,
1

I am creating a pivot table that displays data by an Age field and also a Grade field. These fields have been converted to strings because they need to be included in a calculated field with other string values. But because of this, the rows with each grade category are sorted incorrectly, since it does not recognize the actual number values there:

image

Strangely enough, the Age field does sort properly when swapped with the Grade field, even though this too is a string value type in the dataset:

image

Any ideas what’s going on here and how to sort the Grade categories numerically?

2

Hi @jtroxel,

Your first visual is actually being sorted correctly. When sorting string values, 18 is before 5. If you had 100, it would also be before 5.

You have to create a calculated field that assigns a numerical value to each grade and then use the “Sort by off-visual field” option.

image

1 Like
3

@David_Wong Thanks! So this worked and even when the dynamic “Row fields” calculated field i have switches to “Age” or another field. I don’t fully understand why it doesn’t screw up the age sorting when it changes from “Grade” to “Age” but so far its not causing any problems so ill take it.

image

4

@David_Wong just to follow up on this, the age categories were in fact getting shuffled around incorrectly. I solved this by using the OR operator in my sorting calculation:

ifelse({Ex_grade} = “5”, 5, {Ex_grade} = “6”, 6, {Ex_grade} = “7”, 7, {Ex_grade} = “8”, 8, {Ex_grade} = “9”, 9,{Ex_grade} = “10” OR {Ex_age} = “10”, 10,{Ex_grade} = “11” OR {Ex_age} = “11”, 11,{Ex_grade} = “12” OR {Ex_age} = “12”, 12,{Ex_grade} = “13” OR {Ex_age} = “13”, 13,{Ex_grade} = “14” OR {Ex_age} = “14”, 14,{Ex_grade} = “15” OR {Ex_age} = “15”, 15,{Ex_grade} = “16” OR {Ex_age} = “16”, 16,{Ex_grade} = “17” OR {Ex_age} = “17”, 17, {Ex_grade} = “18” OR {Ex_age} = “18”,18, NULL )

I am finding that a lot of solutions to common problems like this, dynamic fields, etc., seem to require the developer to commit common programming no-no’s like this (convoluted conditional logic). i am also having to rely heavily on ifelse() for a couple dynamic fields that are to change based on user input for these tables, eg.,:

“Row field”
ifelse(${RowParameter} = “Sex”, Sex, ${RowParameter} = “Age”, ifelse({Ex_age} = “”, “Unknown”, {Ex_age}), ifelse({Ex_grade} = “”, “Unknown”, {Ex_grade}))

Not only are these solutions not very scalable, but they are not as automated as they could be if, for example, we could leverage things like string placeholders or the “this” property like in javascript, instead requiring a lot of manual updates and maintenance as the dashboard grows in scale. Are there any features you can think of that can handle these kinds of things more dynamically and efficiently?

5

@David_Wong this worked initially but did not stand up to much testing unfortunately. the odd thing is that quicksight is able to sort strings that begin with numbers in some, but not all cases. trying to understand how this works since creating a single, off filed sorting field that accounts for any and all variables regardless of their unique categories is not effective at scale.
for example, in the table below, i have been able to determine the order of the columns by creating a calculated field that put a number in front of each category so that the “none of the time” - “all of the time” order is maintained. some how it is able to understand how to order these items, but not age or grade categories when they are set to string types. appending a number or letter in front of each age is not very user friendly either.

image
image778×435 8.11 KB

this has been an unexpected hang up that is becoming quite an issue since it will almost certainly be called out by our client and there does not appear to be a clear solution. do you know why the column strings are sorting on their own but not the rows?

6

Hi @jtroxel,

It looks like the only reason you need calculated fields to convert Age and Grade to strings is because you need to display that “Unknown” value in your pivot table. Do you have columns in your data source that contain the age and grade as numbers? If so, you should include them in your dataset so that you don’t need to create yet another calculated field to convert the strings back to numbers.

You need one calculated field to display in your table and another to use for sorting. Something like this:

Display Field = ifelse(${RowParameter} = ‘Age’, {Age_String}, ${RowParameter} = ‘Grade’, {Grade_String}, null)

Sort Field = ifelse(${RowParameter} = ‘Age’, {Age_Number}, ${RowParameter} = ‘Grade’, {Grade_Number}, null)

Select the “sort by off-visual field” option and sort by “Sort Field”.

If you can upload some sample data to Arena and share it here, I can take a look and see why the sorting is not working correctly. I haven’t encountered this issue before.

2 Likes
7

@David_Wong the Grade and Age fields needed to be converted to strings because the “Row Field” calculated field they were included in also had other string fields as part of the ifelse calculation there, and that was creating data type issues. But parameterizing the sort field using duplicate integer versions of those fields is indeed working as expected and is definitely more manageable than my super long version with the OR operator. Thank you!