Salesforce and other SMB Solutions are coming soon. ×

# Ranking without scripting or sorting

## Recommended Posts

Without scripting or sorting is there a calculation that will rank the customers according to either the dollar amount spent or amount of jobs receieved?

##### Share on other sites

what do you want to achieve?

##### Share on other sites

Have large db. Want to go a customers record and have a field show me their current rank as to their dollar amounts. I know how to do it when sorting and setting record counter or current record number. Take too long when their are 5000 records in found set.

I want to do it with a calculation field to avoid the scripting and sorting.

##### Share on other sites

If you had FM Developer, you might be able to build a custom function that when given the list of dollar amounts for the found set (probably using the Copy All Records[] script step,) and the dollar amount of the current record, sorts the list and returns the line (rank) of the current record's dollar amount.

I imagine you'd have to decide what to do about records that have the same dollar amounts, and those that are blank.

With this method, there's a little bit of scripting needed to grab the dollar amounts, but most the work is being done by the calculation engine. Note that there is a limit of 50,000 recursions within custom functions, so if you're using a custom function to sort the dollar amounts, it won't work if the size of the found set exceeds that.

##### Share on other sites

I made a calc just the same, tested it on a 5 record sample, but I don't know if it'll hold...

Code:

`Let(ListItems=(ValueListItems(Get(FileName);"items")&Â¶);ValueCount(ListItems)-ValueCount(Left(ListItems;Position(ListItems;Â¶ & NumberField&Â¶;1;1))))`

"items" is a valuelist of all the values in the field NumberField, which is the field you wish to rank.

for what it's worth...

kjoe

##### Share on other sites

Not sure I understand your logic, kjoe. ValueListItems will automatically remove that carriage return you're adding except for ONE cr at the end. And ValueListItems automatically has a CR to create a list anyway.

Usually ranking involves aggregate data, ie, adding together several records (whether within the same table or a related table). There is simply no way to provide aggregate within a value list. The calculation you provided produces the exact same result as the number field you are evaluating (on a record per record basis).

Even if you can recurse this puppy, it'll never refresh if it's based upon aggregate or related without script or trigger. I don't believe CFs can address groups of records.

Usually customer tables do not contain the data to 'add'. If only one record were involved, the number itself would be the rank!

You can create a summary on the number field and then sort by this summary field whenever you want to review an updated ranking list. Just create a self-join or use your existing relationship to Invoices or whatever. Create your summary within Invoices (or this new self-join). Sort from Customers on CustomerID then re-specify below sort by summary field and select the summary in related table.

You can also create an unstored calc in Customers with Sum(thatRelationship::number). It will automatically be unstored and will update itself but either method will only allow sorting by the sum. Fastest (I thnk) would be summary field and sorting on that whenever a User wants to see customers ranked. You can't sort a relationship on a summary field either.

But then again, I could be wrong. It won't be the first time.

##### Share on other sites

