### Title

Change over prior week calculation field?

### Post

I've built a working database in FMP 11, and designed a summary report that calculates the original and final numbers for each given Shot Rating (in this case A-F). It also calculates the variance between the original and the final budget. I'd like to produce this report on a weekly basis, but it would be really helpful if I could also have it calculate the Change of Prior Week in two separate fields (columns).

For example, if on week ending 09/11/10 the report said that for 'A' Rated Shots there were 4 Final Shots and the budget was $812,056 (see attached jpg below), and then on week ending 09/18/10 the report said there were 6 'A' rated shots and the final budget was $912,056, I'd like like to add two separate fields: one that calculated the Change Over Prior Week in terms of Final Shot number, and one that calculated the Change Over Prior Week in terms of Final Budget number... and in this example the first field would read 2, and the second field would read $100,000.

Since FMP is always current, I don't know how to take a snap shot of a report and make a comparison at a later date. I come from an Excel background, and back then I would simply "save a copy as" for each week ending, and then I set up a formula that subtracted one week ending report from another.

Thanks for your time and attention in this matter, and I look forward to hearing your input.

A lot depends on how you've structured your tables and related them. A summary report can certainly be constructed that groups records by common value (such as the week and/or shot rating) so that subtotals can be computed and displayed. A relationship can be used to access an aggregrate value of selected records from the previous week as well.

But all this depends on the structure of your tables.