Grouping Reports by Quarter of ANY Calendar Year

[DocFKNx]

I recently did a report that had to be grouped quarterly (but NOT for the current calendar year). Crystal included starting at some version built-in functions for this but they only work with the CURRENT calendar year. Not all that handy. There are probably several approaches to this but this one finally became the solution. Group on this formula (Basic Syntax):

If Month( {TABLE.DATEFIELD} ) >= 1 AND Month ( {TABLE.DATEFIELD} ) <= 3 Then
Formula = &quot;1ST QUARTER&quot;
ElseIf Month( {TABLE.DATEFIELD} ) >= 4 AND Month ( {TABLE.DATEFIELD} ) <= 6 Then
Formula = &quot;2ND QUARTER&quot;
ElseIf Month( {TABLE.DATEFIELD} ) >= 7 AND Month ( {TABLE.DATEFIELD} ) <= 9 Then
Formula = &quot;3RD QUARTER&quot;
ElseIf Month( {TABLE.DATEFIELD} ) >= 10 AND Month ( {TABLE.DATEFIELD} ) <= 12 Then
Formula = &quot;4TH QUARTER&quot;
End If

By using the numeric as the first character of the group sort it comes out 1, 2, 3, 4. I tried creating 4 groups and several other approaches and none of them really worked except this one. I'd be interested to hear any other approaches though!

X!

 

[rhinok]

Your formula only works if you look at a single calendar year's data (unless you intend to group by Quarter regardless of year). If you need to group by Quarter for overlapping years (fiscal year or more than 12 month's data) then you also need to include the year as part of the grouped value. Following is a formula that takes overlapping years into account:

//@Quarter Formula
NumberVar MonVar;
StringVar YrText;
StringVar QtrVar;

//Identifies the numeric Month Value of the specified date
MonVar := Month({TABLE.DATEFIELD});

//Converts the Year Value of the specified date into a 4-digit string
YrText := Left(ToText(Year({TABLE.DATEFIELD}),0),1) + Right(ToText(Year({TABLE.DATEFIELD}),0),3);

//Creates a string representation of the Year and Quarter in YYYY/QQ format for easy sorting and grouping.
//Change the month ranges to account for your fiscal quarters
//If you are using < CR 8 then you will need to substitute an If-Then-Else statement for the Case statement.
QtrVar := Select MonVar
Case 1 to 3 : YrText + '/' + 'Q1'
Case 4 to 6 : YrText + '/' + 'Q2'
Case 7 to 9 : YrText + '/' + 'Q3'
Case 10 to 12 : YrText + '/' + 'Q4'
End;

//The end result...
QtrVar

If you need to calculate Quarters based on 4-4-5 Accounting Months please refer to the following FAQ: faq149-1961