Need help on sorting by date quarters.

[stormtrooper]

Hi there. I have 2 dates (start and end). Basically they are the start dates and end dates of the quarters (April 1 - June 30, etc.).

I have a formula that displays the 2 dates:

totext(datetime({FAC_GRANT_DTL.START_DT}),"MMMM dd&quot+"-"+
totext(datetime({FAC_GRANT_DTL.END_DT}),"MMMM dd, yyyy"

But, I need this sorted by the most recent quarter. Any ideas? Thanks.

 

[bdreed35]

Why don't you group by just your start date? This will allow it to order ascending. You can then display your formula in the group header to represent the period start and end date for the user.

~Brian

 

[stormtrooper]

I need to display the format of my formula in the group tree.

eg. April 1 - June 30, 2003

Ps. I forgot to mention this is v9.

 

[bdreed35]

If you need it display in the Group Tree, than follow these steps:

In the Report Explorer, right click the Group Header and select Change Group
Change the group field from your formula to {FAC_GRANT_DTL.START_DT}.
Chnage the section will be printed from "each day" to "each quarter".
Click the Options tab.
Select Customize Group Name Field.
Choose Use Formula as Group Name FIeld and click the X+2 button.
In the formula editor, paste your formula contents that you posted above.
Click OK and Test.

~Brian

 

[stormtrooper]

Worked, thanks!

 

[Madawc]

In Crystal 8.5, there is a command to find the quarter for any given date:
Datepart("q", {your.date})

This gives you 1, 2, 3 or 4. Could this work as an alternative solution?

Madawc Williams
East Anglia, Great Britain