4 Replies Latest reply: Sep 28, 2011 5:40 PM by Ashley Knight RSS

    Changing a Numeric value for Month to text

      Hello

      I have a dimension in my report for Month that shows up as a number 1 through 12.

      How do I get it to show as text, January, February, etc.

      Can I do this by making a calculated dimension.

      If so anyone know how.

      Thanks Ashley

        • Re: Changing a Numeric value for Month to text
          stefano santini

          Hi,

          u can use a table like this:

           

          MonthDesc:

          LOAD * INLINE [

              month, des

              1, Jen

              2, Feb

              3, Mar

              4, Apr

              5, May

              6, Jun

              7, Jul

              8, Aug

              9, Sep

              10, Oct

              11, Nov

              12, Dec

          ]

          where exists(month);

           

          I supposed that your numeric month fileld is named "month", so there will be a key by month between number and description.

          With the 'where' you load only description of the month loaded from your sources.

           

          C u,

          Stefano.

            • Changing a Numeric value for Month to text

              My field is called [Incident Month]

              Where do I put that in the script you sent me?

              Thanks for your help

              Ashley

                • Re: Changing a Numeric value for Month to text
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  What Stefano suggests will work. I even use a Dual() load inline instead, but it's fine. But what about one code line based on a variable you already have in your script and, if you dont, you can easily create it? Check the top part of your Main tab in your script. The frist few lines are environment variables used to set the money, date and timestamp formats, separators and so.

                   

                  Ok, create a new one with the name of the months as you wish

                   

                  SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';

                   

                  Now, in your calendar table use the SubField() function to get the correspoding value to your numeric month value

                   

                  SubField('$(LongMonthNames)', ';', Month(DateField)) AS MonthName,

                   

                  And you are done! This way you don't need to use an inline table to link to your calendar table. You can even use the MonthNames variable that already exists in your script and no need to create a new one. And you can even change the language for the months or use two variables with month names, (for example, Spanish and English).

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica