Generate "ranking" field

[MikeKD]

Hi Folks,

 

I thought I'd pretty much worked out how to generate a ranking field for pupils I teach.

The kids nearly all take a baseline test when they join the school. This is a national test with pretty accurate results (though false negatives are possible).

 

what I'd like to do is generate their percentile - ranking order (out of 100) based on all the kids at my school who've taken the test.

 

I thought I would:

• Filter for records that have valid test scores.
• Count these records. Set $TotalTestScores
• Sort them.
• Work out pupils place in that ranking order. Set $RankTestScores
• Calculate ($RankTestScores / $TotalTestScores) * 100

But - most of that I can't do in a calculation field!

 

I don't have a B plan yet - any ideas please?

 

Cheers,

Mike

[bruceR]

You can't do what in a calculation field?

[eos]

If you do this …

 

• Filter for records that have valid test scores.
• Count these records. Set $TotalTestScores
• Sort them.
• Work out pupils place in that ranking order. Set $RankTestScores
• Calculate ($RankTestScores / $TotalTestScores) * 100

 

… with a sort descending by score, then their rank in that list would be their record number …?

 

If that's not what you mean, please explain.

[MikeKD]

No, you're right; that's exactly what I meant.

 

But can I do all that within a calculation field; doesn't in need script steps (sort / find) that aren't available?

[MikeKD]

Oh, wouldn't it make more sense to do this as a script anyway?

It's not often that the data changes, so clicking a button to run a script to enter stuff would work. Something like:

 

 

• Enter find
• Go to layout
• Filter for records that have valid test scores.
• Count these records. Set $TotalTestScores
• Sort them.
• Go to 1st record. Set Variable $RankNo.
• Start loop
•    Set Field =  Calculate ($RankNo / $TotalTestScores) * 100
•    Set Variable $RankNo + 1
•    Go to next record
• End loop when $RankNo = $TotalTestScores

 

In principle, would that work?

 

Cheers!

Mike

[eos]

Oh, wouldn't it make more sense to do this as a script anyway?

In principle, would that work?

[…]

 

 

Maybe, but 1 ) it's convoluted (e.g. why keep a counter that's identical to the record number anyway?), and 2 ) didn't you agree that after sorting, the record number is the rank?

Enter Find Mode
Go to Layout [ Students ( Students ) ]
# Filter for records that have valid test scores.
Enter Find Mode
Set Field [ Students::score ; "*" ]
Perform Find
Sort [ Students::score ]
Replace Field Contents [ Table::rank ; Get ( RecordNumber ) ]

What could trip you up here (besides multi-user issues when using RFC …) are ties; that would warrant a loop, as in

[…]
Sort [ no dialog ; Students::score ]
Go to Record/Request [ first ]
Loop
  Set Field  [ Students::rank ; 
    Let ( 
      recNum = Get ( RecordNumber ) ; 
      Case ( 
        Students::score = GetNthRecord ( Students::score ; recNum - 1 ) ; 
        GetNthRecord ( Students::rank ; recNum - 1 ) ; 
        recNum 
      )
    ) 
  ]
  Go To Record/Request [ next ; exit after last ]
End Loop

or, instead of using GetNthRecord(), store score and rank in variables before you go to the next record; then compare $previousScore with the current score and either write the record number or $previousRank.

[MikeKD]

Thanks for that - I've never used RecordNumber, so didn't register it as an option.

 

That loop exit is really neat - I like!

 

This is going to seem seriously naive; but is recNum a variable more local than $recNum would have been? - Does it only work within the loop, not the script?

I'm not quite sure how the SetField bit works; I'll have a play and a read tomorrow and see if I can understand it.

 

The raw rank isn't all that useful unless I either:

1. Show Rank / Total to give an idea of the number. (Actually this would be very trivial - this primary place for this info is in a tooltip on the register.
2. Turn it into a mark out of 100, so we can see if they're in the top 1, 10 or 50% etc.

Thanks sooo much for your help!! :-)

Cheers,

Mike

[eos]

This is going to seem seriously naive; but is recNum a variable more local than $recNum would have been? - Does it only work within the loop, not the script?

 

In FileMaker, a loop does not introduce a scope (the portion of code where a variable is valid); this is handled differently in some other programming environments.

 

The variable you referred to is simply called “Let variable”.

 

Here's a brief cheat sheet for the types of variables and their scope:

 

