Creating Quarters using Months

[MalcolmCICWF]

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?

[#2 · Accepted solution]

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

[MalcolmCICWF]

This is not exactly the way I had envisioned doing it, but it did in fact work! thank you

[v_iyyappan]

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,

[SouthsideFinance]

Hi.. I found this post and created this field using the code but does not change the report output when I click on Q1 ... still shows all months data.  I'm missing something as I need to attach it to the field Booking Month