3 Replies Latest reply on Sep 27, 2010 3:22 PM by philmodjunk

    Charts: Best way to set up continuous X-Axis values?

    jonasg

      Title

      Charts: Best way to set up continuous X-Axis values?

      Post

      Okay,

      using FM11's charts, I'm trying to plot some data (Y-Axis) against a linear sequence of X-values. Example:

      I want to plot a project's completion over time, using a table progress_over_time(date_recorded, progress). Although the diagram works as it should, it does not display GAPS in the data table. This issue is described here:

      Charting dates

      There must be one or more workarounds for this. I'm just wondering if anyone has ideas for what works best. Some problems to consider:

      1. Getting a daily data point. I could create/overwrite a record with today's progress via script whenever the file is opened. However, opening it on monday will leave a 2-day data gap if no one touched the file over the weekend. Maybe said create/overwrite-script could fill in a data point for every day since it was last opened, but that would lead to the next problem:

      2. When filling gaps between data values A and B, one needs to create a smooth, linear transition of values so that there are no sudden jumps in the graph...

      3. (most importantly) What is the most elegant way to create a list of all days, starting from the first to the last available progress record?

      I'm afraid the answers might be "scripts, scripts, scripts" (and I could probably do it that way), but it just doesn't seem very elegant to me. I prefer working with the relationship graph, as you never know if a script gets interrupted (not even mentioning IWP issues).

      Does anyone have any ideas? Thanks!

      Jonas

        • 1. Re: Charts: Best way to set up continuous X-Axis values?
          philmodjunk

          One method I have used (I wasn't working with FileMaker charts at the time, but Excel charts had the same limitation): You can compute a straight line interpolation that computes a new point between known points to smooth out the data plotting.

          This method computes the equation of a straight line from (x1, y1) to (x3, y3) and then computes (x2, y2) from the resulting equation.

          Assuming X1, X2, X3, Y1, Y3 are known values:

          Let ( m = ( Y3 - Y1 ) / ( X3 - X1 ) ; Y2 = M * X2 + Y3 - M * X3 )

          Will compute this missing Y2 value for the given value X2.

          Thus if you had a gap spanning Saturday and Sunday, you could use data values for Friday and Monday and plug in x values for Saturday and Sunday to get the interpolated Y values for these two days. And yes a script can be defined that loops through your data generating new records with interpolated points everytime a gap is identified.

          • 2. Re: Charts: Best way to set up continuous X-Axis values?
            jonasg

            Thanks, that makes sense.

            How would you go about (temporarily) creating all the X-Values for the diagram?

            Assuming we have 3 data points: one for April 20th, one for April 21st, and one for May 2nd. One would need 13 dates for the X-axis... How about keeping a date table with pre-generated dates from 2010 to 2030 and using theta joins in the relationship graph to set the correct range? Once again though, that's 7300 mock records– not my idea of elegant :-/

            • 3. Re: Charts: Best way to set up continuous X-Axis values?
              philmodjunk

              This method isn't really practical except to fill in small gaps. The larger the gap, the more likely your results may be misleading.

              All you need is a loop that starts with the last known X before the start of the gap and then increments by a regular value, such as one day until a value greater than or equal to the first known X on the other side of the gap is reached. Date fields in FileMaker may be treated as number fields loaded with integers that count the number of days from an early arbitrary date. If the value of a Date field is 9/29/2010 (MMDDYYYY format), Add one to it and you'll get the date 9/30/2010.