Sort a filter pane with my custom order

manmade — Mon, 31 Jan 2022 08:55:59 GMT

Hi, I am trying to sort a filter pane according to the order I want. So I found order by expression but I am not understanding how to use that. In specific the column has values like JUL21, JAN21, etc. I want to sort it by month. Like JAN21, FEB21, etc.

Re: Sort a filter pane with my custom order

anat — Mon, 31 Jan 2022 10:21:49 GMT

you can try below function in custom sort order

Match(field, 'Jan2021', 'Feb2021',.....)

Re: Sort a filter pane with my custom order

Andrei_Cusnir — Mon, 31 Jan 2022 10:22:47 GMT

Values 'FEB21', 'JUN21' etc. are interpreted as strings and thus they can only be sorted alphabetically. You will have to change the sorting method to "Sort by expression" and then use a combination of various functions to construct a date from the given values. After that the values will be sorted based on the constructed date format. In your case, you should do the following:

1. Go to Edit sheet

2. Edit the Filter pane visualization

3. Go to Sorting and Un-check "Custom".

4. Choose "Sort by expression" and use the following expression:

MakeDate('20'&Right(Dates, 2), Match(Left(Dates, 3), 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','FEB','OCT','NOV','DEC'), 1)

Allow me to breakdown the parts of the expression for better understanding:

'20'&Right(Dates, 2) will concatenate "20" with the last 2 characters of each date. So either 21 or 22 etc. The result will be 2021 or 2022 etc.
Match(Left(Dates, 3), 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','FEB','OCT','NOV','DEC'), will give you the number of the month. You are getting the values JAN, FEB etc. using the Left() function and then you check the index. So for example JAN will give you number 1, FEB number 2 etc.
MakeDate() will build dates like 1/1/2021, 2/1/2022 etc. which will allow proper sorting of the values

Output is:

BEFORE:

AFTER:

As you can see now they are sorted properly!

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.

Re: Sort a filter pane with my custom order

manmade — Mon, 31 Jan 2022 11:36:17 GMT

Thanks a lot, it is working!

topic Sort a filter pane with my custom order in Visualization and Usability

Sort a filter pane with my custom order

Re: Sort a filter pane with my custom order

Re: Sort a filter pane with my custom order

Re: Sort a filter pane with my custom order