Sorting Year date Filter in Ascending order

ozarkram

Hi All,

I have a beast mode created as CONCAT(YEAR(`pickupDATE`),'-',MONTHNAME(`pickupDATE`)) which in turn returns values such as 2020-April, 2020-june and so on...When I try to display this as a quick filter I get the values displayed in the quick filter in my card in random order -such as

2020-April

2020-August

2020-December

and so on....Can you please let me know how to display these values in ascending order in my quick filter as below

2020-April

2020-May

2020-June

2020-July

and so on...

Thanks,

Arun

mhouston

Since you are using the month name, it is sorting alphabetically. I think you could just sort by pickup_date to sort it in date order. Otherwise, if you want the month name in your display, I would create a second beast mode for sorting on using the year and month number: CONCAT(YEAR(`pickupDATE`),'-',MONTH(`pickupDATE`)).

ozarkram

@mhouston ...Appreciate your response!..Can you please guide me on the sort beast mode on CONCAT(YEAR(`pickupDATE`),'-',MONTH(`pickupDATE`)).

mhouston

@ozarkram you would create a new beast mode with the formula CONCAT(YEAR(`pickupDATE`),'-',MONTH(`pickupDATE`)) and then put that beast mode in the sort section of your card, and select sort ascending.

ozarkram

@mhouston : The values in my quick filter still don't appear in the ascending order...Only the values in my data table are sorted in ascending when I drag my beast mode to sorting and select ascending...

ozarkram

I tried the beast mode like this :CONCAT(YEAR(`REQ_PIC_DATE`),'-',MONTHNAME(`REQ_PIC_DATE`))

order by YEAR(`REQ_PIC_DATE`),MONTHNAME(`REQ_PIC_DATE`)...But its invalid...

MarkSnodgrass

Quick filters to do not pay any attention to the sorting properties, unfortunately. They are treated as strings and will always sort ascending. Your best bet is to have your values be Year-Month, with the month always being 2 digits. You can do that like this:

DATE_FORMAT(`datefield`,'%Y-%m')

This should return it as 2022-04 for April 2022.

ozarkram

Thanks @MarkSnodgrass !..This works..Appreciate your help!