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

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?

 

Outcomes