cumulative totals

[brucepensyl]

Wish to construct a layout that will use other calculated fields to build another calculation like on a spreadsheet

Example:

Date, leave earned, leave taken, total leave

01/01, 12 hours, 3hours, 9 hours

01/03 0 hours, 2 hours, 7 hours

02/03 12 hours, 8 hours, 11 hours

Total leave is a running calculation based of previous record calculation

The example above would be a report layout. Data entry would only be for date, earned, taken

Thanks

Version: v6.x

Platform: Mac OS X Panther

[CyborgSam]

If I'm understanding this right, the real issue is how to come up with a sub-total for a list of records.

Define a summary field, click "Total of" and check "Running total". Make sure the records are sorted the way you want, since the subtotal goes in order. For details, look in the FM6 manual on pages 5-6 and 8-12.

Does this work for you?

[brucepensyl]

I guess I did not supply the information correctly. I tried your way and have not had the results that I would like. The final report should look like:

Annual Leave:

Earned Taken Cumulative

Start 400

01/04 10.2 410.20

01/30 24 386.20

Sick Leave:

**same idea as annual leave above

Cululative is based on the current record as well as the field cumulative from the record preceding.

(i.e. (Leave Earned+cumulative)-Leave taken)

I see that when this post is posted the columns I had set up do not work. Is there a way to demonstrate this so it will be clearer

Thanks again

[brucepensyl]

Possibly I was not as clear as I thought. I have read the bible and found that I am getting closer. But I am still having some trouble.

The attatched file may show what I am looking for.

The cumulative total or running total is working well, but I want to break the total when a new type of leave starts. This is real easy in a spreadsheet, but kicking my a** in FMP.

Thanks for any help

Version: v6.x

Platform: Mac OS X Panther

leave example.rtf.zip

[michellem]

Sounds like you'll have to add new calculated fields (like annual_leave, sick_leave), which gets populated by if statements - like if (leavetype = "annual",leavehours,0) or some such. Then do running totals of those calculated fields.

[CyborgSam]

Here's a quick file I threw together that serves as an example using Summary fields.

Basically, each record has a date, an amount, and a category. I just used two categories: Sick and Annual. I lumped earned & taken together, you can separate them out.

I'm not sure what you mean by "break the total when a new type of leave starts". if my example won't allow this, please explain.

Version: v7.x

Platform: Mac OS X Panther

Leave.zip