AnsweredAssumed Answered

[Q] Method to Chart a Distribution Curve of Related Records?

Question asked by JPScott on Jan 6, 2017
Latest reply on Jan 6, 2017 by jbante



I have a Product data file where each record represents a different product (~500 products).  I have another large Analytical data file (more than a million records) which contains test data about the individual products as they are made (i.e. attribute, tested value, date, min. specification, max. specification) etc. Note that each Product can have from 5 to 20 attributes.


The Product database is currently set up so that if I go to a product record, I can select a particular attribute and then within a portal can see what the tested analytical data is for all items made. For example I can go to the record for Product A, select an attribute such as "weight" and then see within a portal all the weights of Product A produced.


I also have a chart on this layout which pulls the related record info and displays the attribute value as a function of time along with the low and high specifications (i.e. to see how the historical data compares to the specifications and how it may have changed over time.). I can also calculate the statistical data (i.e. avg., standard deviation, % out of spec. etc.) using Aggregate calculations on the related records.


So far so good.


Where I have a question is I'd also like to chart a distribution curve on the same layout showing the number of times that a particular value was achieved (for a chosen product_attribute data set).


If possible I'd like to just use the related data already available  [i.e. I could potentially use the measured data for the x-axis of the chart and then for the y-axis hopefully have some sort of summation or calculated function which shows the number of records which match that exact reading and/or which are < the given reading].  Is something like that available?  I'm using FMP 13.


Note I'd prefer NOT to use third-party software if at all possible (although otherwise I'd be interested in knowing what could provide this solution) due to corporate purchasing difficulties. Also I'd prefer to just use the data sets already available to me rather than to go through a lot of hoops to construct the distribution chart.   One reason is that the Product-Attribute combinations are poorly defined, there can be wide variation within data sets, and the precision can vary widely as well (i.e. some data sets may contain data to the hundredths decimal place while others may be integers or reported to the nearest 100), so I'm not sure how that may impact a solution where the data has to be generated on the fly in order to construct a chart.  I'd also prefer not to have to run a script which jumps into the Analytical data file and modifies that file, if I can avoid it.


Any suggestions?