Using Maximum value as a group category

[gard0128]

I am attempting to create a report that shows the last time a customer has been visited - by age categories (in days) - using the following data.

Account Visit Dates Age of last visit Aged Category
Acct 1 20050106 260 days over 180 days
Acct 2 20050317 190 days over 180 days
Acct 2 20050901 22 days 0-60 days
Acct 3 20040824 395 days over 180 days
Acct 3 20050823 31 days 0-60 days
Acct 4 20050420 156 days 61-180 days

I need to use only the last visit, for each Acct, to understand the last time the acct was visited, then group the accounts within "Age categories".

Last visited "0-60 days ago"
Acct 2 - 22 days ago
Acct 3 - 31 days ago
Last visited "61-180 days ago"
Acct 4 - 156 days ago
Last visited "over 180 days ago"
Acct 1 - 260 days ago

I want to delete the visit 190 days ago for Acct 2, and the visit for 395 days ago for Acct 3.

I've created 2 groups: "Age Category", and "Acct ID", and calculated a maximum on the visit date for both groups. I've also used a selection formula to make the actual date = the maximum date on the group "Acct ID", so that only the latest record, within the Acct ID group, will get picked up.

The report works fine until I make the "Aged Category" group 1. Then only the latest date for ANY customer in that group is picked up.

Is there a way to to pick up only the latest visit by for each "Acct ID", but also group the report by "Aged Category" as group 1 (highest group).


PLEASE HELP

 

[lbass]

What version of CR are you using, and do you have the option of using a SQL expression?

-LB

 

[gard0128]

version 10 and yes I do

 

[lbass]

I'm not sure how CR 10 handles SQL expressions, but you can either do this as a SQL expression or as a command object. The exact syntax/punctuation depends upon your datasource.

In the SQL expression expert, create the following {%maxdate}:

(Select max(A.`date`) from Table A where
A.`AcctID` = Table.`AcctID`)

Substitute your exact field and table names, leaving "A" as is, since it represents an alias table name.

Then in your record selection formula use:

{Table.date} = {%maxdate}

This will return only the maximum date to the report. You can then use a formula to create intervals and insert a group on that.

-LB