Sorting Year date Filter in Ascending order

ozarkram
ozarkram
in Magic ETL

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

Tagged:

Comments

  • mhouston
    mhouston Contributor

    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
    ozarkram

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

  • mhouston
    mhouston Contributor

    @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
    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
    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
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ozarkram
    ozarkram

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

Categories