Is There a Way to Get the 90th Percentile Value for a Field From a Related Table?
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.