6 Replies Latest reply on May 6, 2015 5:25 AM by chf

    Graph Charting Surival Rate

    chf

      Title

      Graph Charting Surival Rate

      Post

      Hi there,

      I have an inventory that keeps track of fish.  Each line has a unique stock number that allows us to keep track of lines.  Every week, survival rates for a subset of fish are scanned in using filemaker go, and inputted as new records in a "survival rate" table.  The surivival rate table has the following fields:

      date of entry

      stock number (which is the unique fish line number)

      Number of fish estimated

      Within the "line details" section of the inventory, there is a tab where you can see survival rates in a line graph with date on the x axis and number of fish estimated on the y axis.  This is great when you are looking for one particular line, but I would like to find a way to show this graph for many lines on the same graph.  Is this possible? Does anyone have an idea for a good way to show this much data?  The thought I had would be to have a chart in which each stock number is its own series, but I do not know how to do this, nor whether it would even be feasible to look at that amount of data.

      We would like to be able to get a good summary of this data, so we can see overall how well fish are surviving and make appropriate changes. 

      Any suggestions would be greatly appreciated!

       

      Thank you!

        • 1. Re: Graph Charting Surival Rate
          philmodjunk

          Just to be sure I understand your request, would each fish line have it's own line (Y- series) on the chart? Or do you want to chart an aggregate value such as a sum or average that combines data from more than one fish line into a single charted line?

          Both are possible.

          • 2. Re: Graph Charting Surival Rate
            chf

            The best would be to have each fish line have its own y-series on the chart. Would there be a way to select only certain lines at once though?

            Thank you!

            • 3. Re: Graph Charting Surival Rate
              philmodjunk

              The number of lines charted would be static, but the Fish Line selected for each Y-series could be user selectable.

              Are you using FileMaker 13?

              Have you used SQL before?

              Have you used ExecuteSQL before?

              I'm thinking in terms of delimited data charting using a set of ExecuteSQL() queries for each Y-series, with a WHERE clause that references a global field to select for specific fish lines. You can then select a fish line in each of the global fields in order to chart them.

              • 4. Re: Graph Charting Surival Rate
                chf

                I am using FileMaker 13.

                 

                I have never used SQL or ExecuteSQL before, but that sounds like exactly what I would like to do.

                • 5. Re: Graph Charting Surival Rate
                  philmodjunk

                  If you've never used SQL, however, this becomes a pretty steep  learning curve that can be difficult to scale via a Forum Thread.

                  Let's start with a Non-SQL approach and see if that will work:

                  Inventory::StockNumber identifies the FIshLine and You one that is identified as "1" and another identified as "2". This not the final version--that will allow user selecting fish lines, but it can be modified to do that once it works for you.

                  Define a calculation Field, cChartLine1, as:

                  If ( StockNumber = 1 ; NumberOfFish )

                  Define cChartLine2 as:

                  If ( StockNumber = 2 ; NumberOfFish )

                  define sCL1 as a summary field that computes the total of cChartLine1. Define sCL2 to total cChartLine2

                  Select the two summary fields as different Y-Series. Specify a data source option of "found set" , Summarized data.

                  Sort your records by the Date of Entry field.

                  If that works, we can substitute global fields for the literal "1" and "2" values in the above If functions to make them user selectable.

                  • 6. Re: Graph Charting Surival Rate
                    chf

                    Would I put these new calculation fields in the "survival" table? or in the actual "inventory" table?  The reason I ask, is we could have as many as 2000 lines at the same time, and we do not track the survival of all lines, just a subet.