Group Sort Formula Help

[puter55]

CR XI

I have a currently have a report with a crosstab setup as follows:
Rows: RouteName
Columns: CustomerName
Summarized Field: CirculationCount

The report is currently only grouped by a Date Parameter.

I need to add a group to the report and can't quite figure out how to do it. I Need to have the crosstab display information for the RouteNames that have a Circulation Count > 0 for like customers. This is difficult to word so I will give an example of how I would like it grouped.

Lets Say I have 5 Customers, named Customer1,...Customer5
and a bunch of routes named Route1,....Route50.

Routes 1,2,4 all have Customers 1,2,3,4,5 in common which means they all have a number > 0 in the circulation count in the crosstab. I would like this to be the first group.

Routes 3,5,6,7,8 all have Customers 1,2,3 in common. I would like this to be the second group... and so on. The Routes will never be used again in the grouping.

I hope this makes sense, and any help would be appreciated.

 

[lbass]

Create a formula:

if {table.route} in [1,2,4] then "Cluster 1" else
if {table.route} in [3,5 to 8] then "Cluster 2"

Add this as your row #1 in the crosstab.

-LB

 

[puter55]

Thanks for your response lbass

It appears i forgot to mention that the routes will vary from week to week. the 124 was just an example. One week Route 4 might be in the first group, the next week it might be in the 3rd.

I'm wasn't familiar with the cluster, but I will look into it.

 

[puter55]

Nevermind about the cluster thing, I'm kind of slow sometimes.

 

[lbass]

So if the routes that cluster together vary per week, what is it that tells you which routes belong together each week?

-LB

 

[puter55]

What tells me which routes belong together would be the customers they have in common with > 0 in the circulation count. I was thinking it would probably be easiest to go from most customers to least.

For example Cluster 1 might have Routes 1-4 because they have customers 1-5 all having the circulation count >0.

Cluster 2 might have routes 5-10 because they have customers 1-3 in common.

Cluster 3 might have routes 11-15 because they have have customers 1,2,4 in common.

The routes will never be used in more than 1 cluster.

 

[lbass]

Sorry, but I cannot follow your logic.

-LB

 

[puter55]

lbass

Thanks for trying, sadly I don't know how else I can explain it.