Date Grouping (by Quarter)

[scan2k]

Hello,
I am grouping by a date field (Ver. 8.5) and utilizing the built in "grouping by quarter" choice from the drop down menu.
However, the data groups appear as 1/2001, 4/2001, 7/2001, 10/2001, 1/2002 etc. Can anyone suggest how to name these quarters as Q1/2001, Q2/2001, Q3/2001, Q4/2001, Q1/2002 etc.
Any help is greatly appreciated.

Thanks,
D.

 

[synapsevampire]

Create a formula for display purposes, such as:

"Q"+GroupName ({Table.Date}, "quarterly&quot

You can select the groupname field within the formula editor.

-k

 

[bdreed35]

I am sure there many ways to do this but here is one:

Create this formula and place it in your group header:

stringVar array qrt := Split(GroupName ({your_date_field}, "quarterly"),"/");

select ToNumber(qrt[1])
    case 1 : "Q1/" + qrt[2]
    case 4 : "Q2/" + qrt[2]
    case 7 : "Q3/" + qrt[2]
    case 10 : "Q4/" + qrt[2]
    default: "UNKNOWN"

~Brian

 

[scan2k]

Thanks to synapsevampire and bdreed35. I used bdreed35's method because it yielded Q1/2001, Q2/2001, Q3/2001 etc. synapsevampire's method yielded the original Q1/2001, Q4/2001, Q7/2001 etc which I was trying to get away from!! Thank you both for your contributions!!!

 

[synapsevampire]

Whooops, sorry, I didn't pay very close attention there...

Breed handled it nicely.

-k

 

[rosemaryl]

Dear Scan2k,

I use almost this exact scenario in my crystal course to teach the Choose function.

I like the choose function as it offers some simplicity.

The expression is Choose(Index,n1,n2,n3,...)

The number of choices is limited to the return of the index. In my example here that would be four!

Here you go:

//begin formula
stringvar fmt := "/" + Totext(Year({YourDateField}),"#"

Choose(datepart("q",{Incident.Open Date & Time}),
"Q1"+fmt, "Q2"+fmt, "Q3"+fmt,"Q4"+fmt)

Another nice thing about this, is say that your quarters don't run Jan to December but say rather July to June, you can simply change the text so that it "converts" the results to say what you want, for example:

//begin formula
stringvar fmt := "/" + Totext(Year({yourdatefield}),"#"

Choose(datepart("q",{your_date_field},
"Q3"+fmt, "Q4"+fmt, "Q1"+fmt,"Q2"+fmt)

I just thought it was a simple and cool way to do that.

regards,
ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.