Group by calculated field

[gard0128]

I need to run a report that will pick up the last date a customer was contacted, and then group the report by categories (i.e. 0-30 days, 31-60 days, etc.)

the data looks like this
Customer id Date visited
001 20040101
002 20041231
002 20050903
003 20050403
003 20050915

The final report should list somthing like this

Customers seen in the

last 30 days
Customer id Last Seen
002 20050903
003 20050915

ast 180 days
Customer id Last Seen
001 20040101

I am attempting to identify the max date for each account, and then create a formula field that will categorize the account by days since last visited - basically, subtracting the max date from the current date.

My problem is that, when I attempt to group by the calculated field, I don't get my calculated field as an available field to group on.

Any ideas on what I might be doing wrong or how better to approach this?

Any help is appreciated.

 

[lbass]

What version of CR are you using?

-LB

 

[gard0128]

version 10

 

[dgillz]

Please post your formula to the forum.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[gard0128]

field from the database
{Activity.Actual Date}

Formula's:
LeadActvityAge
DateDiff ("d",CurrentDate ,{Activity.Actual Date} )*-1

LeadActivityAgeCategory
if {@LeadActivityAge} < 31 then "00-30 days" else
if {@LeadActivityAge} < 46 then "31-45 days" else
if {@LeadActivityAge} < 61 then "46-60 days" else
61+ days and over

 

[dgillz]

Your Datediff() formula does not refer to the Maximum you indicated in your original post. Is there a reason for this or did I miss something?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[gard0128]

sorry...missed that.

Initially, I used {Activity.ActualDate} in my formula.

When I used {Activity.ActualDate}, I could group by this field, but the calculation was wrong since it wasn't always grabbing the last date, for each customer, from my data list.

I then did a summary (maximum) on this field. I changed my first formula to pick up the maximum of {Activity.ActualDate} (Group1) in my formula. The formula works, but now I cannot access the field to group on it.

 

[lbass]

Do you have the option of adding a command object? Go to database expert->add command and enter something like:

Select Table.`Customer ID`, max(Table.`Date`) as maxdate
FROM `Table` Table
GROUP BY Table.`Customer ID`

Then link the command to your table on Customer ID AND by linking {Table.Date} to {Command.maxdate}. In your report, create a formula like this:

if datediff("d",{Command.maxdate},currentdate) in 0 to 30 then "00 to 30 days" else
if datediff("d",{Command.maxdate},currentdate) in 31 to 45 then 31 to 45 days" else
if datediff("d",{Command.maxdate},currentdate) in 46 to 60 then "46 to 60 days" else "61+ days"

You can then insert a group on this.

-LB

 

[gard0128]

I do not have the option of adding a command in database expert

 

[lbass]

Here's an approach without using a command object. This orders the groups by interval and then creates faux groups using suppression of group header sections.

First group on customer ID and then go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.customerID})

Next, right click on {table.date} in the detail section and insert a maximum on it (This is so the topN sort will become available). Then insert two additional group header section and drag the detail fields into GH#1_c and then suppress the detail section.

Then go to report->group sort->and choose "maximum of {table.date}" descending. Next create a formula {@intervals}:

if datediff("d", maximum({table.date},{table.customerID}),currentdate) in 0 to 30 then "00 to 30 days" else
if datediff("d",maximum({table.date},{table.customerID}),currentdate) in 31 to 45 then 31 to 45 days" else
if datediff("d",maximum({table.date},{table.customerID}),currentdate) in 46 to 60 then "46 to 60 days" else "61+ days" //etc.

Place this formula in the GH#1_b to act as the group name. Then create this formula:

//{@int} to be place in GH#1_c:
whileprintingrecords;
numbervar intervalx;

if onfirstrecord or
intervalx < {@intervals} then
intervalx := {@intervals};

Then go to the section expert->GH#1_a->suppress->x+2 and enter:

whileprintingrecords;
numbervar intervalx;

not onfirstrecord and
intervalx = {@intervals}

Also place this in the suppression area for GH#1_b. GH#1_a will provide a blank line between groups, like a footer, while GH#1_b will act as the fake outer group header, containing the first instance of an {@Intervals} value.

-LB

 

[gard0128]

I get everything down to "Place this formula in the GH#1_b to act as a group name...."

I've created the formula "if datediff"..., but when I attempt to place it in GH#1_b as the group name, I get an error.

What I'm doing is going to header_b. I then click Change Group. I then go to the "options" tab, choose "Customize group name field", click "Use formula as a group name", and insert the "if datediff"... formula. But when I test the formula, I get an error "this function cannot be used because it must be evaluated later". It probably has something to do with the "whileprintingrecords". I haven't used this before, and am not sure how to do so. Could you please explain exactly how this is used and where? Is it used within the formula, somewhere else???

Thanks for all of your help.

 

[lbass]

Create the formula in the formula expert and then just drag it into GH#1_b.

-LB

 

[gard0128]

when I write this formula, I get an error

formula name @int

actual formula

whileprintingrecords;
numbervar intervalx;

if onfirstrecord or
intervalx<{@intervals} then
intervalx:={@intervals};

When I test, I get the following message
"A number is required here" - refers to (@intervals)

 

[gard0128]

got it to work....thanks for all your help

 

[lbass]

Sorry for the confusion. When I tested this, my {@intervals} formula used numbers as results, as in:

if datediff("d", maximum({table.date},{table.customerID}),currentdate) in 0 to 30 then 1 else
if datediff("d",maximum({table.date},{table.customerID}),currentdate) in 31 to 45 then 2 else
if datediff("d",maximum({table.date},{table.customerID}),currentdate) in 46 to 60 then 3 else 4

I would use this type of formula for {@intervals} so that it works correctly in the variable formula, and then create a second formula for the fake group labels:

if {@intervals} = 1 then "0 to 30 days" else
if {@intervals} = 2 then "31 to 45 days" else
if {@intervals} = 3 then "46 to 60 days" else
"61+ days"

-LB

 

[gard0128]

thanks for all your help. report now works