AnsweredAssumed Answered

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

Question asked by maser on Jul 31, 2012
Latest reply on Jul 31, 2012 by philmodjunk


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


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?