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..

rarora20's avatar
rarora20
Regular Visitor
8 years ago

After right clicking I dont see the month--> month option. 

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..