Analyze and store summary data

[AnnF]

I have a table that contains about 2500 records, with approx 500 new records imported per month. The data is gathered via script (by date range and client) and put into a report format that provides grand summarys on the monthly data. Ie: Total Users/Sales/ etc for the month.

I need to store the summary totals so that I can analyze changes in monthly usage per client.

I know I can build a calulation to "sum" the information, but that does not allow the individual montly totals I need to see. I'd also like the ability to compare client to client. I don't want to pull a report everytime I need to look at the information. Is there a way to store this data into a table without manually entering the totals when I pull reports each month?

[Søren Dyhr]

Is there a way to store this data into a table

To store and to get data into an accessable format is two quite distinctive measures in filemaker, but investigate this:

http://www.filemaker.com/help/FunctionsRef-341.html

It matters that such storage has to be kept up to date, and it's a violation of relational database theory, where syncronization issues deliberately are attempted as few as posible.

Therefore do I instinctively feel, your approach to the matter is wrong, and feel an urge to lead you in direction of multi criteria relations although it largely depends on the measure of data involved.

--sd

[AnnF]

Here's better clarification via a simplified example. In January 2006, My report shows each day of the month-with daily totals and then a subsummary for the month showing a total of: 25 users, 12 of them made a purchase, Total sales of $500.00, a refund for $20.00. And I have similar results every month for 10 clients. Once the data is reported, it is "past sales history" and I do not want the data to become modified or changed. The data that is added each month (the 500 records)...affects future reports, not the past.

The goal: Have a list that shows January 2006... with all the totals for that month, Feb 2006... with all the totals for the month and so forth, listed in one portal, so I can see a line for each month - all at once on the layout.

The hard way I can think of is to create a table with all the summary information I want to hold, and manually enter the data (after I generate my monthly report). Create a relationship to the the client record, and display the related record in a portal on the client layout.

How do I accomplish the results I need with less manual processing? I'm having trouble working my way out of this one.

[Søren Dyhr]

Alright I've made you a template....

But it largely depend on how much data you have, with huge aggregations of data will there be no excuse - genuine summaries are manditory!

--sd

LiveSummaries.zip

Edited May 22, 2006 by Guest