4 Replies Latest reply: Sep 16, 2013 10:47 AM by Stephen Matthews RSS

    Sorting a list box with months

      Hi,

       

      I'm gonig slightly mad with this problem. I have created the field below:

       

      Month(Date(Date(AddMonths(Date#(MonthYear,'MMM YYYY'),[Mth Report Period])),'MMM YYYY')) as ReportMonth1,

       

      MonthYear e.g. 'Apr 2012'

      [Mth Report Period] is a number e.g. 1

       

      This would give the result Mar

       

      I then use the following field to get a number:

       

      lookup('ID','Month',Text(Month(Date(Date(AddMonths(Date#(MonthYear,'MMM YYYY'),[Mth Report Period])),'MMM YYYY'))),'MonthLookup') as MonthSort,

       

      the lookup table basically starts with Apr as 1 and ends with Mar as 12, i then sort the data in the script by MonthSort.

       

      I am then puting the field ReportMonth1 in a list box, but I cant get Apr first and Mar last.

      If I tick the box to say load order, or do an expression =Only(Data.MonthSort).

       

      Can someone please help as this is driving me crazy!!

       

      Thanks in Advance,

       

      Simon.

       

        • Re: Sorting a list box with months

          Hi

           

          I use the expression within the sort tab.  I see you used a field called ID to create the presentation order.

           

          Alternatively, you could order your load and use this to drive the sort.

           

          I hope this helps and I haven't missed the point.  Let me know how you get on.

           

          Regards


          Steve

            • Re: Sorting a list box with months

              Hi Steve,

               

              Thanks for the quick reply.

               

              In my scrpit i do an order by on the field MonthSort, which when you look at the data table puts all the apr first, may second, jun third etc...

               

              But then when I tick the box to sort by original load it does not come back in the order expected. I then try to use the expression to sort by MonthSort, but that does not work either. I would be greatful, if you could look at my current document and try and get it to sort.

               

              Once again, thank you for the quick reply.

               

              Cheers

               

              Simon