Month Order on Bar Chart

TMonty0319
TMonty0319 Member
in Beast Mode

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

  • GrantSmith
    GrantSmith Coach
    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!**

Answers

  • GrantSmith
    GrantSmith Coach
    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!**
  • MichelleH
    MichelleH Coach

    @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

  • TMonty0319
    TMonty0319 Member

    That worked. Thanks!

Categories