Ranking dates in a Beast Mode doesn't yield the right order in a card

LP
LP Member
in Magic ETL

We are trying to make a simple radio button display to put into a dashboard, and the date is in the format YYYY-Month. The default order is alphabetical, so the information is displayed 2018-August, 2018-December, 2018-July, and so on. (This is for FY19 data only, minus July.) So, one of my managers wrote a simple Beast Mode to order the dates in the list in month order, as below:

 

(CASE
WHEN `Month and Year` = '2018-August' THEN 1
WHEN `Month and Year` = '2018-September' THEN 2
WHEN `Month and Year` = '2018-October' THEN 3
WHEN `Month and Year` = '2018-November' THEN 4
WHEN `Month and Year` = '2018-December' THEN 5
WHEN `Month and Year` = '2019-January' THEN 6
WHEN `Month and Year` = '2019-February' THEN 7

END)

 

That Beast Mode is then put into the 'Sorting' category, with no aggregation, in ascending order. And yet, the order of the options in the radio button display does not change. Furthermore, it doesn't change when you set it in descending order. I've checked and that column of the dataset is free of extra spaces or misspellings or anything. Please help!

Best Answer

Answers

  • KVincent
    KVincent Member

    Are you sorting on another field as well? I have noticed when I try to sort by two things that it does not work with domo. 

  • LP
    LP Member

    @KVincent  Thanks for responding. No, that's the only field we are sorting by.

  • KVincent
    KVincent Member

    Is that beast mode encompassing all possible month-year combinations? I am thinking that if it doesnt then the blanks by not having an else statement might be triggering the resulting fields to be blank and make it read as text which would not allow you to sort ascending or descending. You could test by adding an else 0 at the end.

  • LP
    LP Member

    It does encompass all of the possible options. The Beast Mode does result in a numeric variable- I changed it to 'a', 'b', etc. and it was text (and still didn't work!). I tried else 0 and else 9 and neither of them worked... we may just call Domo Support if this persists.

  • ST_Superman
    ST_Superman Domo Employee

    Is the `Month and Year` field a text field or a date field?  The radio button will always default to displaying the values in ascending order.

     

    You may want to try using a beast mode similar to this for the actual radio button:

    (CASE
    WHEN `Month and Year` = '2018-August' THEN '1: 2018-August'
    WHEN `Month and Year` = '2018-September' THEN '2: 2018-September'
    WHEN `Month and Year` = '2018-October' THEN '3: 2018-October'
    WHEN `Month and Year` = '2018-November' THEN '4: 2018-November'
    WHEN `Month and Year` = '2018-December' THEN '5: 2018-December'
    WHEN `Month and Year` = '2019-January' THEN '6: 2019-January'
    WHEN `Month and Year` = '2019-February' THEN '7: 2019-February'

    END)

     

     

    The alternative would be to convert it to an actual date field:

    STR_TO_DATE(`Month and Year`, '%Y-%M')

     

    You would then get a date field and you should be able to have it show up in the correct order.  However, I believe that it will default to adding the day as 01... so you would get dates of 2019 Feb 01

     

    If that is misleading, and you are measuring through the end of the month then you could do something like this:

    STR_TO_DATE(

    CASE WHEN CONCAT(YEAR(curdate()),'-',MONTHNAME(curdate()))=`Month and Year` then 

    CONCAT(YEAR(curdate()),'-',MONTHNAME(curdate()),'-',DAY(curdate())) 

    ELSE

    CONCAT(`Month and Year`,'-',LAST_DAY(MONTH(STR_TO_DATE(`Month and Year`,'%Y-%M')

    END, '%Y-%M-%d')

     

    This would make the value the last day of the month, or today's date if you were looking at the current month.  

  • LP
    LP Member
    Answer ✓

    I FOUND THE SOLUTION

     

    https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Sort-order-for-legend-not-working/td-p/27597/page/2

     

    Simply checking the box by General > Unsorted Data gave me the right order in combination with the Beast Mode in the OP. Thank you to everyone who helped.

Categories