Comparing this year’s data to last year’s data 1

[shannonlp]

I’ve got a report that compares the current month’s data to last year’s data for the same period (example: comparing August 2009 to August 2008).

I set this up by using the following in the select expert which takes care of it:

{INVOICE.INVOICE_DATE} in {?Start Date} to {?End Date}
or
({INVOICE.INVOICE_DATE} >= date(year({?Start Date})-1,month({?Start Date}),day({?Start Date}))
and
{INVOICE.INVOICE_DATE} <= date(year({?End Date})-1,month({?End Date}),day({?End Date})))

I now need to schedule this report to run on the first of each month. This would mean that instead of having a parameter {?Start Date} & {?End Date}, this needs to be hard coded. For example, on Oct 1, this report will need to retrieve data for 9/1/2009 – 9/30/2009 AND 9/1/2008 and 9/30/2009.

Any help would be greatly appreciated.

 

[lbass]

(
{INVOICE.INVOICE_DATE} in lastfullmonth
or
(
{INVOICE.INVOICE_DATE} >= dateserial(year(currentdate)-1,month(currentdate)-1,1)
and
{INVOICE.INVOICE_DATE} <= dateserial(year(currentdate)-1,month(currentdate),1)-1
)
)

-LB

 

[shannonlp]

Great - thanks!

I was trying

numbervar d := datepart("d", CurrentDate);
numbervar m := datepart("m", CurrentDate);
numbervar y := datepart("yyyy", CurrentDate);
{INVOICE.INVOICE_DATE}in dateserial(y, m-1, 1) to dateserial(y, m, 1 - 1)
or
{INVOICE.INVOICE_DATE}in dateserial(y-1, m-1, 1) to dateserial(y, m, 1 - 1)

Your formula makes more sense.