Using Summary Fields

I have a database that has several fields that need calculations. The
closest I've found on the web is here: http://interact.uoregon.edu/techweb/Filemakercrosstab.html.
It works fine until I need to take two of the summary fields and
divide one by the other giving me a percentage (example, I sold "1
apple", "4 oranges", and "5 bannanas". I need the summary to show in
addition to the quantity sold, the percentage as well.

I used most of the recommendations provided by the site I referenced,
I placed all of my summary fields on the "Trailing Grand Summary" part
of my layout, but my percentages are not accurate.

I used a counting field, "Items Sold Apples", and the calculation is
"=If(Items Sold = "apples", 1, 0)", and "Items Sold Oranges",
"=If(Items Sold = "oranges", 1, 0)"

and I created a summary field for each

"=Total of Items Sold Apples"
"=Total of Items Sold Oranges", etc.

This allows me to get the correct amount of fruit sold, but my
percentage of total just will not work.

Any suggestions? Thanks.

Re: Using Summary Fields

In article <1192226633.272740.165570@q5g2000prf.googlegroups.com>, EJay
Corvette wrote:

> I have a database that has several fields that need calculations. The
> closest I've found on the web is here:
> http://interact.uoregon.edu/techweb/Filemakercrosstab.html.
> It works fine until I need to take two of the summary fields and
> divide one by the other giving me a percentage (example, I sold "1
> apple", "4 oranges", and "5 bannanas". I need the summary to show in
> addition to the quantity sold, the percentage as well.
>
> I used most of the recommendations provided by the site I referenced,
> I placed all of my summary fields on the "Trailing Grand Summary" part
> of my layout, but my percentages are not accurate.
>
> I used a counting field, "Items Sold Apples", and the calculation is
> "=If(Items Sold = "apples", 1, 0)", and "Items Sold Oranges",
> "=If(Items Sold = "oranges", 1, 0)"
>
> and I created a summary field for each
>
> "=Total of Items Sold Apples"
> "=Total of Items Sold Oranges", etc.
>
> This allows me to get the correct amount of fruit sold, but my
> percentage of total just will not work.
>
> Any suggestions? Thanks.

Firstly, these types of reports are usually better done from a separate
Report table that uses Relationship(s) to obtain the various sub-totals
and normal SUmmary fields to obtain the grand totals. (In fact they're
often better done from Excel).

If you want to continue with the method shwon at that link, then the
page does say at the end how to add percentages using the Get Summary
function. In you case this would be something like:

PercentApples Calculation Field, Number Result
= Get Summary(TotalApples, Month)
/ Get Summary(TotalSales, Month)

where "Month" is the field you are sorting the records on - for example
if the report looks something like:

Apples Oranges Bananas Total
January 1 (10%) 4 (40%) 5 (50%) 10
Febraury 2 (25%) 3 (38%) 3 (38%) 8
March 2 (20%) 4 (40%) 4 (40%) 10

You must sort the records properly to get the correct results (ie. sort
by Month).


A hiccup comes when trying to create a Grand Total row at the bottom or
top of the report. To do this the "breakpoint" becomes ALL records in
the Found Set. The easiest waay to achieve this is to have a new Field
that is always the same data - a "constant" field.
eg.
Constant Number, Auto-enter data = "1"

You can then sort the records by Constant and Month, with the Grand
Total fields given by:

GrandPercentApples Calculation, Field, Number Result
= Get Summary(TotalApples, Constant)
/ Get Summary(TotalSales, Constant)



You would end up with a report layout something like:


[Month] [TotalApples] ([PercentApples]) ... other fruit

|Sub-summary by FruitType (Trailing)
--------------------------


Grand Totals [TotalApples] ([GrandPercentApples]) ... other fruit

|Sub-summary by Constant (Trailing)
--------------------------


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)