Month Order on Bar Chart
Has anyone ever had issue with months not being in right order on X axis in bar chart? I created a custom formula just so I could Order in my card but for some reason March is showing after November on X axis. When I look at datamart all March records have a 3 so dont understand why March is out of order. Any thoughts would be greatly appreciated!
CASE WHEN MonthName
= 'January' THEN '1'
WHEN MonthName
= 'February' THEN '2'
WHEN MonthName
= 'March' THEN '3'
WHEN MonthName
= 'April' THEN '4'
WHEN MonthName
= 'May' THEN '5'
WHEN MonthName
= 'June' THEN '6'
WHEN MonthName
= 'July' THEN '7'
WHEN MonthName
= 'August' THEN '8'
WHEN MonthName
= 'September' THEN '9'
WHEN MonthName
= 'October' THEN '10'
WHEN MonthName
= 'November' THEN '11'
WHEN MonthName
= 'December' THEN '12'
ELSE '' END
Best Answer
-
You're using a string for your sorting so it goes character by character so it's going '1', '11', '12', '2'. You need to return numbers to do proper numerical sorting:
CASE WHEN MonthName = 'January' THEN 1 WHEN MonthName = 'February' THEN 2 WHEN MonthName = 'March' THEN 3 WHEN MonthName = 'April' THEN 4 WHEN MonthName = 'May' THEN 5 WHEN MonthName = 'June' THEN 6 WHEN MonthName = 'July' THEN 7 WHEN MonthName = 'August' THEN 8 WHEN MonthName = 'September' THEN 9 WHEN MonthName = 'October' THEN 10 WHEN MonthName = 'November' THEN 11 WHEN MonthName = 'December' THEN 12 ELSE 99999 END
Alternatively, if you have a date and not just the month name you can do something like:
MONTH(`Date`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
You're using a string for your sorting so it goes character by character so it's going '1', '11', '12', '2'. You need to return numbers to do proper numerical sorting:
CASE WHEN MonthName = 'January' THEN 1 WHEN MonthName = 'February' THEN 2 WHEN MonthName = 'March' THEN 3 WHEN MonthName = 'April' THEN 4 WHEN MonthName = 'May' THEN 5 WHEN MonthName = 'June' THEN 6 WHEN MonthName = 'July' THEN 7 WHEN MonthName = 'August' THEN 8 WHEN MonthName = 'September' THEN 9 WHEN MonthName = 'October' THEN 10 WHEN MonthName = 'November' THEN 11 WHEN MonthName = 'December' THEN 12 ELSE 99999 END
Alternatively, if you have a date and not just the month name you can do something like:
MONTH(`Date`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@AFrancis0219 Since you have single quotes around your numbers, they are actually being read as text and being sorted alphabetically. If you remove the single quotes and the ELSE, then they will be read as numbers instead
0 -
That worked. Thanks!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive