
1. Re: Calculating percent rank
jbante Oct 23, 2012 10:43 AM (in response to jdc)Calculating quantile (percentile) ranks in FileMaker is not as straightforward as in Excel. When I've needed to set quantile scores for found sets of records in the past, I'd simply sort the records and set the quantile score to something like "( Get ( RecordNumber )  1 ) / Get ( FoundCount )". Some of the things you're asking for make the overall solution more complicated, but it's all still feasible:
1. The ranks of new data can be calculated relative to existing data as the new data are entered: Get a list of the existing values via something like this:
ExecuteSQL ( "SELECT value, quantile FROM Table WHERE monthStartDate = ? ORDER BY value" ; "" ; "" ; Table::monthStartDate )
Scan through the list until you find the position your new value would fit. If you find an exact match on value, use the quantile value from that match (to satisfy #2); otherwise, you can either do a similar calculation to the example working with a found set ($valuePosition / ( ValueCount ( $sqlResult ) + 1 )) to get the most accurate quantile value for the one record, or you can interpolate between existing neighboring values to keep the rank ordering accurate until you can recalculate for the whole set. Updating the quantiles for the rest of the records for a month will not be so dynamic. I suggest running a serverside script that looks for records with a recent modification timestamp, then recalculates quantiles for each month with modified (or new) data. It isn't instant, but it's dynamic enough for most purposes.
2. Forcing duplicate numbers to have the same quantile score requires a modification to the recordscan method. First, you need to clarify what quantile score tied values should have:
2.1 Calculate the quantile score as the lowest any of the records would have if they were not tied. For example, if 4 students with GPAs 4, 3.8, 3.8, and 3.0 were ranked 1, 2, 2, and 4, respectively. In this case, your quantilesetting script keeps track of $previousValue and $previousQuantile, and uses $previousQuantile when the current value equals $previousValue, but recalculates $previousQuantile otherwise. (If the ranks should be 1, 3, 3, 4 instead, do the same thing, but sort or scan the found set in the other direction.) This is the most statistically conventional approach.
2.2 For the same 4 students, ranks are 1, 2.5, 2.5, 4, i.e., tied quantiles are the average of what they would be otherwise. In this case, you have to count how many other records share the same value before setting any of them. In older versions of FileMaker, this could take awkward backandforth record scanning or counting through a specialpurpose relationship, but now we can use ExecuteSQL with a query like "SELECT Count(*) FROM Table WHERE monthStartDate = ? and value = ?". The calculation for the quantile gets slightly more complicated, but you can save the value each time you do it using the same technique from 2.1.
2.3 For the same 4 students, ranks are 1, 2, 2, 3; i.e., ranks of other values are recomputed as if the tied values are actually only one data point. This isn't a proper quantile any more, but I suppose it's possible someone might want to do it this way for some reason some day. I guess. In this case, replace Get ( FoundCount ) in the quantile calculation with a counter in your script that only increments for new values.
3. Using the "( Get ( RecordNumber )  1 ) / Get ( FoundCount )" calculation, the first value in the found set will get the quantile score 0. If you use tiehandling option 2.1 (and your values are all ≥ 0), all the tied zeros will also get rank zero. Otherwise, you can just wrap the calculation in an If statement: quantile = If ( value = 0 ; 0 ; /* Else */ ( Get ( RecordNumber )  1 ) / Get ( FoundCount ) ).
4. You may have to fiddle to get the values to match Excel's result exactly, but it looks like it's just a standard quantile function to me, based on what I'm reading in this documentation. ( Get ( RecordNumber )  1 ) / Get ( FoundCount ) in a sorted list will do that. An analogous calculation will work for finding corresponding values in a returndelimited list, such as an ExecuteSQL() result.
5. To calculate quantiles separately for each month, just constrain the found set to a month at a time when calculating them.

2. Re: Calculating percent rank
jdc Oct 23, 2012 11:10 AM (in response to jbante)Thanks so much, I'll give it some thought and try to get it incorporated into my database.
JD

3. Re: Calculating percent rank
AllegroDataSolutions Feb 5, 2016 5:08 AM (in response to jdc)I am looking for a more straight forward way of doing this. A calculation, rather than having to run a script and sort.
In this database,
Field A = the number of items sold by the vendor whose record the user will be viewing
Field B = the total number of items sold by all vendors
Field C = a calculated number describing the percentage that Field A is of Field B
So, if
Field A = 5
and
Field B = 10
then
Field C = .50 (which will be formatted as a % on the layout).
I was hoping that there would be some way to move the result of the expression (Field A * Field B) one decimal place to the right. Maybe I'm just having a brain freeze over this one, but I can't see a way to do it. (It's been a very long night.)