Ranking dates in a Beast Mode doesn't yield the right order in a card
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
-
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.
0
Answers
-
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.
1 -
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.
1 -
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.
0 -
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.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive