11 Replies Latest reply on May 10, 2011 12:43 AM by RestaurantCharlie

    Charting summary data

    DanBond

      Title

      Charting summary data

      Post

      I have 10 columns of numbers, and summary fields giving me the totals of each column. I'd like to make a line chart, with the x axis being the column #, and the y axis being the summary calculation. It seemed so easy, but it's been frustrating me for days. And I can't find it (at least in terms I can understand) in TFM. 

        • 1. Re: Charting summary data
          bumper

          Is the chart in a Grand Summary Part?

          If not create one (Layouts) and put the chart into the GS part.

          • 2. Re: Charting summary data
            DanBond

            Thanks Bumper, that may be the answer, but I don't see it.

            What I have in table view is a series of columns: a,b,c... followed by suma, sumb, sumc...

            The data in the first columns is sequential: a is at 0, b is at 4 hours, c is at 8 hours etc. So what I think I need is 2 fields: time and total. Time I can enter manually, but total would need to be:

            suma

            sumb

            sumc

            ...

            Then I just chart total vs. time and I'm done. 

            • 3. Re: Charting summary data
              bumper

              You do need two sets of data to create a line graph. So if you only have one then stop and review your schema. Hard to have a Y without an X.

              Otherwise: Are the suma, sumb, ... actual summary fields (as defined in manage datebase) or are the calc fields using the SUM function? If the latter then you would approach the data sets from a different direction, and place in a different layout part. if the former then:

              If they are summary fields are you sorting the data prior to showing the graph.

              • 4. Re: Charting summary data
                DanBond

                Yes, 2 sets of data: I'm entering the time (x values) by hand.

                I've tried summary fields and calculation fields using the SUM function. Both will give me the right answer, but what I really want is a graph of how the sums change over time. In a spreadsheet I would have a column called TOTAL that would look like this:

                TIME TOTAL

                0 =B25

                4 =C25

                8 =D25

                ... ...

                • 5. Re: Charting summary data
                  bumper

                  Ok but where is the column that has the time in it? And exactly what do the items, b25, c25, etc. represent, the graph engine needs numerical data (dates and times are kept as number within FM) in order to plot them on the graph. Please go into a little more detail about what it is you are trying to graph, what the original fields represent and what the time field should represent. Maybe then we can sort it out.

                  • 6. Re: Charting summary data
                    DanBond

                    Sorry I haven't been more clear. It seems like this is a general problem, and I wanted to avoid getting bogged down in details. 

                    I have a bleeding disorder. To treat this, I inject a clotting protein into my blood. It immediately begins to decay exponentially, with my half life being 23 hours. 

                    I have a table of infusions that has a timestamp of each infusion, quantity of the protein injected, and some other stuff. 

                    The "Calculations" table uses this information to calculate the protein remaining from each infusion. The columns in this table are:

                    "Timestamp of infusion", "remaining now" (using the Get ( CurrentTimeStamp ) function), "remaining in 4 hours", "remaining in 8 hours" etc. 

                    The total protein remaining in the blood at each time point is the sum of each of these columns. 

                    I THINK:

                    The "Graph Data" table contains: TIME; values are 0, 4, 8, etc. and the SUM of the "remaining now" column, the SUM of the "remaining in 4 hours" column, the SUM of the "remaining in 8 hours" column, etc. 

                    • 7. Re: Charting summary data
                      bumper

                      Ok, let me wake up and I'll give it a go. Thanks for explaining it. 

                      My first question is how you are calculating the time data, a Ts is a point in time, so are you just adding 0, 4, 8, etc hours to the initial Ts to do the calculation that gives you the time for each sum data? A Ts is just a number that represents the date and time from FM's version of when time began. To add an hour to a time or timestamp in FM you just add 3600. So to add 4 hours to your initial time you would just add 14400 (3600 * 4), etc. to get six records per day, you then would put the results of each calculation based on the six time points into the "sum" field. From there the graphing is pretty straight forward.

                      Are you looking to graph a single day or say a week or month, etc? And I'm not sure the calculation is a sum as such but more of a "point in time" result. And lastly how many records do you end up with when you go to graph a day, 0. 4, 8, 12, 16, 20 and 23 and then you start over for the next day? Or one record per 24 hour period with six fields, one for each time slot?

                      • 8. Re: Charting summary data
                        DanBond

                        Sorry. Been on the road all day.

                        Yes, I understand the time calculations. As I said, all the calculations are working fine. The only problem I have (see) is basically transposing a row of values to a column. 

                        Each column is a point in time- either NOW, or some number of hours into the future. The protein level at any of the chosen points is the sum of all the remaining protein from all infusions before "NOW".The number of columns is completely arbitrary. I can calculate 0, 1, 8,9,16 hours if I want. The total number of columns depends on the number of hours ahead I want to look. NOW + 72 hours is probably the maximum. 

                        • 9. Re: Charting summary data
                          bumper

                          OK, when you get your data are the two fields, time and amount, in separate record (there can be other fields, but not necessary to the charting) with one record for each time and the other a product of the calculation protein remaining based on that time? Are you having to do any sorting to make the calculation work (ie, summary fields)?

                          • 10. Re: Charting summary data
                            bumper

                            Dan, check your messages. 

                            • 11. Re: Charting summary data
                              RestaurantCharlie

                              Two things come to mind, and Bumper recently mentioned one:

                              1)Sorting: when using summary values on a chart, you need to perform a sort otherwise your graph will just show a straight line across your chart.

                              2)Charts can be a little troublesome sometimes. One way I've worked around the trouble is greating a chart based on return delimmited lists. Basically you have a script automatically go through your records and make a list of x and y each on a global variable, and then in the chart options you tell it to plot $$x and $$y and presto...