5 Replies Latest reply: Jul 12, 2017 12:51 PM by SOWJANYA MOOLA RSS

    Month/Year date field in date sort order

    Steve Zagzebski

      I have a Month/Year like:

       

      Apr-13

      Aug-13

      Jan-13

      Sept-13

      etc.

       

      No matter what I have tried I can't get this to sort in date order. Seems like it should be simple but it keeps doing it alphabetically and sometimes in an order I don't even understand.

       

      Any help?

       

      Thanks, Steve:

       


        • Re: Month/Year date field in date sort order
          jagan mohan rao appala


          Hi Steve,

           

          If you arriving this month dimension from datefield then try like this

           

          LOAD

          *,

          Dual(Date(DateFieldName, 'MMM-YY'), MonthEnd(DateFieldName)) AS MonthName

          FROM TableName;

           

          Now in Sort tab select Number and Asc/Desc as your wish.

           

          Hope this helps you.

           

          Regards,

          jagan.

          • Re: Month/Year date field in date sort order
            Prem Kumar Thangallapally

              LET varMinDate = Num(MakeDate(2001,1,1));

            LET varMaxDate = Num(MakeDate(2007,12,31));

            LET vToday = Num(today());

             

             

            //*************** Temporary Calendar ***************

            TempCalendar:

            LOAD

              $(varMinDate)+RowNo()-1 AS Num,

              Date($(varMinDate)+RowNo()-1,'DD/MM/YYYY') AS TempDate

            //AUTOGENERATE($(varMaxDate)-$(varMinDate)+1);

            AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

             

             

            //*************** Master Calendar ***************

            MasterCalendar:

            LOAD Date(TempDate,'DD/MM/YYYY') AS %CommonCalendarLink,

                 week(TempDate) AS Week,

                 year(TempDate) AS Year,

                 QuarterName(TempDate) AS QuarterName,

                 if(left(QuarterName(TempDate),7)='Jan-Mar','Q1',

                  if(left(QuarterName(TempDate),7)='Apr-Jun','Q2',

                  if(left(QuarterName(TempDate),7)='Jul-Sep','Q3','Q4'))) as Quarter,

                 month(TempDate) AS Month,

                 day(TempDate) AS Day,

                 weekday(TempDate) AS WeekDay,

               

                 date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

                 week(TempDate)&'-'&Year(TempDate) AS WeekYear,

                 Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

              Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

            RESIDENT TempCalendar 

            ORDER BY TempDate Asc;

             

             

            Drop table TempCalendar;

            • Re: Month/Year date field in date sort order
              Iyyappan v

              Hi,

               

              It can be done both script and design.

               

              USE THE DUAL FUNCTION

                        The Dual function can often be used to solve trickier problems. The Dual function lets you

              specify both a numeric value as well as which text to associate with this value.

               

                   For instance, if you want to use the week number, but sorted correctly also over the change

              of the year, then you should use the date number as numeric value but display the week

              number, optionally together with the year:

              For example :

              Dual( Week( Date ), WeekStart( Date ) ) as YearWeek

               

              Dimension:

              Dual(Date(DateFieldName, MonthEnd(DateFieldName))    

               

              Sort tab:

              Numeric Value : Asc

               

              Note : DateFieldName is MMM-YY format otherwise use like this Dual(Date(DateFieldName, 'MMM-YY'), MonthEnd(DateFieldName))

               

              Hope its help,

               

              Regards,