Calculate total for year

[Sam Laundon]

I have repeat customers and would like to track what they spend each year. I want to create a report that has totals for each customer for 2001, 2002 and 2003. The present fields are: order_count (the number of orders the client has placed), order_amount (how much each order is). I would like to create a calculation field that gives a total amount spent each year so the calculation field would be called "year_total".

Thanks in advance for your help - Sam

[CobaltSky]

Hi Sam,

There are any number of ways to do this in FileMaker, but perhaps one of the simplest would be to define two additional fields:

1. A calculation field of result type number with the formula: Year(order_date)

2. A summary field defined to provide the total of the 'order_amount' field.

Then create a new layout which has two parts, a leading sub-summary defined to appear when the database is sorted by name and a trailing sub-summary part defined to appear when the database is sorted by year (ie by the calc field defined at 1 above). Place the client name in the leading sub-summary part and the two new fields (year and total) in the trailing sub-summary part

When you sort your database by client and year then enter preview mode, you will be viewing a breakdown of client expenditure by year with the total for each year on a new line, grouped under the client's name.

If you wish, you coudl set up a script to sort, switch to the new layout, print and switch back to the original layout. That way you will be able to generate a printed report of client expenditure by year - on demand!