3 Replies Latest reply on Feb 5, 2016 5:08 AM by AllegroDataSolutions

    Calculating percent rank


      How do you calculate percent rank in FM with some requirments:

      1. Dynamic calculation as data is entered or imported
      2. Duplicate numbers have same percent rank
      3. Zero value has 0% (percent rank)
      4. Results in same value as excel's percentrank.inc function
      5. Calculated for individual month within yearly data (for example January would have a ranking of expenses, February ranking, etc)

      Thanks for any help provided


        • 1. Re: Calculating percent rank

          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 re-calculate 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 server-side script that looks for records with a recent modification timestamp, then re-calculates 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 record-scan 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 quantile-setting 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 back-and-forth record scanning or counting through a special-purpose 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 re-computed 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 tie-handling 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 return-delimited 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

            Thanks so much, I'll give it some thought and try to get it incorporated into my database.


            • 3. Re: Calculating percent rank

              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


              Field B = 10


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