Bar chart - ordering buckets/bins

Q&A
, ,
1

Hi all,

I have an display/sorting issue when displaying size buckets/bins using a bar chart.

The bucket labels which I’m using are strings - <500, 1k_2k, 2k_3k etc. etc., so when I plot these on the chart with the respective values in each bucket, they wont necessarily display in order of the above.

When I display this information in a table, I have created a calculated field which allocates alphabetical letters based on the bucket, so I can order them A –> Z (e.g. the above would be A, B, C etc. etc.). I add this to the table, sort accordingly and just hide the column.

Is there any way to do this when using the bar chart?

I don’t want to change the bucket/bins to a.<500, b.1k_2k, c.2k_3k etc. etc.

Thanks,

Q

2

Hi @QSCommUser ,

You can sort your visualization by a field that is not displayed. This field can contain the sort key (e.g., 1 2 3 or a b c).

You can find instructions in the chapter: To sort by using an off-visual metric

image
image542×332 29.4 KB

Best regards,

Nico

Did my answer help you in resolving your request? If yes, I would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

3

Hi Nico,

Thanks for the resposne - I knew there would be a way to do this.

Unfortunately, this has not worked for me. Below is some test data I have used, where I have created calculated filed “bucket"_sort” which assigns values a-e to the relevant bucket type:

ifelse(
{bucket} = "<1mn", "a",

{bucket} = "1mn_5mn", "b",

{bucket} = "5mn_10mn", "c",

{bucket} = "10mn_20mn", "d",

{bucket} = "20mn_50mn", "e",
“undefined”

)

image
image394×279 6.47 KB

I have applied the off-visual sorting, but what appears to have happened is that is it sorting the bucket field based on the string value - i.e. 10 –> 1 –> 20 –> 5 etc.

This is not the intended hehaviour. Also, the sorting now does not change when I select ascending or descending - it just stays as the above.

Any thoughts on why this is occuring?

Thanks

4

Hi @QSCommUser ,

my bad. I thought it is possible to sort strings (a b c) in an alphabetical order. Numbers are better. Please change your calculation from a b c to 1 2 3.

ifelse(
{bucket} = "<1mn", 1,

{bucket} = "1mn_5mn", 2,

{bucket} = "5mn_10mn", 3,

{bucket} = "10mn_20mn", 4,

{bucket} = "20mn_50mn", 5,
1000

)

In the Off-visual field you select your calculated field and as aggregation select “Min”.

After you press “Apply” you should see some changes.

image
image229×390 9.33 KB

Best regards,

Nico

5

Hi Nico,

Thanks for this, but unfortunately this does not work.

Changing the “bucket_sort” to numbers (integers) wont work as i need to return the same data type when doing the calulcation, and as the “buckets” are strings, I need to retrun them as such.

Even changing 1, 2,3 etc. to “1”, “2”, “3” as strings, the sorting applied on screen is the same as when I was using alphabetical letters - i.e. the sort is occurs on the string values from the “buckets” field and the bar chart is ordered 10 –> 1 –> 20 –> 5 etc.

Do you think this could a limitation of the graphic itself or is there something else going on here?

Thanks

Q

6

Hi @QSCommUser ,

could you please upload your dummy-data to quicksight arena?

That may help me to see the issue and to solve it.

Best regards,

Nico

7

Hi Nico,

Here is some sample data:

Bucket item_count
<1mn 45
1mn_5mn 13
5mn_10mn 22
10mn_20mn 85
20mn_50mn 37

As mentioned, I’m using a simple bar chart and then using the sort by “off-field” visual using the calculated field of bucket_sort pasted earlier in the thread.

I have tried this on another categorical field in a bar chart (with a calculated field to sort that categorical field) - but I’m getting the same result, in that it is sorting the string values based on alphabetical order.

Regards,

Q

8

Hi @QSCommUser

I tried it in Arena and it works for me. Here are the steps:

Calculated field “sort_order” (as numbers):

ifelse(
Bucket = "<1mn", 1,

Bucket = "1mn_5mn", 2,

Bucket = "5mn_10mn", 3,

Bucket = "10mn_20mn", 4,

Bucket = "20mn_50mn", 5,
0

)

image
image1086×520 48.9 KB

image
image864×443 24.7 KB

  1. (First Screenshot)
    1. Choose Visual Type “Vertical bar chart”.
    2. Drag and drop Bucket on X Axis and item_count on Value.
    3. Open the Menu (three dots) on Bucket
    4. Choose “Sort by” and then “Off-visual field…”
  2. (Second Screenshot)
    1. Choose “sort_order” (your calculated field") for Sort by
    2. Aggregation “Min”.
    3. Sort order “Ascending”
    4. Click on “Apply”.

You can find my example here: Sort Order for Barchart

Does this help you?

Best regards,

Nico

Did my answer help you in resolving your request? If yes, I would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

9

Hi Nico,

Thanks very much for taking the time to do this. I’m familiar with the QS Arena, hence why I didnt not use it to upload - it also seems I dont have the right permissions to view the analysis (when clicking on the square box’s on the left.

I have changed the values in my calculated field to return 1, 2, 3 etc. and this now works for me, using the agg method as “Min”.

This has confused me a little, as I always thought that i the ifelse statement, it had to return the same data type as what is being passed to it i.e. if you pass a string (eg. “<1mn”), you have to return a string also. Seems my thinking was wrong here and appears to the cause of my issues.

Thanks again.

Q

1 Like