Analyzing a Set of similar records.

[OffTheDeepEnd.com]

Here is the fun question of the day...We have a multiple database filemaker sales solution and what I need to do is create a report listing the quantity of sales of one item per month. We have a line items file which contains a record for each time the item was sold. I was thinking that I needed to setup another database and have four fields in it, one the item number (in which I would create a relationship to the line items file using this field) a date from field, a date to field, and also the amount sold field. In the amount sold field, there would be a calculation that would use the date from and the date to field range and use that as the range on the line items database to determine how many of that item was sold on that range... Any help on developing that calculation? [color:"blue"]

[Ugo DI LUCA]

Hi Austin,

Why would you need a separate file for this.

You surely have a Line Item.

That's where you can count how many products you sold with either :

- Sum(::Line Item:Quantity Sold) -- which leads to a total quantity sold

- Count(::Line Item:Product_Id) ---which leads to a count of occurances

About the monthly report, you would need a calculation for the right side (in the line item) of type Product_Id & "-"& Month(Invoice Date)&"-"&Year(Invoice Dtae)

You now understand that you need a similar calculation in your Product File.

These two keys would be used for a new relationship

There are though different solutions I could think of.

The first one would be a global populated with a value list (even custom) of 12 numbers and a calculaton Product_Id & "-"&global&"-"&Year(Status(CurrentDate))

Then, you'll have a dynamic display of your datas, using the value list.

Another solution would be to use 12 globals and 12 calculations and 12 relationships, having therefore a "statistical" display of your sales, even implementing cross calculations (not recommanded though).

Then, the new calcs would look like

- Sum(::New Relationship(s)ToLine Item:Quantity Sold) -- which leads to a total quantity sold

- Count(::New Relationship(s)ToLine Item:Product_Id) ---which leads to a count of occurances

Hope I understood your querry.