3 Replies Latest reply: Sep 4, 2013 5:48 PM by Iyyappan v RSS

    Creating Quarters using Months

    Jeremy Schmitt

      I have created a field called PoolMonth using the MONTH function (MONTH(poolstart) as PoolMonth). In the next table I am trying to use PoolMonth in a MIXMATCH fuction to breakout months by business quarter. My script is completing and creating a field called "Quarter", except there is nothing in it. I know the PoolMonth field contains the month as Jan, Feb, Mar... ect, but it seems I cannot get the formatting correct to group the months in each quarter. I have tried writing the month as Jan, January, 01, 1, and nothing is working. Can anyone point out what the issue is?

       

      Untitled.jpg

        • Re: MONTH function with MIXMATCH
          Charles Crous

          Hi Jeremy,

           

          If I understand the problem correctly, you are actually after a piece of code that will provide you with the correct quarter for a given month...

           

          If so, then try this:

           

          'Q' & ceil(PoolMonth / 3) as Quarter

           

          April is the 4th month and so 4 divided by 3 will give you 1.33, and the ceil(ing) function rounds it up to 2. So April will be allocated to the second quarter (Q2) and October(10/3) will be round up to Q4.

           

          Hope This helps,

          Charles

          • Re: MONTH function with MIXMATCH
            Iyyappan v

            Hi,

             

            Do u want Q1,Q2, Q3 and Q4 like this ? If u want that use like

            'Q' & ceil(PoolMonth / 3) as Quarter (or)

             

            In script :

            Month(PoolMonth) * 1 as MonthKey,

            MonthMapping:

            LOAD * INLINE [

                MonthKey, Month, Quarter

                1, Jan, Q1

                2, Feb, Q1

                3, Mar, Q1

                4, Apr, Q2

                5, May, Q2

                6, Jun, Q2

                7, Jul, Q3

                8, Aug, Q3

                9, Sep, Q3

                10, Oct, Q4

                11, Nov, Q4

                12, Dec, Q4

            ];

             

            Regards,