Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
MalcolmCICWF
Contributor II

Creating Quarters using Months

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

1 Solution

Accepted Solutions
Not applicable

Re: MONTH function with MIXMATCH

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

3 Replies
Not applicable

Re: MONTH function with MIXMATCH

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
Contributor II

Re: MONTH function with MIXMATCH

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

v_iyyappan
Valued Contributor

Re: MONTH function with MIXMATCH

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,

Community Browser