Checking records by date and by age.

[dlongnecker]

I'm trying to create a report that will check to see if a work order is open (CLoSeD DATE = "&quot and if the OPEN DATE is less that 7, 8-14, and 21 days and more.

Below is the formula that I started working with, but the results vary and I'm not sure how to proceed. The numbers aren't coming up right and I've yet to find a rhyme or reason to it EXCEPT that the (CurrentDate - 14) and '- 14' isn't recognized (I'm not sure how Crystal handles dates yet).

If IsNull({tasks.CLSDDATE}) and {tasks.OPENDATE} = (CurrentDate - 14) then
1
else
0

Using a Summary field, I'm counting to get my total.

Any ideas?

Thanks in advance!

-David

David R. Longnecker
Product Support/Tech III
A+ Certified, Compass PDSE
USD 259, Wichita Public Schools
dlongnecker@usd259.net

 

[BlurredVision]

Just change your = to '<='

IF ISNULL({PROBLEMS.CLOSE_DATE}) and {PROBLEMS.OPEN_DATE} <= currentdate -14 then
1 else 0

Brian

http://www.crystalinfo.net

 

[dlongnecker]

Brian-

Geez.. that was blind of me. Okay.. I modified it to, for the other collumns, to check for being between ranges, the following formula is returning just zero's. Am I being blind again and just missing it?


IF IsNull({tasks.CLSDDATE}) and {tasks.OPENDATE} <= currentdate -14 and {tasks.OPENDATE} >= currentdate -8 then
1 else 0

i.e. If OpenDate is less than or equal to 14 days ago and greater than or equal to 8 days ago, then return. I want to separate it to

< 7 days
8 - 14 days
> 14 days

However, I don't want the counts to repeat, so that, hopefully, the three collumns total and match what the total column is.

Thanks!

David R. Longnecker
Product Support/Tech III
A+ Certified, Compass PDSE
USD 259, Wichita Public Schools
dlongnecker@usd259.net

 

[dgillz]

Click on file, report options and make sure that the &quot;set null field values to default&quot; is NOT CHECKED. If it is the isnull() test will alwaya be false and you will get all zeros. Then just create three formulas with if then logic:

@<7days
If IsNull({tasks.clsdate}) and {tasks.opendate}<= CurrentDate+7 then 1 else 0

@8to14days
If IsNull({tasks.clsdate}) and {tasks.opendate}> currentdate+7 and {tasks.opendate}<=CurrentDate+14 then 1 else 0

@>14days
If IsNull({tasks.clsdate}) and {tasks.opendate}> CurrentDate+14 then 1 else 0

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[dlongnecker]

Dgillz-

After using your formulas, I checked and now everything shows up under <7Days. I switched the +'s to -'s (shouldn't they be, I mean, 7 days previous from today?) and it returned numbers, but they didn't seem to make sense and didn't match the real data.

Thanks!

-David

David R. Longnecker
Product Support/Tech III
A+ Certified, Compass PDSE
USD 259, Wichita Public Schools
dlongnecker@usd259.net