Custom sorting

Q&A
, ,
1

Hi everyone, I’ve been trying to create a custom sort order for one of my visuals:

image
image807×539 31.7 KB

I need the order to be 0, 1-4, 5-15, 16-20, 21-42, Missing

My field wells are the following:

image
image851×122 8.26 KB

(I’m new to QS, so hopefully I used the correct fields in the correct wells.)

I then created a new field and this is my syntax:
image

Now I’m not sure which field well this new calculated field should be moved to.

I tried moving it to Group/Color, but then the labels are just 1-6:

image
image799×538 30.1 KB

Any suggestions?

Thanks.

2

I don’t think you can apply custom sorting to Group/Color. In a vertical bar chart you can only do it on the x-axis and in a horizontal bar chart you can do it on the y-axis.

For example, to apply custom sorting to the y-axis, select “Sort options”.

image
image817×251 22.4 KB

Select the field that you want to sort by and the aggregation (e.g. Min).
image

1 Like
3

@jotg
Can you try to edit your calculated field and instead of using 1, 2, 3, 4, 5, 6, using ‘1: 0’, ‘2: 1-4’, ‘3: 5-15’, ‘4: 16-20’, ‘5: 21-42’, ‘6: Missing’, ‘else’?

This way the legend labels would be
1: 0
2: 1-4
3: 5-15
4: 16-20
5: 21-42
6: Missing

1 Like
4

Thanks for the suggestion, but I don’t really want the legend to look like that, although that might be my only option. :slight_smile:

5

Thanks David. I could see that. This is quite frustrating as things like this are so easy to do in PowerBi. The NIHSS scores are an indication of stroke severity, so it would be good for a hospital to see how many admissions they had in each category per month as more severe strokes require more resources. I’m not sure how else to display this.

1 Like
6

@jotg
Another option is to change the labels to
0
01-04
05-15
16-20
21-42
Missing

This way it should be ordered as expected. Does it work for you?

1 Like
7

Thanks.

This is the syntax I’m using:
image

But I’m getting an error on the second last line.

8

And adding a comma after the 0 on line 7 then gives me an error in line 8.

9

@jotg
You can try to define it as a string, so something like

ifelse(
{cf} = ‘0’, ‘0’,
{cf} = ‘1-4’, ‘01-04’
…

10

Similar error:

image

I’m not sure what I’m doing wrong. :woman_shrugging:

11

The issue is with the 0 at the end. You’re creating this field as a string, so you can’t assign a 0. Either all values should be a string or all values should be a number. You can’t mix string values and numbers. You can change the 0 to null or if you really want 0, you can assign it as a string by putting it in quotes.

12

Ok thanks, I’ll give that a try. :slight_smile:

13

There is a hacky possibility of achieving your own sorting in visualizations that do not support custom sorting. You can add empty spaces before your values and use classic alphabetical sorting. QS will use leading whitespaces for sorting, but it does not display them. Keep in mind QS may change this functionality at any time.

14

Thanks for that tip. :slight_smile:

15

@jotg
Yes, you should remove the 0 in the end, and replace it with a string, like ‘by default’ or ‘anything else’.

Let us know if you run into any issue.

1 Like
16

Thanks.
My understanding of QS is still very basic, so I’ll have to work out what a string is and then take it from there.
Thanks again for all of your help.

1 Like