Last Quarter With A Given

[dbinfoweb]

Hello, hope someone can help. I want to get data for last quarter with any given date. E.g. for today's date, I would like to run the report for 10/1/2002 up to 12/31/2002. For next Thursday, I still want to run the report for the same quarter. However, for two months from now (any date in April), I would run the report for first quarter of this year (1/1/2003-3/31/2003) and so on. The field this logic is based on is the "Sale Date" in the table. I am just wondering how I can achieve this. Thanks for any help in advance.

 

[chelseatech]

Formula for start of Last Qtr...

If month({table.saledate}) in [1,2,3] then
Date(year({table.saledate})-1,10,1) else
If month({table.saledate}) in [4,5,6] then
Date(year({table.saledate}),1,1) else
If month({table.saledate}) in [7,8,9] then
Date(year({table.saledate}),4,1) else
Date(year({table.saledate}),7,1)

Formula for End of Last Qtr
If month({table.saledate}) in [1,2,3] then
Date(year({table.saledate})-1,12,31) else
If month({table.saledate}) in [4,5,6] then
Date(year({table.saledate}),3,31) else
If month({table.saledate}) in [7,8,9] then
Date(year({table.saledate}),6,30) else
Date(year({table.saledate}),9,30) Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[synapsevampire]

I use the following model:

Create 2 formulas as below, and then reference them in the record selection criteria:

//@LastQuarterStartDate:
If (Month(CurrentDate) < 4) Then
DateTime(Year(CurrentDate)-1,10,1,0,0,0)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)-5,1,0,0,0)

//@LastQuarterEndDate:
If Month (CurrentDate) < 4 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,23,59,59)-1

Record Selection Criteria:

{Table.SaleDate} >= @LastQuarterStartDate
and
{Table.SaleDate} <= @LastQuarterEndDate

This will pass the SQL to the database.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dbinfoweb]

Good job! This is great! It really works! Thanks you so much for the enlightening logic. I found this forum so technically educational and helpful. Thanks again for all your assistance and enlightenment.