Formula for last 4 or 6 months of data

[GaryCWL]

Backend: SQL Server 2000
Crystal Version: BO Crystal XI

Is it possible to set up a formula that returns the last six months worth of data? I tried something like:

{order.date} in DateTime (CurrentDate - 180) to DateTime (CurrentDate)

But, the problem with that, is depending on when the report is run or how many days in a month, I may not get all the data for latest month. For example, the above formula will return data from October 2006 - April 2007; however, October's data does not include an entire months worth.

Please help.
Thanks,
Gary

 

[Madawc]

Try

DateDiff ("m", {date1},  currentdate) <= 6

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

What you're probably after is the last COMPLETED 6 months of data.


{table.date} >= dateserial(year(minimum(lastfullmonth)),month(minimum(lastfullmonth))-6,1)
and
{table.date} <= lastfullmonth

-k

 

[GaryCWL]

Thanks for you responses. Both formulas worked GREAT.