16 Replies Latest reply: May 25, 2015 6:59 PM by Rob Wunderlich

Quarter Function

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

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

Try:

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

• Quarter Function

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

• Quarter Function

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

• Quarter Function

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

• Quarter Function

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

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

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

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

-Rob

• Quarter Function

Great, thanks Rob. That did the trick.

• Re: Quarter Function

Forma mais simples, rápida e funcional.

Serviu perfeitamente para solucionar o meu problema.

• Re: Quarter Function

What ?

• Re: Quarter Function

-Rob

• Re: Quarter Function

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

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:

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;

Month(Date) as Quarter,

...

• Re: Quarter Function

Swuehl,

Thanks dear, It works.

• Re: Quarter Function

Another alternative would be: