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:
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?