topic Quarter Function in QlikView

Quarter Function

Anonymous — Sat, 19 Jun 2010 18:02:00 GMT

How can I extract quarter from a date column? I dont like the format of QuarterName function of QlickView. I want a simple display like Q1, Q2, Q3, Q4. Any pointers?

Thanks,

Dinesh.

Quarter Function

Anonymous — Sat, 19 Jun 2010 18:18:11 GMT

I currently use the below formula. Hopefully there is a simpler way:

if(month(UCCDATE)='Jan','Q1',

if(month(UCCDATE)='Feb','Q1',

if(month(UCCDATE)='Mar','Q1',

if(month(UCCDATE)='Apr','Q2',

if(month(UCCDATE)='May','Q2',

if(month(UCCDATE)='Jun','Q2',

if(month(UCCDATE)='Jul','Q3',

if(month(UCCDATE)='Aug','Q3',

if(month(UCCDATE)='Sep','Q3',

if(month(UCCDATE)='Oct','Q4',

if(month(UCCDATE)='Nov','Q4',

if(month(UCCDATE)='Dec','Q4')))))))))))) as Quarter,

Quarter Function

disqr_rm — Sat, 19 Jun 2010 19:26:09 GMT

Try:

'Q' & ceil(month(UCCDATE)/3)

Quarter Function

ashfaq_haseeb — Sat, 19 Jun 2010 19:57:41 GMT

Try This coad, this might help you.

'Q' & ceil(month(UCCDATE)/3)

Quarter Function

Wed, 08 Feb 2012 07:05:57 GMT

How would you do it if your quarter only starts at april

Quarter Function

Tue, 05 Jun 2012 20:59:27 GMT

This works nicely other than getting just a blank "Q" on its own. Why am I getting that?

Quarter Function

swuehl — Tue, 05 Jun 2012 22:18:03 GMT

Alex, you are only getting a 'Q' with no quarter number? Have you tried just using

ceil(month(DATE)/3) as Quarter,

to look if you got a number from that, if not, try just a month(DATE) and if this doesn't return a number than look at your DATE field. It mus be a recognozed QV date/timestamp type, i.e. must have a numerical representation.

Or am I misunderstanding your question?

Re: Quarter Function

Wed, 06 Jun 2012 15:29:03 GMT

When I just use ceil(month(DATE)/3), I get just 1, 2, 3, 4.

Would it be doing this because I am using a list box? And I'm not doing it in the script?

And no, in the expression I get this: Q, Q1, Q2, Q3, Q4. I don't need the first Q value.

Quarter Function

rwunderlich — Wed, 06 Jun 2012 15:40:37 GMT

Because some of your dates are null. To filter them out use:

=if(len(DATE)>0, 'Q' & ceil(month(DATE)/3))

-Rob

Quarter Function

Wed, 06 Jun 2012 15:43:03 GMT

Great, thanks Rob. That did the trick.

Re: Quarter Function

gaurav2017 — Wed, 28 Nov 2012 07:06:47 GMT

How about if the Calendar starts from April. Please don't show me the inline method.

I am using this below script. But it's incomplete i guess.

'Q' & Ceil(Month(Date)/4) as Quarter

Regards,

Re: Quarter Function

swuehl — Wed, 28 Nov 2012 11:55:35 GMT

There are probably several approaches,

='Q' & ceil((mod((Month(Date)+8),12)+1)/3)

='Q' & pick(Month(Date),4,4,4,1,1,1,2,2,2,3,3,3)

or using a Mapping Table

MAP:

MAPPING LOAD * INLINE [

Month, Quarter

1,Q4

2,Q4

3,Q4

4,Q1

5,Q1

6,Q1

7,Q2

8,Q2

9,Q2

10,Q3

11,Q3

12,Q3

];

MAP Quarter using MAP;

LOAD

Month(Date) as Quarter,

...

Re: Quarter Function

gaurav2017 — Sat, 01 Dec 2012 08:01:53 GMT

Swuehl,

Thanks dear, It works.

Re: Quarter Function

rwunderlich — Mon, 03 Dec 2012 19:45:26 GMT

Another alternative would be:

='Q' & Ceil(Month(AddMonths(Date,-3))/3)

-Rob

Re: Quarter Function

Mon, 25 May 2015 19:53:08 GMT

Forma mais simples, rápida e funcional.

Serviu perfeitamente para solucionar o meu problema.

Obrigado Rob.

Re: Quarter Function

Anonymous — Mon, 25 May 2015 22:27:09 GMT

What ?

Re: Quarter Function

rwunderlich — Mon, 25 May 2015 22:59:54 GMT

De nada.

-Rob

Re: Quarter Function

Anonymous — Fri, 21 Dec 2018 20:46:17 GMT

Thank you! This worked nicely for me as my company has a different Fiscal Calendar so I was able to set the Quarters as needed.

Re: Quarter Function

Ksrinivasan — Thu, 10 Oct 2019 09:07:15 GMT

Hi sir,

instead of lengthy, you can use as script as below:

'Q' & Ceil(Month(AddMonths(DATE,-3))/3) as QUA ,

here DATE means your DATE field,

then followed that another requirement may be raise as Fiscal year,

for Q1, Q2, Q3 = current year + Next year (2019-2020)

for Q4 = Last year + Current Year (2018-2019),

for that

if(QUA='Q4', Year(DATE)-1 &'-'& Year(DATE), if(QUA='Q3', Year(DATE) &'-'& (Year(DATE)+1),if(QUA='Q2', Year(DATE) &'-'& (Year(DATE)+1),if(QUA='Q1', Year(DATE) &'-'& (Year(DATE)+1),'')))) as Fiscal Year,

Result will be:

Enjoy with qlik,

K. Srinivasan.

Re: Quarter Function

Ksrinivasan — Thu, 10 Oct 2019 09:08:59 GMT

Result will be:

K. Srinivasan.