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