This may or may not be the right way to do it so I am open to any suggestions:

I have 10 markets with store locations in each market.

I have different number of storesfor each market.

I need to rank the zip codes within each market and would like to automate the process.

The main problem is having the script recognize when the next market begins.

Any help would be appreciated.

You haven't said much about your structure, but if we make some assumptions, this can be done:

If you have a Store table, with a Market field and a Zip Code field, you can add a couple fields to do determine the counts for each Zip Code. Add these fields:

Count of Records (summary) = Count of Record ID

Count by ZipCode (calculation, number result) = getsummary ( Count of Records ; Zip Code )

When the records are sorted by Market and Zip Code, with the sort option on Zip Code set to reorder based on the Count of Records summary field, the records will be sorted by Market, then by the Zip Code count. If desired, a columnar report with sub-summary parts can be used to display the gouped counts. See attached example.

If an actual rank number is needed, there are a couple techniques that can be used. Let us know.

MarketRanks.fp7.zip

Thanks for the detailed info!

Yes I do need to have actual rankings. The main problem with writing a script is every market could have a different number of Zips.

So how do you set up a script so that it ranks 10 Zips in one market and 17 in another?

Agains thanks for the detailed answer.

One way is to use a script to loop through the records, and at each change in Zip, increment a rank counter (a global) and set the Rank field. Then at each change in Market, reset the rank counter back to 1 and continue.

Another method scans a value lists made up of the zip codes to determine the rank. Suppose you have a conditional value list "Zipcodes" that shows all the zipcodes for the current market, then a calc like this will show the rank of each store within that market:

Rank (calculation, number result) =

Let(

values = ValueListItems ( Get ( FileName ); "Zipcodes");

PatternCount ( Left ( values; Position ( values; Zip Code; 1; 1 ) ); ¶ ) + 1

)

I'm not sure which method would perform better.

The only problem with this custom function is that if there's a tie it will not skip the subsequent ranking.

it will provide the ranking [color:red]1, 2, 2, 3

rather than [color:red]1, 2, 2, 4

To correct this issue I changed my number I was ranking (in the value list) from

[color:red]average

to

[color:red]average & "-" & serial

and I changed the custom function to the following:

[color:red]Let(

list = ValueListItems ( Get(FileName) ; ValueListName) ;

PatternCount(

Left("¶" & list & "¶";

Position("¶" & list & "¶";"¶" & Field & "-";1;1));

"¶")

)

Basically I changed

[color:red]"¶" & Field & "¶"

to

[color:red]"¶" & Field & "-"