5 Replies Latest reply: Jul 29, 2011 8:50 AM by mehmet.temel RSS

    Dynamic month naming

      Hi All,

       

      I’ve got a sales data with headers (expressions);

       

      M0 – M1 – M2 – M3 – M4 – M6 – M7 …

       

      M0 is current month, M1 is last month, M2 is the month before, etc..

       

      How can I turn these into month names? Basically I’m askingfor dynamic month naming.

       

      I've tried M0 = month(today()) which works then tried M1 = month(today()-1 which didn't work.. any idea??

       

      Thanks,

      Mem

        • Dynamic month naming
          Dennis Hoogenboom

          You could try something like this in your script:

           

             (Month(Today())- (right(Monthfield,1))) as Month,

           

          This will return number of the right month.

           

          But you have to modify it to your own script, but maybe you can start from here.

           

          And keep in mind that you could better solve it on a MonthYear field in stead a MonthField.

            • Dynamic month naming

              Hi Dennis,

               

              Unfortunately that didn't work. But I've managed to get a number output by writing;

               

              =Month(today())-Month(30) This gives a result of 6 (Jun)

               

              =Month(today())-Month(60) This gives a result of 5 (May)

               

              =Month(today())-Month(90) This gives a result of 4 (Apr)

               

              and so on ...

               

              If I could turn these numbers into month names that would solve my problem I guess..

               

              Cheers,

              Mem

                • Re: Dynamic month naming
                  Stefan Wühl

                  Hi,

                   

                  try

                  =month(makedate(2011,month(today())-1,1))

                   

                  for M1

                  =month(makedate(2011,month(today())-2,1))

                   

                  etc.

                   

                   

                  P.S:

                   

                  I would not use "month(today()) - month(30)" to get the previous month, but month(today())-1

                   

                  Both result in 6 (as of today), buth month(30) means month('1900-01-29') which is Jan (or 1), but I think this is misleading and depending on start of internal calendar.

                   

                  In above expression, I make a date (because month() expects a date, using calculated month number and arbitrary year and day number (i.e. 2011, 1)

              • Re: Dynamic month naming

                Thanks both,

                 

                They both do the job except Swuehl's method stops working for "-7" (Dec-2010) since the data is for 18 months.

                 

                Sunil's works perfectly.

                 

                Cheers,

                Mem