someVar = Let() (and any enclosed expressions)*

$someVar = script (leaving aside some other use cases …)

$$someVar = file

 

So you could say that a Let variable is “more local” (tightly scoped) than a global variable.

 

*Note that

Let (
  a = 1 ;
  a
) 
+ a

would yield a syntax error, because outside the Let(), a is unknown. But …

Let (
  a = 1 ;
  Let (
    b = 2 ;
    a + b
  )  
)

… is OK; since the second Let() is within the scope of the first one, a is known there, too (but the outer Let() wouldn't recognize b).

 

Never mind the silliness of these examples …

[jbante]

Your scripted approach is on the right track. There's nothing unnecessarily convoluted about it. One thing you might want to consider is limiting the scope of your ranking to students in the same cohort, if that's appropriate for your application.

 

It is possible to calculate approximate ranks from a stored histogram-like data structure without having to sort a found set of all records in question and rank (or re-rank) all of them at once, but that's only beneficial and appropriate for much larger data sets than I think you're working with, based on our previous conversations. That sort of technique is better suited for applications where ranks need to be calculated for so much data that the more obvious method is too painfully slow (it's O(n) vs. (at best) O(n log n)), or for maintaining real-time statistics on high velocity transactional data.

[comment]

I am mightily confused by what's going on here:

 

what I'd like to do is generate their percentile - ranking order (out of 100) based on all the kids at my school who've taken the test.

 

Do you want the rank, or the percentile-rank? If it's the latter, then this thread needs to start all over.

[MikeKD]

Hi comment,

I've just wiki'd percentile-rank - wow - I didn't think it was that!!

 

I thought a percentile was just a ranking order converted to 100 to make it easier to understand - didn't realise there was a bell curve attached to it. Is that just to help differentiate between the marks that lie in the most common range?

 

I don't think I need to use the bell curve; for us the data will be useful in comparing two kids (potentially in different cohorts & potentially ones who've left the school).

 

I /think/ this thread IS on the right track - certainly for my ability level!!

 

jbante,

I don't /think/ I want to limit it to cohort.

 

But as you can both tell, maths isn't a natural strength of mine, let alone statistics!!!

 

Cheers,

Mike

Many thanks eos!

Nice clear explanation re the variables - now to understand how those field are set so I can try to make them out of 100..

[comment]

I am not an expert in statistics either. Being well aware of this deficiency, I would avoid inventing my own "statistical" tools and attribute a meaning to them which they might not have.

 

IMHO, the process needs to follow a different path:

 

1. What is the meaning I wish the statistical variable to convey to me?

 

2. Which (existing) statistical variable expresses this meaning? Consult an expert in statistics.

 

3. How is this statistical variable calculated?

 

4. How to implement this calculation in Filemaker?

[jbante]

Wikipedia's page on percentile rank ("quantile," more generally) can easily steer you astray in your understanding of what a percentile rank is. Your understanding was actually correct the first time. The normal distribution example presented on that page is only an example they used to illustrate their point that percentile rank is an ordinal measurement (rank or position in a list), not an interval measurement, i.e., if one student's percentile is 75%, and another's is 50%, the 75% student isn't necessarily 50% better than the 50% student, just better than 50% more other students.

 

One notable exception is IQ scores, but only because they are defined so it works out that way. Modern IQ scoring actually starts with a percentile among a calibration sample, then a more traditional-looking IQ is calculated by figuring what that percentile would correspond to in a normal distribution with mean=100, standard deviation=15 distribution. This particular construction is unusual, but it's not unusual to scale a more raw statistic to something more helpfully meaningful. 0-100 scales are common. Correlation measures are almost always numbers between -1 and 1, as another example.

[MikeKD]

Thanks all of you!

 

comment - I think you're right; it's good practice to work out what I want to achieve, then ask the experts how to achieve it - I've been putting the cart before the horse in this and working out what I have the ability to achieve and going from there.

 

jbante - thanks so much for your post. I've got my version of a percentile working today using eos's 1st suggestion.

 

At the moment, I've got data that is useful to me and my staff, using the fairly simple formula I first thought of, but somewhere down the line I'm going to need to follow comment's advice and ask you folks before starting more statty threads like this one.

 

Cheers!

[comment]

Wikipedia's page on percentile rank ("quantile," more generally) can easily steer you astray in your understanding of what a percentile rank is.