Last 12 complete months

[Gazzamuffin]

Can anyone suggets a Formula to return the last 12 full months. So I need to exclude all data from the current month.
Cheers

 

[dgillz]

try this:

{datefield) in Dateadd("y",-1,Minimum(Monthtodate)) to minimum(MonthtoDate)-1



Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[Gazzamuffin]

It is not recognising the "y",-1 part of the dateadd function as the currenty year -1. Instead it is just returning the minimum monthtodate-1 twice.

 

[Naith]

Change "y" to "yyyy"

 

[mbarron]

You could also use:

DateDiff("m",{datefield},currentdate) in 1 to 12 Mike

 

[Gazzamuffin]

Thanks changing it to "yyyy" worked.

 

[synapsevampire]

If you're using this in the record selection criteria, make sure that it's getting passed to the database by using the Show SQL Query.

Crystal is very picky about how SQL is constructed, dgillz's solution won't work for datetimes, which is the most common date type these days, and mbarron's probably will not pass the SQL.

And be cautious of how Crystal does functions like this, especially when using datetimes, I think that you'll find a slight problem in the SQL generated for the < side of this on dgillz's solution (unless you run it at 12:00:01):

av_atsPerss.&quot;dtmBirthDate&quot; >= {ts '2001-08-01 00:00:00.00'} AND
av_atsPerss.&quot;dtmBirthDate&quot; < {ts '2002-07-31 00:00:01.00'}

I generally write my own formulas for this sort of thing, especially in the record selection.

You can code a formula for the lower and upper bounds, then reference the formulas in the record selection criteria:

End Range Formula:
datetime(year(dateadd(&quot;m&quot;,-1,currentdate)),month(dateadd(&quot;m&quot;,-1,currentdate)),day(dateadd(&quot;m&quot;,-1,currentdate)),12,59,59)

And use a <= in the record selection as in:

{MyDateField} <= {End Range Formula}

Adjust the time portion to 0,0,0 and the -1 dateadd to -12 to create a Start Range Formula:

Start Range Formula:
datetime(year(dateadd(&quot;m&quot;,-12,currentdate)),month(dateadd(&quot;m&quot;,-12,currentdate)),day(dateadd(&quot;m&quot;,-12,currentdate)),0,0,0)

Sometimes you can use the above referenced formulas within the record selection criteria, but it's safer to create them in formulas. I've found that it gives the highest % of pass through, and NEVER use any references to formulas with variables in the record selection criteria, this will almost always kill the pass through.

Crystal functions are useful, but test...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com