3 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    Month to Number conversion

    TomVP _

      I have named months like (January) in one of my data columns and I wish to sort by months.  I figured I could convert the named month to a number and sort on that column.  Other than creating a 12 step imbeded "IF" statement to change to numbers is there any thing else I can do.

        • Month to Number conversion
          Tom Whiteside

          Thomas, go back to one of the Forum's early [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000009#000000"]posts[/url] and see how to create a calculated month field to both sort and group on.  It is much more useful than juggling nested IF's.

           

          Best wishes,

           

          [size="1"][ May 19, 2006, 12:33 PM: Message edited by: Todd Niemi ][/size]

          • Month to Number conversion
            Steve Caiels

            Hi,

             

            Tom's previous solution seems to be the best if you want to work with full dates.

             

            But if you have full dates, but are only intested in the number of the month, then create a numeric calculated field with an expression of month(fieldname).

             

            Or if you  only[/i] have the month and want to sort correctly, then create a calculated field as you suggested.  If you have V6 or above, a lookup will be much easier to control than a nested IF.

             

            Another method of getting a "bespoke" sort order is to create a numeric calculated field using INSTR.  INSTR reports the position of the found string, so if your original field is called month, then the expression [font="courier"]INSTR(month,"January February march april may june july august september october november december") /font[/quote]will locate the contents of the month field in the list and generate a field you can sort on.

             

            Regards,

            Steve

             

            [size="1"][ December 01, 2003, 09:48 AM: Message edited by: Steve Caiels ][/size]

            • Month to Number conversion
              Grant Perkins

              Tom,

               

              Just to add to the other replies, having had a moment of inspiration during an experiment with a similar report to yours, there is another approach if you have Monarch V6 or V7. At least there is if you are working with Summaries.

               

              Check out the Help entry for "Key Field Dialogue" and particlarly the section related to "Specified Values". There you will find the problem of order of Months specifically mentioned.

               

              In summary, if you set up a list of specified values for a summary keyfield (basic use is as a filter for summaries) BUT include all of the months in your report and then put them into the order you require, you will be in a position to use the same order as a sort for the summary.

               

              So you could have a regular file available to simply paste the entries (need to check if that works ....).

               

              The report I have been looking at included the year number (E.G.  Jan 03). In which case you could make the model future proof (to some extent) by making the list repeat for each year which might be of interest.

               

              After grappling with the same sort of problem as yours a few times over the years, I had one of those   :rolleyes:  moments a couple of days ago when I realised that I was often stranded back in V5 mode having missed the significance of some of the less frequently used (by me at least) features made available in more recent versions.

               

              I thought it worth posting since your question makes this post a good vehicle for sharing the input for future enquirers.

               

              I hope this is of interest.

               

              Grant

               

               

              Originally posted by TomVP:

              I have named months like (January) in one of my data columns and I wish to sort by months.  I figured I could convert the named month to a number and sort on that column.  Other than creating a 12 step imbeded "IF" statement to change to numbers is there any thing else I can do. /b[/quote]