Jump to content

Generate "ranking" field


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

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 …
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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?

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!!!!

Link to comment
Share on other sites

This topic is 2876 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
 Share

×
×
  • Create New...

Important Information

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