topic Re: MONTH function with MIXMATCH in QlikView

Creating Quarters using Months

MalcolmCICWF — Wed, 04 Sep 2013 20:25:08 GMT

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?

Re: MONTH function with MIXMATCH

Wed, 04 Sep 2013 20:56:18 GMT

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

MalcolmCICWF — Wed, 04 Sep 2013 21:47:04 GMT

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

Re: MONTH function with MIXMATCH

v_iyyappan — Wed, 04 Sep 2013 21:48:07 GMT

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,

Re: MONTH function with MIXMATCH

SouthsideFinance — Sat, 24 Feb 2024 14:09:02 GMT

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