Order strings on x-axis of bar chart

Question & Answer
, ,
1

I have a bar chart that display total by month. The x-axis is “month” but these are not dates but strings. I see that these “months” are out of order. For instance, they show up “Oct, Apr, Jan, Aug” How can I force the these strings to display in the proper order? A calculated field perhaps? If so, if you could provide an example.
Thanks

2

Hello @pk007, welcome to the QuickSight Community! I understand that your x-axis values are strings and not dates. Is there a reason for that? If you had a date field you could convert it to a month using the truncDate() calculation, here is the link to the quicksight documentation for that function.

Now if you do not have a date field in your dataset at all, that will make this a little more complicated. If it is a string, you can only sort by alphabetical order. The other option would be to try and ifelse statement where you return the string values in order and replace your current field with the new one. It would look something like this:

ifelse(
{date} = "Jan", "Jan",
{date} = "Feb", "Feb",
{date} = "Mar", "Mar",
....)

I believe that would resolve your issue, let me know if that helps!

3

Unfortunately that didn’t work out. I’m wondering if there is a way to convert a string to date? Thanks

4

Hello @pk007, there is a parseDate function in QuickSight that allows you to convert a string to a date field. I will attach a link for the documentation here. I am not certain how it will handle a string field like “Jan” but it is worth a shot. Let me know if that helps!

5

Hi @pk007,

what if changing the given approch to create a calculated field “date_sort”

ifelse(
{date} = "Jan", 1,
{date} = "Feb", 2,
{date} = "Mar", 3,
....)

Then you can use the field as sort value. If you are showing more the one year you maybe need to add the year into the field as well.

BG

1 Like