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

Why doing such a long process. Instead try doing this.

Goto queryeditor, duplicate your month column and transform to MonthNumber and rename it any name and apply.

Now in modeling tab , use this new named column to sort the month column.

rarora20's avatar
rarora20
Regular Visitor
8 years ago

pxg08680 how do I transform to month number. I am new to this. Help me with some steps. I have a month name column. Thank you!

  • pxg08680's avatar
    pxg08680
    Resolver III
    8 years ago

    rarora20

    Do these steps

     

    STEP1: Goto Home, EditQueries.

    STEP2: Select the Month column that you have, right click and goto transform-->Month-->Month. This gives you month numbers.

    STEP3: For same column right click and rename it to MONTHNUMBER.

    STEP4: Close& Apply.

    STEP5: Select the Month column from your data and got to modeling tab, sort column, select the newly created column, sort by MONTHNUMBER.

     

    This should work.

     

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