3 Replies Latest reply on Aug 30, 2012 10:35 AM by philmodjunk

    Need line chart, multiple data series

    smithchuck

      Title

      Need line chart, multiple data series

      Post

      Hi all,

      Below this description is a table with the data I'm trying to graph in various ways.  It's a project where each client goes through a number of neurofeedback sessions and provides a self-reported score on how they're currently feeling about a problem area (a "Treatment Goal" or TG).  So for example, in the first two data rows, Client ID 101, in Session Num 1, reported scores on two TG's: Attention (Score of 8) and Sleep (Score of 6).  He had the same pattern of data points through 4 sessions.

      Client 102 had three TG's on his first session (session num 1): attention, anxiety, and rumination.  But you'll see when we get to his 4th session, he has added an additional TG: Sleep.  There may also be some gaps in the data series (e.g., maybe client 101 didn't report a score for one TG for a particular session).

      I want line charts, and I'm using FileMaker Pro 11.  I can already pretty easily create a line chart that will show one data series for one client and one TG, say Client 101's Attention scores.

      What I'm having trouble doing is getting satisfactory line charts with more than one data series.  At this point the data points are ALWAYS going to be from the "Score" field, and the X-Axis labels are ALWAYS going to be "Session Num."  What I'd like to be able to do is easily create charts for multiple clients who have the same treatment goal, and charts for one client showing all of their treatment goals.  So for example, a line chart showing a line for all clients who have a problem with "Sleep" (each client's scores for Sleep represents one data series).  Or I'd like to be able to show all the scores for all treatment goals for one client (so I'd have one chart that shows everything that client 101 is working on, with one line per treatment goal).

      I used to be pretty comfortable with FileMaker in years past (through version 6, I think) but put it aside for a while and am now coming back to it, and the charting feature is totally new to me.

      Also, if any of you happen to be Excel gurus and know of an easy way to do this in Excel, I'd be happy to hear that, too.  Thanks!

      ...Chuck

      Session Num Client ID Score TG Description
      1 101 8 Attention
      1 101 6 Sleep
      2 101 8 Attention
      2 101 6 Sleep
      3 101 6 Attention
      3 101 4 Sleep
      4 101 7 Attention
      4 101 5 Sleep
      1 102 10 Attention
      1 102 7 Anxiety
      1 102 10 Rumination
      2 102 4 Attention
      2 102 5 Anxiety
      2 102 4 Rumination
      3 102 9 Sleep
      3 102 5 Anger
      3 102 4 Attention
      3 102 7 Rumination

        • 1. Re: Need line chart, multiple data series
          philmodjunk

          You've hit a known limitation to FileMaker charts. You can cart multiple data series in a FileMaker line chart, but only if each series is pulled from different fields. You'd need to put the data into a set of records where each record stores data for each Y-value in a different field. This can be done, but you'll need to set up a table for it and devise a script or relationships that pull the data into records in this new table.

          You also may have issues with this detail: "There may also be some gaps in the data series " If you leave a field empty to show the gap, fileMaker will chart it as a value of 0, if you want to show a smooth trend of data that ignores the gap, you'll need to calculate an interpolated value from the preceding and succeding values. y2 = (y3 + y1 ) / 2

          • 2. Re: Need line chart, multiple data series
            smithchuck

            Hmmm.  Well, thanks for the info on this limitation before I spend any more time on it!  I did run across a method that might work for me, although it causes the data points to not quite line up properly along the x-axis.  (It was the final example in this document http://help.filemaker.com/ci/fattach/get/52672/ and called "Charting Multiple Data Series from One Source Field").

            The particular project didn't seem to lend itself to having separate fields in the same record for these scores.  Client 1's treatment goal #1 is often different from Client 2's treatment goal #1.  Or I could have created a separate field for each treatment goal (Sleep, Anxiety, etc.), but then no doubt a new client would come along with a treatment goal we hadn't considered before ("can't stop thinking about circus clowns") and then that would require a database redesign!

            I'm still sort of wondering if a self-join might let me get around the issue, but it's been years since I played with those.

            ...Chuck

            • 3. Re: Need line chart, multiple data series
              philmodjunk

              I suggest that you not modify the design of your current table, but to define a "charting" table and devise a method to pull your data into fields of that new table. Pairs of text and number fields can be used. The text fields can store the specified goal and the number fields store the values you want to chart. You can have individual records for each point on the graph for a given series, just different fields in the same table for values from different data series that share the same x axis value.