Quarter Formula

[etarc]

Hi all! I am on XI Release 2 and am trying to find a way to get all data greater than the start of the current quarter for a simple report. For example, the current date is 5/29/2007 so I want the report to bring back data where the {TABLE.DATE} > 4/1/2007 . I have tried going with where DatePart("q",{TABLE.DATE}) >= DatePart("q",CurrentDate) and the same for the year, but I end up with:
2Q07
3Q07
4Q07
2Q08
3Q08
4Q08
2Q09...
where I will never get the first quarter.

Any ideas would be greatly appreciated.

 

[synapsevampire]

Try:

if month(currentdate) in 1 to 3 then
{table.date} >= cdate(year(currentdate)),1,1)
else
if month(currentdate) in 4 to 6 then
{table.date} >= cdate(year(currentdate)),4,1)
else
if month(currentdate) in 7 to 9 then
{table.date} >= cdate(year(currentdate)),7,1)
else
if month(currentdate) in 10 to 12 then
{table.date} >= cdate(year(currentdate)),10,1)

Brute force...

-k

 

[IdoMillet]

Year({TABLE.DATE})>Year(CurrentDate)
OR
(
DatePart("q",{TABLE.DATE}) >= DatePart("q",CurrentDate) AND Year({TABLE.DATE})=Year(CurrentDate)
)

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[etarc]

thanks! tried both just to see and both are hits.

 

[synapsevampire]

Ido's is shorter, but it won't pass the criteria to the database, so it will be slower.

Test this using Database->Show SQL Query.

The WHERE clause will demonstrate what criteria is passed, year() and datediff() won't make it.

-k