Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If it's over a year ago?

Status
Not open for further replies.

Yustrn

Instructor
Joined
Jun 19, 2002
Messages
42
Location
US
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!
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top