30, 60, 90 day grouping

[dkaf]

I have contracts that have expired and will be expiring. I need to write a report that groups these contract into "Expires in the next 30 days", "Expires in the next 60 days", "Expires in the next 90 days".

The expired contracts can date back several months (they are extended until the new contract is awarded)but still need to be lumped in with the "Expires in the next 30 days". I need the expired dates to be generated off of the day I run the report. I need "Expires in the next 60 days" to cover the range day 31 to 60 and "Expires in the next 90 days" to cover the range day 61 to 90.

My database field is Expr1 and the format is 1/22/2015 12:00:00 AM.

Thanks

 

[pmax9999]

Try:

If	Date({Table.Expr1}) <= CurrentDate + 30
Then	'Expires in the next 30 days'
Else
If	Date({Table.Expr1}) <= CurrentDate + 60	
Then	'Expires in the next 60 days'
Else
If	Date({Table.Expr1}) <= CurrentDate + 90	
Then	'Expires in the next 90 days'

http://www.tek-tips.com/

This will include everything with a an expiry date prior to the run date, including those that did not get renewed intentionally, eg were never going to be renewed. You would need to know how to identify those contracts and include an extra condition in the first part of the If statement.

Hope this helps.

Cheers
Pete

 

[kray4660]

Think you could have used datediff also.

 

[dkaf]

Yes, this works, but it still shows contracts that will expire after 90 days. I need to not show those.

Any way I can write this so these are distinct groups? That way I can filter within the group by Agent or by Vendor.

Thanks,

 

[dkaf]

Sorry...I did group them just like I wanted. But it still is showing contracts that are beyond the 90 days. I don't want them to show on the report.

Thanks again!!

 

[kray4660]

I assume they dropped into the Others group. You can suppress that group. When you are doing a specified order grouping, in the Others tab, you can Discard all others.

 

[pmax9999]

If you don't want to see the 90+ days on the report, exclude them completely from the report via the Record Selection formula.

Something like this should do it:

Date({Table.Expr1}) <= CurrentDate + 90

Cheers
Pete