Skip to Content
0
Former Member
Dec 08, 2009 at 04:28 PM

How do I group/subtotal the sum of all changes in a numeric field?

552 Views

Ok, well, I didn't see an answer to this after searching for 30 minutes, so here goes:

I have the following table:

Unit ID|Department|Repair Date|Repair Odometer

101|01|2009-01-01|100,000

101|01|2009-03-01|104,000

101|01|2009-05-29|108,400

102|01|2009-01-13|92,000

102|01|2009-03-14|96,500

102|01|2009-06-01|101,000

103|02|2009-01-01|45,000

103|02|2009-06-01|59,000

I need to be able to summarize how many miles each vehicle traveled, then summarize those subtotals for each department.

So far, I have been able to create a formula that gets the mileage at the start date and the mileage at the end date and subtracts the two. This helps avoid any data entry mistakes made in between the two dates (which is quite common with our user base, so I'm trying to make this foolproof).

numbervar MinMiles;

numbervar MaxMiles;

select {Repair Date}

case Minimum ({Repair Date}, {Unit ID}) : MinMiles := {Repair Odometer}

case Maximum ({Repair Date}, {Unit ID}) : MaxMiles := {Repair Odometer}

numbervar Miles := MaxMiles - MinMiles;

This works great when I put it in the group footer for the Unit, but I can't get a subtotal in the next level up, the group for the department. For some reason I can't get a running total on a formula that references the Miles variable.

Is there anything that will total the change in a field, and then is there any way to subtotal the sum of all the changes? I've spent the last day and a half trying 20 different ways to get this to work. So far they all have the same results, and this is the simplest formula I'd been using. Everything else was having to use a lot of if Unit ID <> Next (Unit ID) logic.