Search code examples
databaseexcelfilemaker

FileMaker 14 Graphing a Total Summary

Here is the example data in Excel format:

Data and Graphs

I have a name field, a time spent field, and a separate area that adds up the data per the name, as well as one that adds ALL of the hours and subtracts it from the total number that would be expected. The total number that would be expected can be an arbitrarily added calculation (it will eventually be the number of unique names * the number of expected hours). The top graph displays the data of each person's hours worked. So far, in Filemaker Pro 14, I have come this far. I can display that graph and and summary of each persons' hours. That bottom pie chart, however, is giving me trouble. I can only get the data of one person at a time in there, usually the first person to show up. I just can't figure how to calculate and ENTIRE field in one graph while summarizing by name in another.

Here's what I currently have in FileMaker:

enter image description here

Basically, where I need to go from here is to just get that pie chart to have the "Total" and "Uncategorized" sections where Total is the sum of everything under "timeSum" and "Uncategorized is the sum of everything under "timeSum" - 160, then I'd be done the whole thing. I've tried adding global fields, additional summary fields, sorting by a field that has the same variable for each record, and nothing works. The pie chart only displays the data for one person, whether it's in the sub-summary part or the grand total part.

Does this make sense? Any guidance would be appreciated.

Solution

  • If the data to be charted is an aggregation of all records in the found set, the chart should be placed in a grand summary part.

    A summary field defined as Total of [ TimeSpent ] would provide one of two required data points. The other data point is not quite clear; to take your example literally, the chart could be defined to show: 

    List ( sTotalTimespent ; sTotalTimespent - 160 )

    as the values, using data from current record (delimited data).

    Added screenshots:

    Data:

    enter image description here

    Layout:

    enter image description here

    Pie chart setup:

    enter image description here

    Result (records are sorted by Category):

    enter image description here