1 Reply Latest reply on Jul 31, 2012 10:13 AM by philmodjunk

    How to do "running total" on a field when another field is the subsummary field?

    maser

      Title

      How to do "running total" on a field when another field is the subsummary field?

      Post

      What I want to do -- using FM 12 -- is generate a line graph of the running total counts of a couple of field values when sorted by another field.    But I'm not seeing how I would do this without the need to generate a lot of additional calculation fields, so I'm hoping somebody might point me in the right direction.

       

      In a simplest description, the fields would look like this with some test data:

      Year, Type

      2000, A

      2000, B

      2001, A

      2002, B

      2003, A

      2003, A

      2003, B

       

      SO, I want to generate a report (so I can line graph it) that would give me *running totals* of the values of "type" in addition to the overall running total of types A and B so I can plot all three Type values each year (a fairly standard line graph...)

       

      So values from a back-of-the-envelope calculation (from the above data example) would be:

      Year, running total, running total of A, running total of B

      2000, 2, 1, 1

      2001, 3, 2, 1

      2002, 4, 2, 3

      2003, 7, 4, 3

       

      The problem I'm having -- that I can't figure out how to do without creating additional "1, 0" calculated variables -- is how to generate the running total for the *values* of Type when I'm doing a subsummary of everything by *Year* -- I get "zero" values (as expected) for years where are not values for each type, so I can't do a line graph plot like I'd like.   The value for "B" for 2001 needs to remain "1" and not be a zero (for example...)

       

      Do I need to bite the bullet and have a series of calculated fields (ie, "Is This A", "Is this B") that basically say "if type=a, then 1" and then do running total displays of *those* fields -- and not the contents of the "type" field?

       

      The problem I have with that (which likely *would work*) is that if somebody creates a new type value (ie, "C"), then I'd have to create a "Is this C" variable and add that to the layout, etc...

       

      SO, I'm wondering if there's a way I can avoid that?   Any suggestions?

       

        • 1. Re: How to do "running total" on a field when another field is the subsummary field?
          philmodjunk

          Not really.

          You can use a table of records with unique values and a relationship to compute some of the totals but the limitations of FileMaker line charts are going to keep any solution from being as elegant and flexible as we would like to have. For one thing, add a "C" value can't be charted until up modify the settings of the Chart object and the requirement that each line in a line graph take data from a different field in the same record also keeps this from being as fully flexible as we might like.