The Months appear randomly on the Bar Chart instead of in an order. How to change the order of data presented in a Vertical Bar Chart?

Question

I have been creating dashboards on QuickSight for a few months now. I am currently trying to create a dashboard that shows how many people have logged in to our App and want to present this by months.

The problem that I have is that QuickSight tends to present the data in a random way and although I can sort this alphabetically, that does not help me. See an example screenshot with sample data; vertical bar chart

As you can see from the screenshot, the months are ordered as Sept, Oct, Aug.

How can I change this to reflect the correct order of months such as Aug, Sept, Oct. I also want to make sure that going forward, whenever I update the data, I can still be able to sort them correctly.

Hope this make sense

Answer 1

I assume that you are seeing this ordering because the fields you are sorting by are textual (varchar, text, etc) and not ordinal (at least numerically). In other words, QuickSight is sorting alphabetically by month name not by month order. Fortunately there are a couple of ways to resolve this.

I don't really know a lot more about your data but the best way to resolve this is to create an actual date field that represents the month (e.g. the first day of the month). Dates are first-class in QuickSight and should sort naturally in the order you're looking for.

If you already have date entries but they're not monthly, you can use truncDate in a calculated field to truncate the dates to the month level then use formatting options to display them as months.

Lastly, if you absolutely had no other options you could parse your existing fields (in the format Mon-YY) to create date fields.

Do do this you would actually most likely want to create multiple calculated fields that culminate as the date, for example:

Make a new calculated field for the month that does something like, assuming your date column is named yourMonthCol

ifelse(
  split({yourMonthCol}, '-', 1) == 'Jan', '01',
  split({yourMonthCol}, '-', 1) == 'Feb', '02',
  ...,
  split({yourMonthCol}, '-', 1) == 'Nov', '11',
  '12'
)

Then you'd need to do something similar for the year

split({yourDateCol}, '0', 2)

Finally, you should be able to get dates by making a calculated field like the following from the created calculated fields

parseDate('01/' + month + '/' + year)

Then you should be able to use that date as described above. That was a mouthful but I hope it helps!