Data calculation or Selection

[idehen]

Hi Guys,

I am using crystal version 10.

I have the following information.

Grant Date Action Date Status
01/01/2002 01/02/2004 Hold
01/02/2003 01/08/2006 Release
20/08/2003 30/01/2006 Hold
21/09/2004 20/06/2007 Release
20/01/2006 18/05/2008 Hold

The report uses the Action Date as Parameter Range.

What i want to do is pull through any information that have an action date 3 years after the grant date. Basically if i was to run the report for range (from 01/01/2005 to 12/05/2008), i want to be able to view only information that falls within the date range (Action Date) but is only 3 years or more of the Grant date.

any help will be appreciated.

Thanks

 

[kskid]

To create a new parameter, click on Feld Explorer->Parameter->New

On the parameter screen pop-up, create you parameter name (?ActionDate) and select date as type. On the Value options, scroll down and select Allow range values. Click OK.

Go to your record selection and add the following

{table.action_date) in {?ActionDate}

 

[lbass]

You would want to use a record selection like this:

{table.action_date) in {?ActionDate} and
datediff("d", {grantdate},{actiondate}) >= (3*365.25)

It seems like you would want to do this per some group based on grantID though. Could there be multiple action dates per grant ID? I wouldn't recommend this solution if there are.

-LB

 

[idehen]

Many thanks Ibas and KsKid. Appreciate the help.

Sorry for my late response, been away on holiday and just returned the hustle and bustle of the office life again today.

I'll give your suggestions a try and let you know what happens. Thought i quickely thank everyone for their help as soon as possible.

Sol

 

[kskid]

If you are looking for the 3 years ago given a date range parameter, then you could use the following.

{table.action_date) in {?ActionDate} and
{table.grantdate} in [dateserial(year(minimum({?actionDate}))-3,month(minimum({?actionDate})),day(minimum({?ActionDate}))) to dateserial(year(maximum({?actionDate}))-3,month(maximum({?actionDate})),day(maximum({?ActionDate})))

-lb

 

[idehen]

Thanks everyone for every ideas and inputs. Really appreciate it.

While i do think both formula and logic provided by Ibass and Kskid do work, i tried an alternative idea i came up with that also did the trick. Thought i post it for others to have alternative solutions to the fantastic suggestions above.

I created a new formula (E.g @Formula) and in the formula used datediff as shown:

datediff("YYYY",{Grant Date},{Action Date})

This will basically show me an outcome of the date difference between Grant_Date and Action Date and then use selection criteria for @Formula >= 3.

This did the trick. But again many thanks to everyone.

 

[lbass]

Note that if you Grant Date = 12/31/07 and Action Date = 1/1/08, your formula would return 1 year, when it was really only one day. When you use datediff with "yyyy", it is only subtracting that portion of the datefield--which was why I used 3*365.25.

-LB

 

[idehen]

Thanks for that again Ibass. I missed that bit out and have amended my formula. I probably didn't notice cos i was looking more for info after 3 years.

But glad you mentioned it as my report is a dynamic report that could use date in 3 years or more or less during next run and this would have been a problem.

Fantastic forum! big thanks again.