4 Replies Latest reply on Nov 19, 2013 10:34 AM by ianmanning

    Calculating data for a chart x+y, x+2y, x+3y ...



      Calculating data for a chart x+y, x+2y, x+3y ...


           I have to chart  a fairly simple set of values but I am struggling to work out how to calculate them.

           Essentially it is comparing two products, one is high initial cost but low energy consumption the other is low cost , high energy consumption.

           The data is in a single record so each record will have its own graph.

           the X period is years 1,2,3...    the number of Periods (years) is calculated as hours life / hours per year ie 50000 hours life, used 9 hours per day, 5 days per week 52 weeks per year. = 50000 / ( 9*5*52) = about 21 periods

           The data points for the Y axis need to be calculated 21 times as follows:

           X=1   Y = Initial cost + energy used

           X=2   Y = Initial cost + 2*energy used

           X=3   Y = Initial cost + 3*energy used

            and so on until

           X =21  Y= Initial cost + 21*energy used

           I have done this manually and entered it in to a text field delimiting each Y value but I'm sure that this must be possible to be done with a calculation.

           the second Y series is very similar to the first and I'm sure if someone can show me how to build the calculation for the first series I can adapt it for the second.

           This is the first time I have used the chart feature in filemaker so I may be missing something obvious, feel free to treat me like a novice. Running Filemaker 12, pro, advanced and server.





        • 1. Re: Calculating data for a chart x+y, x+2y, x+3y ...

               If you have a table of 21 records with a number field for x and a calculation field for y, you can easily chart this data. The trick is to avoid having to enter initial cost and energy used more than once. There are two ways to do that.

               You can put these values into global fields. The value in a  field with global storage will be accessible to all the records in your table. Then your calculation for y becomes:

               gInitialCost + X * gEnergyUsed //Iuse 'g' as the first letter of all fields for which I select global storage.


               You can put these two values in a related table with a relationship that matches all 21 records in your table of chart data to the same record where you have entered that data. That will produce a slightly different expression:

               RelatedTable::InitialCost + X * RelatedTable::EnergyUsed

               Either way works and which is "best" will depend on other design and use factors that I can't perceive from the information posted thus far in this thread.

          • 2. Re: Calculating data for a chart x+y, x+2y, x+3y ...

                 Thanks for the quick reply.

                 The problem is the there are hundreds of records and each record has a chart on it. Also the number of periods changes depending upon the use per year. ie Life time of 50000 hours used 9 hour a day requires 21 periods the same light left on continuously only requires 6 periods.

                 I did think that I could create 20-30 fields on each record, each one calculating a specific period then a final field to combine all the results into a single series, but I'm sure it must be able to do the calculations in one go and generate the delimited field with the required number of values.

                 Can a loop be  made in a calculation field or is it only in scripts. I think I could do this with a script.



            • 3. Re: Calculating data for a chart x+y, x+2y, x+3y ...

                   This looks like data that should exist in a table related to your current table of hundreds of records. There's a data source option for related data that you can use for this purpose. And these records can be any number of records that you need for that specific record.

                   And a looping script can take the data in a record in your current table and loop to produce the needed related records for your chart data.

              • 4. Re: Calculating data for a chart x+y, x+2y, x+3y ...

                     Thanks for your reply,

                     I ended up writing a script that looped the required number of times to build the delimited string  I needed for the chart.

                     I then added a script trigger to run the script when the data changes.