Forum Discussion

rarora20's avatar
rarora20
Regular Visitor
8 years ago
Solved

Sorting by Month Number

Hello,   I have a month and year column. I have created a conditional column which gives me the month number based on the month name. When I try to sort the data by the conditional column(Number), ...
  • parry2k's avatar
    parry2k
    8 years ago

    rarora20 make sure to change date type for your new column M# to whole number, it is string , you will not get correct sort because with string sort will go like this

    1

    10

    11

    12

    2

    3

    4

    5

    6

    7

    8

    9

     

    and that is the reason you see Jan, Oct, Nov, Dec and so on..

pxg08680's avatar
pxg08680
Resolver III
8 years ago

rarora20

I am sorry, I was thinking that your data has a date column. What I posted works for Date fields.

You can do a switch kind of statement.

STEP1: In EditQuery, duplicate your month column to M# or any name. Close & Apply.

STEP2: Now create a new column this way...

column = IF(Table1[M#] = "January",1,

                IF(Table1[M#] = "Feb",2,

                IF(Table1[M#] = "March",3,

                IF(Table1[M#] = "April",4,
                IF(Table1[M#] = "May",5,

                IF(Table1[M#] = "June",6,

                IF(Table1[M#] = "July",7,

                IF(Table1[M#] = "August",8, 

                IF(Table1[M#] = "Sep",9,

                IF(Table1[M#] = "Oct",10,

                IF(Table1[M#] = "Nov",11,

                IF(Table1[M#] = "Dec",12 ))))))))))))

 

Now sort you Month column with the newly create column.

 

rarora20's avatar
rarora20
Regular Visitor
8 years ago

Getting the same results. It's weird because if you look at the screenshots, after sorting only Oct Nov Dec are unsorted rest everything is sorted. 

  • pxg08680's avatar
    pxg08680
    Resolver III
    8 years ago

    rarora20

    Can you share your data..

    • parry2k's avatar
      parry2k
      Super User
      8 years ago

      rarora20 make sure to change date type for your new column M# to whole number, it is string , you will not get correct sort because with string sort will go like this

      1

      10

      11

      12

      2

      3

      4

      5

      6

      7

      8

      9

       

      and that is the reason you see Jan, Oct, Nov, Dec and so on..

      • sevenhills's avatar
        sevenhills
        Super User
        8 years ago

         

        In the query editor, select your query, select your month (ie., number) column, right click, change type, click whole number. 

         

        You can do the same using Data Type as "whole number".

         

        Basically, when you added conditional column, it shows Data Type as Any. You need to fix it as Whole number.

         

        After this, Verify: Data Tab in the main app: Make sure for the month: january, feb ... column, sort by column is still the number column. 

         

        Save. Now the order should be good and recreate the chart (if it is not fixed).