topic Re: Quarters in QlikView in QlikView

Quarters in QlikView

Thu, 16 Oct 2014 16:33:26 GMT

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.

Re: Quarters in QlikView

anbu1984 — Thu, 16 Oct 2014 16:55:30 GMT

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

Re: Quarters in QlikView

jaimeaguilar — Thu, 16 Oct 2014 16:59:18 GMT

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

Re: Quarters in QlikView

anbu1984 — Thu, 16 Oct 2014 17:07:02 GMT

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

Re: Quarters in QlikView

Thu, 16 Oct 2014 17:56:22 GMT

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!

Re: Quarters in QlikView

Fri, 17 Oct 2014 01:47:12 GMT

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

Thank you.

Re: Quarters in QlikView

anbu1984 — Fri, 17 Oct 2014 06:34:25 GMT

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.