If it's over a year ago?

[Yustrn]

Using Crystal 8.5

Here is the scenario.

Someone buys items from us once a quarter, but I need it broken out by if they bought the items over a year ago, or under a year ago

So something like

Items bought less than 1 year ago

423

Items bought more than a year ago

223

I have the dates they were bought, but don't know how to write the formula for if it's over a year be on this line, under a year be on this line.

Something like if (rot.purchasedate) < 365 = (items bought)

As your can see from the above I need HELP!

 

[synapsevampire]

Try:

If DATEDIFF("Y",(rot.purchasedate),CURRENTDATE) >= 1 THEN
"Year old"
else
"This Year"

If you need to count them, then you might use:

If DATEDIFF("Y",(rot.purchasedate),CURRENTDATE) >= 1 THEN
1
else
0

and another for the less than:

If DATEDIFF("Y",(rot.purchasedate),CURRENTDATE) >= 1 THEN
0
else
1

-k

 

[Yustrn]

Looks good, but I'd like to find a way to where I didn't have to come back in and update the current date information...it will always be standard at 1 year.

 

[lbass]

Your response is unclear. Currentdate is a function that always returns today's date, and so it will never need to be updated. The question is whether you want the year to be a year prior to today's date, or are you comparing some standard years, like calendar years or fiscal years?

If you use the datediff function, I suggest using:

if datediff("d",{rot.purchasedate},currentdate) < 365 then "< 1 Year" else
">= 1 Year"

Then group on this formula. You can then insert summaries, (count) on {table.item} to get group subtotals and grand totals.

The datediff function would give misleading information in instances like the following which would return a value of one year, even though the dates are only one day apart:

datediff("Y",Date(2003,12,31),Date(2004,01,01))

-LB