If this needs to be scripted (and it's hard to see any other way), wouldn't Filemaker's native sort be more efficient than a custom function?

(BTW, the limit for custom function is 10,000 iterations.)

##### Share on other sites

Piedpiper,

Your point about aggregates and refresh troubles is pretty much killing, I should have known that would finish it off.

But for completeness, there is some logic behind the use of the CRs in the calc. First off, if you leave out all the CRs, the highest ranking number will be marked zero, which is not exactly wrong but unusual. When I add the CRs, the highest ranking is marked 1. I could've just added 1, but hey...

[ QUOTE ]

If only one record were involved, the number itself would be the rank!

[/ QUOTE ] How so? It does not tell you anything about whether it's the highest, lowest or somewhere in between.

Well, it was just a stab to get to a no-script-no-sort solution. In the dark as it turns out to be.

kjoe

##### Share on other sites

Yukon,

I was wondering whether it is very interesting to know whether some customer is at position 1295 and another at 1297. Does not really mean much. What if you could make fixed ranges based on past data, then it would be an easy calculation to compare the customer's amount spent to these ranges and put him in a low, mid, or high, something like:

Case(

AmountSpent

AmountSpent

AmountSpent

"high")

quick and dirty, but maybe just as telling?

kjoe

##### Share on other sites

[ QUOTE ]

If this needs to be scripted (and it's hard to see any other way), wouldn't Filemaker's native sort be more efficient than a custom function?

[/ QUOTE ]

I tried both, and you are absolutely correct; it is much more efficient using the built-in sort. My CF idea was a brainstorm attempt at solving it without sorting the records.

Using the built-in sort makes the problem easier, if Yukon is willing to give up his dreams of a sortless solution. I still like the idea of using the Copy All Records here, but with the Sort[] first so they are in order. Then you can use a simple unstored calc to show the rank:

Rank (calculation, number result) = PatternCount ( Left( gAllDollars; Position ( Â¶ & gAllDollars & Â¶; Â¶ & dollars & Â¶;1;1) ); Â¶ ) + 1

"dollars" is the amount for the current record, "gAllDollars" is a global list of all the dollars values of the found set.

The Update script would look like this:

Sort [ by dollars ]

Go to Layout [ Layout with only the dollars field ]

Copy All Records

Go to Layout [ Layout with gAllDollars field ]

Paste [ Select ; gAllDollars ]

Commit Records[]

This Update script would be used whenever the found set changes or the dollars change to get the ranks to update.

[ QUOTE ]

(BTW, the limit for custom function is 10,000 iterations.)

[/ QUOTE ]

On my computer, I get 50,000 iterations before it gives up.

Personally, I like kjoe's suggestion of using a rank scale based on total dollars.

##### Share on other sites

I dunno. Seems convoluted to me to run an update script every time the found set changes or a number field changes. The unstored calculation would/could be incorrect if ONE number field changed because the ranks could change.

I feel it would be simpler to use summary in related. Any time someone wants to see a Rank list, sort by CustomerID (and reorder based on summary descending). kjoe, my point is that if sorted descending, the Record Number *is* the rank!!

If business wants top 100, go to record 1, omit multiple (100), show omitted (all behind a Freeze Window and who cares if it's scripted). I can assure it it's very fast over very large data sets and network.

Business rules would need to address ties. I think I'd group them by Mod() and the remainder if I wanted to group them. A list is simply logical, simplest and fastest, in my opinion. Produce it spur-of-the-moment. Because the minute you waste time writing it to customer records, it will have changed anyway. It's best NOT to view a ranking than to view one which is instantly incorrect.

##### Share on other sites

YC.... It's OK to call you that right. Anyway I think Comment is close. I know you said no sorts or no scripts but sorry to say I don't know any tricks with calcs that would do that. Maybe someone else does.

[ QUOTE ]

If this needs to be scripted (and it's hard to see any other way), wouldn't Filemaker's native sort be more efficient than a custom function?

(BTW, the limit for custom function is 10,000 iterations.)

[/ QUOTE ]

I will attach a screen shot of an old file I no longer use. (don't work there anymore) Thanks Goodness!

What I would do is have field for Job counts and Dollar Amounts for each year. Also 2 sets of Rank fields for each Amount and Job Count for each year. During the Current year I woud use relationships (CustomerIDYear)Sorted in desending order. I would have one set of Rank fields display the current record number and the in a SCRIPT set the second field to that number. I keep the related fields in a work layout only as to not to get different numbers each time the found count changes. Or even looking at a differnt year. Only the second set of numbers (the non related that were set in a script would I put in the report layout. Then I would compare agaist previous years. Display Customers who are not as strong as previous year in Red. This

way the salesman knew who to call right away.

These systems are good and should be kept up to date via

Orders for counts and Invoices for Amount. It really makes you feel stupid when a good a customer has left you. Monthes later becuase you are slow and someone says . "What ever happed to So And So Company. You call them up and they say where were 3 months ago when I had a problem? Or Your prices are too high and no one at you company seems to care about me so I went to another vendor. I am trying to implement this system at my new job beacuase the same thing happened there. This time the customer said all our work is now going to China. Kinda makes you feel real dumb when they say that.

##### Share on other sites

In order to keep the current found set and sort order, I'd make the script:

Freeze Window

New Window

Go to Layout [ Layout with only the dollars field ]

Show All Records

Sort [ by dollars ]

Copy All Records

Go to Layout [ Layout with gAllDollars field ]

Paste [ Select ; gAllDollars ]

Commit Records[]

Close Window

[ QUOTE ]

On my computer, I get 50,000 iterations before it gives up.

[/ QUOTE ]

Are you sure?

##### Share on other sites

Another thought:

Of course, any ranking involves a sort. But it seems the bottleneck here is sorting on an unstored aggregate calculation. What if the script simply inserted the values into a regular number field?

##### Share on other sites

[ QUOTE ]

Another thought:

Of course, any ranking involves a sort. But it seems the bottleneck here is sorting on an unstored aggregate calculation. What if the script simply inserted the values into a regular number field?

[/ QUOTE ]

[ QUOTE ]

During the Current year I woud use relationships (CustomerIDYear)Sorted in desending order. I would have one set of Rank fields display the current record number and the in a SCRIPT set the second field to that number.

[/ QUOTE ]

Kinda of what I said.

##### Share on other sites

It seems to me you're talking about sorting on the related data, then remembering the order? I meant avoiding sorting on the related data altogether.

##### Share on other sites

[ QUOTE ]

In order to keep the current found set and sort order, I'd make the script:

Freeze Window

New Window

Go to Layout [ Layout with only the dollars field ]

Show All Records

Sort [ by dollars ]

Copy All Records

Go to Layout [ Layout with gAllDollars field ]

Paste [ Select ; gAllDollars ]

Commit Records[]

Close Window

[/ QUOTE ]

[ QUOTE ]

It seems to me you're talking about sorting on the related data, then remembering the order? I meant avoiding sorting on the related data altogether.

[/ QUOTE ]

You losing me now Sort or no sort?

##### Share on other sites

My dear chopper: context is everything. The first part is a note on Ender's script. The second part is under the heading of "Another thought:...". I hope that makes it clear.

##### Share on other sites

Silver and Gold , Silver and Gold. Woooooo Whoooooo

A lot to prospect. Give me some time to work through this.

##### Share on other sites

[ QUOTE ]

In order to keep the current found set and sort order, I'd make the script:

Freeze Window

New Window

Go to Layout [ Layout with only the dollars field ]

Show All Records

Sort [ by dollars ]

Copy All Records

Go to Layout [ Layout with gAllDollars field ]

Paste [ Select ; gAllDollars ]

Commit Records[]

Close Window

[/ QUOTE ]

Nice refinement.

[ QUOTE ]

[/ QUOTE ]

I tried my iterative function and the one in that thread and got different results. Attached is an example. It seems the limit of iterations is 50,000, but if the function has to build a resulting data string as it goes (as most recursive CFs do,) then it stops at 10,000. So for a sorting CF, the limit would indeed be 10,000.

##### Share on other sites

[ QUOTE ]

Another thought:

Of course, any ranking involves a sort. But it seems the bottleneck here is sorting on an unstored aggregate calculation. What if the script simply inserted the values into a regular number field?

[/ QUOTE ]

Do you mean use a script to set the dollar total for each Customer record? I'd imagine Yukon would like to avoid using a Replace or Set Field Loop to update the totals. But if this number field in each Customer record was updated each time the orders are placed or received or whatever, then no Replace would be needed and the sorting on that field would be very quick.

##### Share on other sites

[ QUOTE ]

I dunno. Seems convoluted to me to run an update script every time the found set changes or a number field changes. The unstored calculation would/could be incorrect if ONE number field changed because the ranks could change.

I feel it would be simpler to use summary in related. Any time someone wants to see a Rank list, sort

by CustomerID (and reorder based on summary descending).

[/ QUOTE ]

PiedPiper,

I'm not sure I see much difference as your suggestion and mine both require sorting. Also, wouldn't you need to have the records sorted in order to view that rank? With mine, the sort order can be changed after setting that global. Perhaps you can provide more.

[ QUOTE ]

kjoe, my point is that if sorted descending, the Record Number *is* the rank!!

...

Business rules would need to address ties. I think I'd group them by Mod() and the remainder if I wanted to group them. A list is simply logical, simplest and fastest, in my opinion...

[/ QUOTE ]

This bring up an interesting point. If I'm understanding you right, a straight rank based on record number would give a different rank for records that are tied. Example:

Rank Dollars

1 \$50

2 \$10

3 \$10

4 \$10

5 \$20

It's not clear if this is desireable (as you said, business rules...) I used the assumption that ties should have equal ranking, like:

Rank Dollars

1 \$50

2 \$10

2 \$10

2 \$10

5 \$20

I don't know that this would be better, but I think it's worth pointing out this difference for Yukon. I'd like to see more of your algorithm for grouping ties, as it's not clear to me how this would work with your summaries.

##### Share on other sites

[ QUOTE ]

It seems the limit of iterations is 50,000, but if the function has to build a resulting data string as it goes (as most recursive CFs do,) then it stops at 10,000.

[/ QUOTE ]

Indeed, that seems to be the logical conclusion. Interesting.

[ QUOTE ]

Do you mean use a script to set the dollar total for each Customer record?

[/ QUOTE ]

Yes.

[ QUOTE ]

I'd imagine Yukon would like to avoid using a Replace or Set Field Loop to update the totals. But if this number field in each Customer record was updated each time the orders are placed or received or whatever, then no Replace would be needed and the sorting on that field would be very quick.

[/ QUOTE ]

I don't know about that - it's a lot of coding to implement. If the purpose is to evaluate the relative importance of the customer, running the script once in a while would be sufficient. kjoe's ranging suggestion also sounds very reasonable to me.

##### Share on other sites

thinking over what's interesting for sales analysis, isn't reporting per-customer time series such as Chopper suggested more useful than static ranking between customers, which after all is only a snapshot that does not really tell you about the company's performance over time? or am I missing the point of ranking? What does relative importance mean? One customer of \$10.000 is the same as two of \$5.000. Maybe the time spent on making deals should be incorporated. Ah well I think that in the end, keeping or loosing a customer depends only a little bit on how you analyse your data and a lot on how you treat the client. Chopper's right there again.

kjoe

##### Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Images

• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33,322
• Total Posts
140,989
×
×
• Create New...