5 Replies Latest reply on Oct 17, 2011 1:12 PM by philmodjunk

    Charting... X-axis values labels display question

    tcmeyers

      Title

      Charting... X-axis values labels display question

      Post

      I'm charting data gathered with FileMaker by querying a PLC, and I haven't figured out how to nicely label the X (time) axis. The problem is that there are typically 1000 records being charted, with one or more collected values being dispayed, and each record carries a timestamp, which is the value used in the chart for the X axis. The way I'm doing it (the only way I've figured out) the timestamp for each data point is printed below the X axis, and since they are so close together, this produces a "smear". What i had hoped to do would be to label the X axis only every 30 minutes or so. I figured out a calc on the timestamp that would produce text only at each 30 minute mark, but wasn't able to find a separate way to set that as what should be shown below the X axis.

      I've attached a screen shot of a chart. Am I missing something?

      -Troy

      Test_Chart.png

        • 1. Re: Charting... X-axis values labels display question
          philmodjunk

          Interesting challenge.

          I would think your calculation would work for this if it produces an empty result in all cases except those that fall on the 30 minute interval. You can specify such a calculation in the chart setup. Haven't tried this though so I can't be positive that it will work.

          More sophisticated means for controlling axis values and labels in FileMaker Charts would make a very welcome future improvement...

          For those that agree, you can use the Feature Suggestion Form to so inform FileMaker Inc.

          • 2. Re: Charting... X-axis values labels display question
            tcmeyers

            Phil,

            As it turns out, YES the calculation works fine. When I wrote it, I stopped before even trying it, because I said to myself, "Wait, this won't work, it'll only plot points at the half-hour marks." What I didn't realize is that what I thought was the "X-axis value" is NOT a value, it's the LABEL. The value used for the X axis is the record number in the found set! I had thought that irregular time periods would be plotted appropriately based on the "X-axis value", say, if the data only had 5-second-spaced samples for part of the time, and 1-second-space samples for another part, that the 5ers would be plotted further apart from each other. But that's not the case. Each record is a vertical column of equal width, with the Y axis value plotted in it, not an X-Y scatter of points based on 2 values.

            So, the method does work. I've attached a second stab at the chart which shows the result, but as a demonstration of the shortcoming, I made 60 duplicates of the 19:30 data point to illustrate that that data comes at the end (where duplicates go, record-wise, unless sorted) rather than stacking points up at the presumed 19:30 spot on the chart. The smear at the end is a bunch of 19:30 texts stepping on each other. Also, the overshoot is because I was using curve-smooth, which, considering the data, isn't the right choice since it's pretty smooth anyway.

            So, to really make this work the way I want, I think I need to create a table of time periods, say each one with a start and end time five seconds apart and sequential, and make a relation to the logged data to get and average any logged data in that period. That way, even if the data is jagged in the logging period, the spacing of the display points will be time-consistent.

             

            -Troy

            • 3. Re: Charting... X-axis values labels display question
              philmodjunk

              I think that you'll need to interpolate data points for records where the time interval does not include a value in order to keep Filemaker from charting a zero value for that record.

              • 4. Re: Charting... X-axis values labels display question
                tcmeyers

                Phil, you are right. Here's the same chart with data holes in it. The missing values are blank, not zero, but are interpreted as zero I guess. This is with poly segments selected rather than curves, which looks even worse.

                -Troy

                • 5. Re: Charting... X-axis values labels display question
                  philmodjunk

                  Do you know how to interpolate the data? (Ironically, I first ran into this in an Excel chart years ago and used VBA with MS Access to interpolate the missing points to get the proper curve in the chart.)

                  Basically, you can use a looping script and the point-slope formula for graphing a linear relationship between two points to fill in the needed missing data points.