8 Replies Latest reply on Jul 30, 2012 11:05 AM by DrewTenenholz

    Graphing in FM12 - Medical data

    DrewTenenholz

      All --

       

      I'm looking into the new using native graphing abilities in FM12 for a solution and have a question. Given the dummy data set for a dummy patient's liver function tests below, is it possible to graph the data in the same way that is already being done using the xmChart plugin? I've included screen shots for xmChart followed by FM12.

       

      The main thing I'm seeing right now is that when a specific test was done on certain discountinuous dates, the line that connects the data points is also discontinuous. This is not an acceptable way to display the data. Any thoughts on how to make the chart look right?

       

      Thanks,

      Drew Tenenholz

       

      The original Data Set

      Header 1Header 2Header 3
      DateTestData
      08/03/09ALT35
      03/04/10ALT11
      04/25/10ALT17
      10/07/10ALT22
      10/11/10ALT30
      01/16/12ALT9
      05/12/12ALT19



      09/03/08ALT (SGPT)29
      01/20/09ALT (SGPT)24
      11/28/11ALT (SGPT)16



      06/12/09AST15
      08/03/09AST19
      03/04/10AST14
      04/25/10AST37
      10/07/10AST18
      10/11/10AST21
      01/16/12AST11
      05/12/12AST20



      09/03/08AST (SGOT)28
      01/20/09AST (SGOT)24
      11/28/11AST (SGOT)20



      12/27/10SGOT (AST)24

       

       

      The Data set arranged to explicitly give values for each test for each unique date from the data above

      DateALTALT (SGPT)ASTAST (SGOT)SGOT (AST)
      09/03/08
      29
      28
      01/20/09
      24
      24
      06/12/09

      15

      08/03/0935
      19

      03/04/1011
      14

      04/25/1017
      37

      10/07/1022
      18

      10/11/1030
      21

      12/27/10



      24
      11/28/11
      16
      20
      01/16/129
      11

      05/12/1219
      20

        • 1. Re: Graphing in FM12 - Medical data
          ch0c0halic

          Drew may need to airbrush the results,

           

          Yes this can be graphed. Using ExecuteSQL() and setting the chart to graph a single record, not all records.

           

          1. Define each series in the Y-axis as an ExecuteSQL statement that grabs a single test type, like "ALT".

          2. Set the X-axis as ExecuteSQL() getting Distinct date values.

          3. Set graph "data Sources" to "Individual Record Data".

          • 2. Re: Graphing in FM12 - Medical data
            RonSmithMD

            First of all, you should probably create a standard lab test descriptor field. AST=SGOT and ALT=SGPT anyway so don't have multiple descriptions for the same thing. In other words in your second chart there should be only two data columns besides the dates.

             

            Ron

            • 3. Re: Graphing in FM12 - Medical data
              DrewTenenholz

              Jimmy --

               

              Thanks for the quick response.  I'm was planning to use 'delimited data' for the chart, and in testing I've manually entered the data set included in the original post.

               

              If you look closely at both the data and the two charts, you'll see that I want to overlay the lab results from several different tests done during a certain time period.  On any given day when tests were done, not all of the tests were done.  But, I want a line graph that connects the dots for each of the test results throughout the range.

               

              From the previous data, in FileMaker I get a pretty good line connecting the AST results from 6/12/09 - 10/11/10, but then the line stops and picks up again for the final two dates, 1/16/12 and 5/12/12.  With xmChart, I have a connected line for all of the results from this lab test.  Similar story with the ALT (SGOT) and AST (SGOT).  They have results for the first two unique dates in the series and then their lines should connect to the result on 11/28/11.  In FileMaker, I get two dots standing alone.

               

              To me, this points to a basic conceptual flaw within the graphing engine -- it can't overlay the graphic representation of different data sets within a single graph.  This is something science & medicine do all the time, whether it is a scatter graph with regression line overlay, multiple lines in the way I'm trying to get now, bars and lines together, and the as yet always missing error bars.  If the engineers can create it and the interaction team can make it simple enough to pass into production, you'd have something really useful.  I am still frustrated that we are 'stuck' with the lowest common denominator of business math, where it seems that profits always rise and no one ever makes a mistake....

               

              xmChart does all of this, and we can continue to use it, but are hoping to reduce plugin use and unify the code.

               

              By the way, this is the absolute simplest graph that is in use in this system.  Displaying a graph of a child's height or weight against the standard growth curves for each statistical decade is a bit more complex.  It looks like a ten smooth curves rising geometrically with each reaching their own plateau which is then overlaid with the data from the actual patient (each data point being an office visit where growth has been measured) as a straight line graph with dots.  The standard data lines all have the same color and no dots which allows the patient data to stand out.  If we can tackle the simple graph, this is next on the list.

               

              -- Drew

               

               

               

              >Yes this can be graphed. Using ExecuteSQL() and setting the chart to graph a single record, not all records.

              >1. Define each series in the Y-axis as an ExecuteSQL statement that grabs a single test type, like "ALT".

              >2. Set the X-axis as ExecuteSQL() getting Distinct date values.

              >3. Set graph "data Sources" to "Individual Record Data".

              • 4. Re: Graphing in FM12 - Medical data
                DrewTenenholz

                Ron --

                 

                Yes, I saw the unnecessary division of records that are really the same information, and there are routines to clean this up within the EMR.  Even with this data set fixed up, though, there would be no value for ALT on 6/12/09 and 12/27/10.

                 

                As you know, this sort of missing data happens all the time in medicine.  You send blood work out and get your results back and some test wasn't done; they claim your order wasn't clear, or there wasn't enough sample (i.e. someone at the lab dropped it on the floor), etc. etc.  The fact is this is a reasonable example of the data that comes into the EMR and needs to be graphed.

                 

                I'd also really love to be able to put up two different Y-axes ranges for the overlay of data that is measured in very different units, but that's clearly not available in FMPro yet.

                 

                -- Drew

                • 5. Re: Graphing in FM12 - Medical data
                  ch0c0halic

                  Drew may need to understand 'data',

                   

                  You have a blank record between each sequence so of course your datapoints are disconitnuous, Because your graph shows data per record.

                   

                  I still maintain if you grabbed the data using ExecuteSQL() you'd get a continuouse chart for each data series. Not a discontinuous series of dots.

                   

                  For a data series that looks like this.

                   

                  Date          Count    Country     

                  7/23/2012     105     CA

                  7/23/2012     782     US

                  7/24/2012     105     CA

                  7/24/2012     782     US

                  7/25/2012     105     CA

                  7/25/2012     782     US

                  7/26/2012     104     CA

                  7/26/2012     776     US

                  7/27/2012     105     CA

                  7/27/2012     776     US

                   

                  I show a graph like this:

                   

                  sample_cntry.png

                   

                  It appears your graph is showing per record where some records are completely empty. I don't expect FMP to know how to connect the dots when there is no data to use.


                  • 6. Re: Graphing in FM12 - Medical data
                    RonSmithMD

                    xmChart does all of this, and we can continue to use it, but are hoping to reduce plugin use and unify the code.

                     

                    By the way, this is the absolute simplest graph that is in use in this system.  Displaying a graph of a child's height or weight against the standard growth curves for each statistical decade is a bit more complex.  It looks like a ten smooth curves rising geometrically with each reaching their own plateau which is then overlaid with the data from the actual patient (each data point being an office visit where growth has been measured) as a straight line graph with dots.  The standard data lines all have the same color and no dots which allows the patient data to stand out.  If we can tackle the simple graph, this is next on the list.

                     

                    Like this below which I've used XMChart to do in our PaperCutPro EMR/Practice Management software for over ten years:

                     

                    PreviewScreenSnapz001.jpg

                    • 7. Re: Graphing in FM12 - Medical data
                      RonSmithMD

                      You are absolutely correct about the differences with xmChart vs FMPro charting. As you can see from what we've done for years with xmChart, there are few limits to its graphing capability. xmChart is everything and the kitchen sink. The results are so good for what I need to do in the growth charts, that is it well worth the time. Parents like it a lot when they can see things in a compact way rather than three full growth chart sheets.

                       

                      But xmChart is not for the faint of heart either. You need to be a really geeker developer like me (as you can see from my Code128 and other documents) to want to stretch the limits like that.

                       


                      Ron

                      • 8. Re: Graphing in FM12 - Medical data
                        DrewTenenholz

                        Jimmy --

                         

                        I've been thinking about my question and what I really wanted when I posted.  I've boiled it down to:

                         

                        A) Can FileMaker graph the data sets that I actually have?  and if not,

                         

                        B) What is the best way to describe what I need in a way that can be presented to the FMI development team so they can understand it clearly.

                         

                        Maybe I can help you understand the question I'm asking by modifying the data set you presented with one simple alteration:  you get US data on even-numbered days and Canadian data on odd-numbered days.  Is there any way (whether with 'delimited data', a calculated field in a single record that represents delimited data, or a found set) that FileMaker could graph that?

                         

                        I could go on quite a bit about all sorts of graphing I'd like to do, and I see it as a question of how to overlay the representation of one data set on the representation of another data set, but there may be a better way to describe it.

                         

                        I'm just a bit frustrated about the current graphing abilities for my needs. I think that improvements would really open up FileMaker to a whole segment of science-related fields that currently use other tools (and frequently have to combine a data-collection tool with a data-analysis tool, with all of the inherent time lost).  I also understand that the ability to graph a live data set is truly wonderful, and all of the hard work that went into making the quick chart tool work so easily and elegantly is a great display of the FMI team's abilities.  Like many people, I just want more.....

                         

                        -- Drew