Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 6786 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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.

Posted

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

Posted

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.

Posted

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.

  • 2 months later...
Posted

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 & "-"

This topic is 6786 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.