3 Replies Latest reply on Jan 6, 2017 11:23 AM by jbante

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




      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?

        • 1. Re: [Q] Method to Chart a Distribution Curve of Related Records?

          I suggest researching 3rd party statistical analysis packages if you can possibly scrape up the budget for it.

          The analysis and charting tools in such a package will far outstrip what is natively available in FileMaker.


          I recall working in a QA lab (I managed their DB's and documents) and they had a stat package specifically for analyzing their QA test results. It accepted excel files as a data source and I would expect just about any such software product to support the same option as excel files are a very common file format for exchanging data. FileMaker can export to this file format so moving the data out of FileMaker and into such a stat package for purposes of analysis should be something fairly straight forward to set up.

          • 2. Re: [Q] Method to Chart a Distribution Curve of Related Records?

            Thanks for the advice, but the statistical analysis is actually a very small subset of the database and not the primary focus.


            I do have access to other software which can provide more in-depth statistical analysis, I was just looking to have some rough tools to get some quick and easy insight into the trends within FMP without having to fire up a completely different system.

            • 3. Re: [Q] Method to Chart a Distribution Curve of Related Records?

              You could make a rough histogram with a FileMaker bar or line chart by calculating bin counts yourself. For a better-looking result, or one where you don't have to pre-calculate the bin counts, look into using a Javascript charting tool in a web viewer, like this example.