12 Replies Latest reply on Sep 28, 2012 10:21 AM by mr.fixit

    charting records on a timeline

    mr.fixit

      I have a DB where every record is an "event" and the date-time of the event is in a timestamp field.

       

      I want to graph the events on a timeline, to give a sense of when the events occured, their frequency, etc.

       

      The best I've come up with so far is to create a 2nd calculated field, 'day', = timestamp / 86400 + 1. A third field is 'day_count', a summary field = 'count of day'. I sort by 'day', put the 'day_count' in a sub-summary field, and create a chart with X='day' and Y='day_count'. I then get the following:

       

       

      This isn't great because it doesn't convey the elapsed time between the events. I want the x axis to be a timeline, so the distance in time between the events would be proportional to the distance in the x-coordinate.

       

      I imagine this is a FAQ, but I can't find it.

       

      All pearls gratefully accepted.

        • 1. Re: charting records on a timeline
          mr.fixit

          this image was meant to be in the original question...chart1.png

          • 2. Re: charting records on a timeline
            comment

            Filemaker will not "space out" the columns according to the x-axis values. You must provide the chart engine with actual data points - either real or dummy ones.

            • 3. Re: charting records on a timeline
              beverly

              Research "Gantt chart" on this forum and elsewhere. MH is right, you have to have 'values to chart'.

               

               

               

              -- sent from my iPhone4 --

              Beverly Voth

              --

              • 4. Re: charting records on a timeline
                mr.fixit

                A Gantt chart is for events that have a start and stop date, yes?  I just have one date.

                • 5. Re: charting records on a timeline
                  mr.fixit

                  What's not 'real' about a timestamp? Say I have 3 events, where X (day)  = { 0, 3, 4 }.  Why isn't the distance (on the x axis) between the 1st and 2nd be 3 times that between the 2nd and 3rd?

                  • 6. Re: charting records on a timeline
                    comment

                    mr.fixit wrote:

                     

                    Why isn't the distance (on the x axis) between the 1st and 2nd be 3 times that between the 2nd and 3rd?

                     

                    Because Filemaker does not examine the x-values of the data points. In your example, 0, 3 and 4 are text labels, and their content is irrelevant; the chart would look the same with [A, B, C] as the x-values.

                     

                    If, however, your x-values were [0, 1, 2, 3, 4] and your y-axis value series would have null values inserted to correspond with the dummy x-values, then you would have the chart you are looking for.

                    1 of 1 people found this helpful
                    • 7. Re: charting records on a timeline
                      mbraendle

                      This is something I sent feedback to FileMaker Inc. immediately after they had introduced charts.

                      There are not only business graphs, but also graphs for time series, scientific and engineering data, for example. And for these, the x axis must be scaled correctly. Excel is able to do this if one changes to the Point diagram type.

                       

                      Having to insert dummy records to obtain a correct visualization is just silly.

                       

                      FileMaker Inc. so far has failed to update the charts. It would be just so easy: As for the y-axis in the format dialog, offer also for the x-axis the option to set a min and max value. Or a tickmark for setting the x-axis as "proportional axis".

                       

                      This failure makes charts in FM Pro absolutely valueless for many cases.

                      1 of 1 people found this helpful
                      • 8. Re: charting records on a timeline
                        beverly

                        Can you calculate what needs be charted?

                        Remember that charts are NOT just data from fields, they can also be composed of a delimited list (as with any list in FMP, return-delimited).

                             http://help.filemaker.com/app/answers/detail/a_id/10227/~/introduction-to-charting-with-filemaker-pro-12

                         

                        And start with the fmp12 help topic "Creating charts from data":

                             http://www.filemaker.com/12help/html/create_chart.12.1.html#1135876

                        there are three examples of charting: summary data, delimited data, related records

                         

                        IF you have some data and need to "fill in the blanks", can you do so with calculations? Once filled in, this can be use for charts.

                         

                        Beverly

                        • 10. Re: charting records on a timeline
                          mr.fixit

                           

                          Michael Horak wrote:

                          ...  Filemaker does not examine the x-values of the data points. In your example, 0, 3 and 4 are text labels, and their content is irrelevant; the chart would look the same with [A, B, C] as the x-values.


                          If, however, your x-values were [0, 1, 2, 3, 4] and your y-axis value series would have null values inserted to correspond with the dummy x-values, then you would have the chart you are looking for.

                           

                           

                          Thanks for clarifying that, Michael.  That's not at all clear from the documentation.  All examples are shown with a complete set of data, no missing gaps in the x-axis.  Imagine if the same operation were done on the Y-axis. 

                           

                          I agree with Martin Brandle, this is a deficiency.  Converting the X values from real (in the mathematical sense) numbers to a set of labels cripples charting for many real-world [sic] applications. 

                           

                          Yes, I could create fake records such that every day from { day0 ... dayN } was represented.  But since my y-axis is 'N records that day', I'd then have to fiddle with things so that the fake days don't have a count of '1'.

                           

                          I'll be looking at Excel.

                          • 11. Re: charting records on a timeline
                            comment

                            mr.fixit wrote:

                             

                            Yes, I could create fake records such that every day from { day0 ... dayN } was represented.  But since my y-axis is 'N records that day', I'd then have to fiddle with things so that the fake days don't have a count of '1'.

                             

                            It's not that complicated:

                            • 12. Re: charting records on a timeline
                              mr.fixit

                              Michael, that's pretty slick.

                               

                              I like your technique of using a 2nd table 'dateslots' to count the events, and how you use global variables to define how time-range that 2nd table is looking at.  I hadn't considered using relationships to create the count, I'd only known about the 'summary field, count when sorted by' technique. Thanks also for wrapping it up in a pretty package. 

                               

                              Of course, I would prefer to NOT have to slot my data, and create a secondary table, etc, but this is better than exporting to another charting app.