Creating Fiscal Quarter Groups 2

[bnsSteve]

Hello,

I would like to group by quarter, however, I would like to have the quarters be from, Q1=Nov-Jan, Q2=Feb-Apr, Q3=May-July, Q4=Aug-Oct.

I'm currently only using 3 fields, date field, product field and product channel field.

Any help would be great. Thanks!

 

[bnsSteve]

Hi,

Just wanted to add that the date field is in this format:
7/5/2011 9:13:11 AM

Might help.

Thanks again,
Steve.

 

[Madawc]

DatePart with "q" gives you a number 1 to 4 for the year, but this would be Jan-March etc. and I don't think you can change it.

What you could do is to add two months from the date, meaning that a November date moves to January and Quarter one. Something like

DatePart("q", (DateAdd("m", 2, {your.date}))

This is based on Crystal 2008. It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[bnsSteve]

Hi Madawc,

Thanks for the reply, you're solution worked great.

Would it be possible to make it so it reads Q1-11 or Q1-12? As in the quarter and the year? Reason being as that I realized Q1 rolls between two years, so Nov 11 - Jan 12 is Q1. Right now I have more than 1 year in my dataset so I cannot distinguish between Q1-11 & Q1-12 as well. Not sure if this is possible.

Thanks again Madawc, much appreciated.
Steve

 

[bnsSteve]

Sorry forgot to mention that I am using Version 14.0.2.364 Crystal 2011.

Thanks!

 

[lbass]

Create a formula like this:

"Q"+totext(datepart("q",DateAdd("m", 2, {your.date})),0,"")+"-"+right(totext(year(DateAdd("m", 2, {your.date})),"0000"),2)

-LB

 

[bnsSteve]

Works perfectly! Thanks again LB.

Steve.