Selecting data based on dates

[busi1]

Hi,

I am trying to select the data based on the recency. I want to show the total number of records in this format.
----------------------------------------------------------
1 Year Recency 2 Year recency 3 Year Recency

I want to create a formula where it selects the most recent data( 1,2 and 3 years seperately).
Help is appreciated.
I am Using Crystal Report 10 and Oracle 10g.


Thank You
SB

 

[synapsevampire]

So if there are 750,000 rows returned, you want column A to show 250,000 rows going down theleftsideof the page, etc.?

I'll guess that you mean youwant some sort of aggreate involved with 3 buckets, one for each year.

So create 3 running totals which do the aggregate requiredof th field required, and in the evaluate->use a formula place:

// 3 years
year({table.field}) = year(currentdate)-2

// 2 years
year({table.field}) = year(currentdate)-1

// 1 year
year({table.field}) = year(currentdate)

Then you can limit all the rows returned by the database by using Report->Selection Formula->Record:

{table.field} >= cdate(year(currentdate)-2,1,1)
and
{table.field} <= currentdate

// 2 years
year({table.field}) = year(currentdate)-1

// 1 year
year({table.field}) = year(currentdate)

Note that I had to make assumptions about what 3 year recency means to you, I went with all data from jan 1, starting 2 years ago through today.

-k

 

[busi1]

Hi Synapse,

Thanks for your reply. Say for example i have 300000 total records, i want records came in from april 27th 2006 till date( One year recency) in the same way the other two. I have like couple of parameters in this report. I want to pull this recency data based on a field called table create date which falls under that period. I dunn want to use the table create date in the report selection formula as there are some other fields in the which i dunn want to be driven on this date field .

Thanks,
SB