conditional count based on field value and date

[dandanearl]

I'm looking for the number of times a record was viewed within a date range. Within the database I'm working with there is an action column where 16 = created and 32 = viewed and a logdate column. I am trying to capture a count of views within 2 days, 7 days, 15 days, 30 days, etc... of create date. I assume I would need a separate formula for each of the date ranges, but just getting one started is making my head hurt.
Ok, so, I'm a part timer with crystal! Any help is appreciated.
Here's an example of what I am looking for the output.

Record Total 2 days 7 days 15 days 30 days 3 months Year 1 Year 2
record 1 2569 1 3 5 7 1 0 0
record 2 5295 2 5 7 19 5 3 1

Thank you.
D

 

[hilfy]

Try this:

1. Group by the record.
2. Create a formula for each date range. It would look something like this:

{@2Days}
if {MyTable.Action} = 32 then
if DateDif('d', {MyTable.Createdate}, {MyTable.LogDate}) <= 2 then 1 else 0
else 0

3. Sum the formula per record to get the total number of views for each record.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com