Quarters in QlikView

[#1]

Hi,

My calendar quarters are as follows:

Calendar Quarter          Company Quarter

1                                  3

2                                  4

3                                  1

4                                  2

I am trying to compare the company quarter to the quarter that is in the database records. I used Ceil(Month(today()) /3 for the current quarter which is the calendar quarter but the calendar current quarter is not the same as the company quarter. How do I change that?

Thanks.

[anbu1984]

If(Mod(Ceil(Month(today()) /3)+2,4),Mod(Ceil(Month(today()) /3)+2,4),4)

[jaimeaguilar]

Hi,

if you already have 1,2,3,4 as values for Calendar Quarter field, you can use either an inline table or pick function in script:

pick([Calendar Quarter], 3,4,1,2) as [Company Quarter]

regards

[anbu1984]

=Pick(Ceil(Month(today()) /3),3,4,1,2)

[#5]

RS:

Anorher option is a mapping load,

Map_Quarter:

Mapping

LOAD * INLINE [

    Quarter, CompanyQuarter

    1, 3

    2, 4

    3, 1

    4, 2

];

Load

Quarter,

ApplyMap('Map_Quarter',Quarter) AS CompanyQuarter

From YourCalendar

There you will have a relation between the Calendar Quarter and Company Quarter.

Hope it helps!

Regards!

[#6]

Can you please explain what Mod(Ceil(Month(today()) /3)+2,4 does?

Thank you.

[anbu1984 · Accepted solution]

Mod(Ceil(Month(today()) /3)+2,4)

Ceil(Month(today()) /3 -- Gives Calendar quarter

Mod(1+2,4) = 3

Mod(2+2,4) = 0 -- If(Mod(Ceil(Month(today()) /3)+2,4),Mod(Ceil(Month(today()) /3)+2,4),4) returns 4

Mod(3+2,4) = 1

Mod(4+2,4) = 2

From QV Help

mod(x1 , x2)

Mathematical modula function. Both parameters must have integer values. x2 must be greater than 0. The result is the non-negative remainder of an integer division.