Display Month in Chronological Order in the Filter

Q&A
, ,
1

I want to display Jan, Feb till Dec instead of 1,2 till 12 and I want that in chronological order to use it in a filter. How to achieve this?

2

Hi @QS_User

Welcome to the Quick Sight community!

To display months as Jan, Feb, till Dec in chronological order in an filter, you need to create a calculated field that maps month names to their corresponding month numbers and then use the month number to sort the month names chronologically.

Example:

ifelse(
  {Month}='January',1,
  {Month}='February',2,
  {Month}='March',3,
  {Month}='April',4,
  {Month}='May',5,
  {Month}='June',6,
  {Month}='July',7,
  {Month}='August',8,
  {Month}='September',9,
  {Month}='October',10,
  {Month}='November',11,
  {Month}='December',12,0
)

Please refer the below community posts this might be helpful for you.

3

@Xclipse Thanks for the solution, I am new to Quick Sight. I tried to follow the steps you provided. In my case, I am displaying the data using a table visual, and I am not using any x-axis or y-axis for sorting. Instead, I am only using Month as a filter control, and users select the month(s) from this filter to filter the table data.

I do have a workaround where I can display months as 01-Jan, 02-Feb and so on, which then sorts correctly. However, I only want to display Jan, Feb, Mar … in chronological order without concatenating numbers to the names.

4

Hi @QS_User

Create a calculated field for the month name, such as Jan, Feb, Mar ..., and another calculated field for the month number. Add both fields to a table visual, then sort the table by the month number field. Hide the month number column, and the month names will be automatically sorted in chronological order.

Example:

MonthNO -
ifelse(
  Month='January',1,
  Month='February',2,
  Month='March',3,
  Month='April',4,
  Month='May',5,
  Month='June',6,
  Month='July',7,
  Month='August',8,
  Month='September',9,
  Month='October',10,
  Month='November',11,
  Month='December',12,0
)

image
image508×558 32.3 KB

5

Thanks for the solution, but I don’t want to show it in the table visual at all, I just want to use the text month in the control filter as shown below. Can this be sorted in Chronological order?

image
image574×754 44.1 KB

6

Hi @QS_User

By default, the filter sorts the values alphabetically. If you want to sort the months in chronological order, you need to append the month number to the month name (for example, “01-Jan”) so that the sorting works numerically.

7

@Xclipse Thanks, yes I am aware of this solution, but I wanted the other way as I described.

Once again thanks for your quick responses.

8

Hi @QS_User

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!