5 Replies Latest reply on Aug 10, 2013 2:40 AM by MikhailEdoshin

    Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?

    Spencer_L

      Title

      Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?

      Post

           I'm hoping someone might be able to answer this question for me... thank you for taking a moment to read this post.

           I have two tables, Table A and Table B.

           Table A contains about 2.4 million records. Table B contains 24 records. The tables are related through four fields (Site, Month, Year, Reg).  

           One of the fields in Table A ( Table_A:ED_Calculated ) returns a numerical value. A corresponding summary field has been created in Table A ( Table_A:ED_Calculated_Average ).

           Table B has five fields (the four related fields and a fifth calculated field called ED which returns the summary field (Table_A:ED_Calculated_Average) from Table A.

           This two table set up has been working well for me. I use it to design and populate charts from the context of table B.  

           What I would like to do is introduce a sixth field in Table B,  which will return the 90th percentile value of the ED_Calculated field for the related records from Table A. I don't know how to do this.

           I've searched for advice on various forums and search engines for percentile functions in filemaker, but haven't had any luck yet.

           I've tried estimating the 90th percentile using a statistical approach (mean plus a multiple of the standard deviation) however that approach hasn't worked well as the data has a skewed distribution.

           Any advice or suggestions would be appreciated.

        • 1. Re: Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?
          MikhailEdoshin

               If you sort the related records (this is going to be rather slow) you can use the GetNthRecord() function to fetch an arbitrary record and thus calcualte the percentile. From what I see in Wikipedia there are different definitions of a percentile, so if you share the one you plan to use I can sketch the formula, I guess.

          • 2. Re: Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?
            Spencer_L

                 Thanks for your reply Mikhail

                 The percentile definition that I'm interested in is the threshold for which 90% of records have a lower value.

                 Would the approach you are suggesting work in a calculation field, or would a script be required?

                 ...

                 I've been thinking  about alternative solutions which would work in a calculation field since my original post.

                 I've looked into using an ExecuteSQL function to supply a result in a calculation field. I don't have a good understanding of SQL, but it seems there are SQL functions like NTILE, SELECT TOP _ PERCENT, and PERCENT_DISC which would be useful, but they don't appear to be available in the Filemaker native SQL.

                 I suppose one could use the ExecuteSQL function to select and order the records. If that ExecuteSQL result was embedded in a List function, then the filemaker function GetValue could obtain the Nth record from the resulting list. 

                 A separate ExecuteSQL function with the same filters could give the record count, and that could be used to calculate 'N' which corresponds to 90th percentile.

                 I'm going to try that. I'll report back on whether it worked.

                  

                  

            • 3. Re: Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?
              philmodjunk

                   If that ExecuteSQL result was embedded in a List function, then the filemaker function GetValue could obtain the Nth record from the resulting list.

                   Actually, the results of an ExecuteSQL function call will be that very list of values if you specify a return as the "Record separator" value.

                   The same results can be achieved without ExecuteSQL, the main difference is that ExecuteSQL lets you get the whole thing done in a function call without having to make changes to your relationship graph like you would to get the same result without SQL.

              • 4. Re: Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?
                Spencer_L

                     That worked very nicely.

                     Thank you for your ideas and help.

                     I'm realizing that ExecuteSQL is a very useful function.

                      The formula appears below. It does two SQL queries on Table A fields (Month Name, Reg, Site, Year) using related values from Table B fields (Month, Reg, Site, Year) . The first query returns the ED_Calculated field from a selection of records from table A in a sorted list. The second query returns the count of records in that list. the ".9" selects the record which is at the 90% position in the list.

                     GetValue (

                     ExecuteSQL
                     (
                     "SELECT a.\"ED_Calculated\" as ED
                     FROM \"TABLEA\" AS a
                     WHERE a.\"Month Name\" = ? AND a.\"Year\" = ? AND a.\"Reg\" = ? AND a.\"Site\" = ?
                     ORDER BY ED ASC" ;
                     "" ; "" ; Month ; Year ; Reg ; Site
                     )

                     ;

                     Int ( .9 *

                     ExecuteSQL (
                     "SELECT COUNT(a.\"ED_Calculated\") as ED
                     FROM \"TABLEA\" AS a
                     WHERE a.\"Month Name\" = ? AND a.\"Year\" = ? AND a.\"Reg\" = ? AND a.\"Site\" =?
                     ORDER BY ED ASC"
                     ; "" ; "" ; Month ; Year ; Reg; Site
                     )

                     )

                     )

                      

                      

                      

                1 of 1 people found this helpful
                • 5. Re: Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?
                  MikhailEdoshin

                       Without ExecuteSQL() this would be like that: sort the relationship from B to A by ED Calculated (ascending) and define the following field in B:

                       GetNthRecord( Table A::ED Calculated; Round( Count( Table A::<some non-empty field> ) * 0.9 ) )

                  1 of 1 people found this helpful