Running Reports for last 90 days 2

[kate8]

Hi,

My reports are scheduled to run automatically in Crystal Enterprise monthly using “timeStamp in LastFullMonth”. Now some of the reports are required to include data for last 90 days. The reports still need to be run monthly. Every month, run for last 90 days.
I have check through the exited functions, but couldn't find one to use. Is there any function that I can use for this purpose or I need to create my own?
Thanks a lot for any helps and ideas!!!

 

[fisheromacse]

I believe a combination of these 3 will meet your needs.

aged0to30days
aged31to60days
aged61to90days

something like this:
((timestamp in Aged0to30days) OR (timestamp in Aged31to60days) OR (timestamp in Aged61to90days))


another option may be to use the currentdate function something like this:
//{@90ago}
currentdate-90

then your selection formula would be something like:
timestamp in {@90ago} to currentdate



I do not have crystal in front of me at the moment, so please forgive any errors/typos.

 

[IanWaterman]

You can also use dateadd()

timestamp >= dateadd("d", -90, Currentdate)

Ian

 

[kate8]

fisheromacse & Ian,

Thank you so much for your quick respond. I have been getting so much helps in this Forum. Thanks to all!!!

Sorry for not explaining clear. When I said "running for last 90 days", actually means pull out data from last three month (should call last three month). For example, the reports were set to run on 10th day each month, on May 10th, the report should have data from Feb,March,April (02/01/2011 to 04/30/2011). So it is based on current month, then back to three month. If I create a formula, I need to deal with the difference of each month...

 

[fisheromacse]

the solutions i (if my syntax/etc is correct) and Ian provided should not be affected by the number of days in a month.


from your most recent description, it think this may be more what you need:

timestamp in [dateserial(year(currentdate),month(currentdate)-3,1) to dateserial(year(currentdate),(month(currentdate)-1)+1,1)-1]

 

[kate8]

fisheromacse,

It works great!! That is what I need.
Thank you so much!!!

 

[IanWaterman]

Sorry to spoil the party but this does not work for Feb and January.

[dateserial(year(currentdate),month(currentdate)-3,1) it will error out.

try

timestamp in [dateadd("m", -3,(date(year(currentdate),month(currentdate),1))) to date(year(currentdate),month(currentdate),1)-1]

Ian

 

[lbass]

Ian,

No, it won't error out. That's the purpose of dateserial--handling month numbers.

-LB

 

[IanWaterman]

Humble pie being consumed;-)

Never used dateserial(), learn something new each day.

Ian

 

[kate8]

I was also concerned about the issue that Ian mentioned, I checked the reference of dateserial(). Now lbass confirmed it!!
Thank you to all !!!

 

[fishymetrics]

sending a Star to fisheromacse.
This is essentially a function for LastFull3Months.
You got me looking at Crystal Help and I can see lots of ways to use the DateSerial function now.

Thanks