Complex finds / number of occurances / 3D subtotal

[capisco]

Call me crazy, but is there no way to capture "the number of occurances" of many different finds all together in one layout? For example, a grid that shows the number of sales by country (rows), and by quarter (columns), the data being pulled and assembled from other db's. I've worked in FMP for years, but I fear I'm missing an entire concept (and am perhaps guilty of thinking in Excel), but it's really quite simple, and all the data is right there. I've tried tagging (1's vs. 0's), but brought the processor to its knees at over 300 calculation fields. Also tried subsummaries, but can't show the result of 50 finds at once, so no "number in current found set". Any thoughts would be most welcome, even steering me totally different directions.

[BobWeaver]

If you can define the occurrences by means of a relationship, then you can use the count() or sum() function in a calculated field to show the number of occurrences or total.

In your example you need a file (or table if using FM7) with one record per country, and 4 relationships based on country and quarter to link to the sales file data.

First, the key fields in the country file are defined as:

CountryQtr1 = Country & Year(Status(CurrentDate)) & 1

CountryQtr2 = Country & Year(Status(CurrentDate)) & 2

CountryQtr3 = Country & Year(Status(CurrentDate)) & 3

CountryQtr4 = Country & Year(Status(CurrentDate)) & 4

The key field in the sales file will be:

CountryQtr = Country & Year(SaleDate) & (1+ Int(Month(SaleDate)/3))

Now, set up a relationship from each of the 4 country file key fields to the key field in the sales file. We will call these relationships SalesQtr1, SalesQtr2, SalesQtr3, and SalesQtr4.

Then create 4 calculated fields in the country file:

SalesQtr1 = Count(SalesQtr1::CountryQtr)

SalesQtr2 = Count(SalesQtr2::CountryQtr)

SalesQtr3 = Count(SalesQtr3::CountryQtr)

SalesQtr4 = Count(SalesQtr4::CountryQtr)

These will give you the total NUMBER of sales by country for each quarter. I you want the total sales amount by country and quarter, the calculated fields are as follows:

TotalSalesQtr1 = Sum(SalesQtr1::SaleAmount)

TotalSalesQtr2 = Sum(SalesQtr2::SaleAmount)

TotalSalesQtr3 = Sum(SalesQtr3::SaleAmount)

TotalSalesQtr4 = Sum(SalesQtr4::SaleAmount)

Assuming the sale amount field is called SaleAmount.

[capisco]

Many thanks for such a detailed reply. Attempting to disect now and will post further when I figure out how to apply to my specifics. I'm impressed and humbled. My thanks again. /J.

[capisco]

My thanks again for the detail. I've assembled the relationships, and am intrigued by the concept. Couple of Q's:

CountryQtr = Country & Year(SaleDate) & (1+ Int(Month(SaleDate)/3)) This is a total mystery to me.

Also, I should have been less vague. My goal is a grid with 13 rows (we've been calling them countires), and four columns of two pairs of mutually exclusive occurances:

For each country, AA, AB, BA, or BB.

Now that I think of it, an additional one as well:

For each country, XAA, XAB, XBA, XBB, OAA, OAB, OBA, OBB.

Luckily I need not deal with dates, but I'm sure your example was more applicable to the other 41 people watching (this Forum is incredible!). My thanks, in advance, again. /J.

[BobWeaver]

First:

CountryQtr = Country & Year(SaleDate) & (1+ Int(Month(SaleDate)/3))

This is a calculation which concatenates country name, year of sale and quarter (calculated from the sale date) together. So, for an example sale record, if the country is Italy and the sale date is April 27, 2005 (2nd qtr) then the result would be:

Italy20052

In the "Italy" record of the country file, the value of the calculated key fields are:

CountryQtr1 = Italy20051

CountryQtr2 = Italy20052

CountryQtr3 = Italy20053

CountryQtr4 = Italy20054

Notice that the second key field CountryQtr2 value matches that of the example sale record, so the second quarter relationship would find that record, and it would be counted in the SalesQtr2 calculation. Similarly, sales in each quarter would match the appropriate relationship for the appropriate country and quarter.

Now, for your application, if you have three variables with only two states each, then you would create the key fields in the report file (country file) like this:

Key1 = Country&"XAA"

Key2 = Country&"XAB"

Key3 = Country&"XBA"

Key4 = Country&"XBB"

Key5 = Country&"OAA"

Key6 = Country&"OAB"

Key7 = Country&"OBA"

Key8 = Country&"OBB"

And in your data file (sales file in the earlier example) the key field would be:

Key = Country & Variable1 & Variable2 & Variable3

where Variable1, Variable2 and Variable3 are the 3 mutually exclusive parameters.

Now you need to create eight relationships instead of the four in the previous example. Everything else remains the same.