LastFullQuarter Function for CR9

[JoyCR9]

In Crystal Reports 9, there are predefined functions such as "LastFullMonth" and "LastFullWeek".

However, there is not a "LastFullQuarter". I am looking for a formula that would be hands off (ie: no manual date range entry) that I can use when I schedule the report with Visual Cut.

I don't need help with Visual Cut, but need help writing the formula for the "LastFullQuarter".

Regards,
Joy

 

[synapsevampire]

I'll assume that you mean a calendar quarter.

Try something like:

if Month(currentdate) in [1,2,3] then
Month({table.DATEFIELD}) in [10,11,12]
and
Year({table.DATEFIELD}) = Year(currentdate)-1
else
if Month (currentdate) in [4, 5, 6] then
Month({table.DATEFIELD}) in [1,2,3]
and
Year({table.DATEFIELD}) = Year(currentdate)
else
if Month(currentdate) in [7, 8, 9] then
Month(table.DATEFIELD}) in [4,5,6]
and
Year({table.DATEFIELD}) = Year(currentdate)
else
if Month(currentdate) in [10, 11, 12] then
Month({table.DATEFIELD}) in [7,8,9]
and
Year({table.DATEFIELD}) = Year(currentdate)

-k

 

[JoyCR9]

Thanks for the reply.

I created a formula called @Quarter:

if Month(currentdate) in [1,2,3] then
Month({SERVICE_CALL.CREATE_DT}) in [10,11,12]
and
Year({SERVICE_CALL.CREATE_DT}) = Year(currentdate)-1
else
if Month (currentdate) in [4, 5, 6] then
Month({SERVICE_CALL.CREATE_DT}) in [1,2,3]
and
Year({SERVICE_CALL.CREATE_DT}) = Year(currentdate)
else
if Month(currentdate) in [7, 8, 9] then
Month({SERVICE_CALL.CREATE_DT}) in [4,5,6]
and
Year({SERVICE_CALL.CREATE_DT}) = Year(currentdate)
else
if Month(currentdate) in [10, 11, 12] then
Month({SERVICE_CALL.CREATE_DT}) in [7,8,9]
and
Year({SERVICE_CALL.CREATE_DT}) = Year(currentdate)

AND IT WORKS PERFECTLY....thank you, thank you, thank you

 

[bdreed35]

How are you using this formula? If you are using it in the record selection, it won't get passed to the database in the where clause of the SQL due to the use of the month and year functions.

You can alter it to get it to pass to the db like this:

{SERVICE_CALL.CREATE_DT} in
switch
(
Month(currentdate) in [1,2,3], date(Year(currentdate)-1,10,1) to date(Year(currentdate)-1,12,31),
Month(currentdate) in [4,5,6], date(Year(currentdate),1,1) to date(Year(currentdate),3,31),
Month(currentdate) in [7,8,9], date(Year(currentdate),4,1) to date(Year(currentdate),6,30),
Month(currentdate) in [10,11,12], date(Year(currentdate),7,1) to date(Year(currentdate),9,30)
)



~Brian

 

[synapsevampire]

Good point, Brian.

-k