7 Replies Latest reply on Oct 24, 2014 2:57 PM by cowens1

# Sub-Summary Percentage

Hello All,

I have a report showing a list of sales calls, sorted by salespeople.

There is a summary set for each person that tallies the number of calls they ran, the number of sold jobs and the total Sales in Dollars.

In the Header I have a calculation that divides the Summary TotalCallsSold by the Summary TotalCallsRan, resulting in an overall closing ratio.

QUESTION:

How can I get a ratio per salesperson to show up on the Sub-Summary line?

• ###### 1. Re: Sub-Summary Percentage

If your summary/calc fields are based on the same table as your layout is, then you should just be able to copy those fields into the subsummary layout part, and they will summarize by each subsummary instance. Filemaker treats every subsummary group as an independent set of records (with some exceptions of course).

You might also consider a trailing subsummary for these fields.

• ###### 2. Re: Sub-Summary Percentage

The problem I'm facing may be a little more complicated than that.

While all fields are based on the same table, I can't calculate a ratio of a found set or subset using only one field.

I use a summary field to count all records in the found set. It also counts all the records in each Sub-set.

Another Summary field counts the records marked "Sold" in the found set and each sub-set.

When I put a calc field (that divides the sold summary by the total summary) on the header and each Sub-Summary line, it returns the results of that calculation for the Found Set, and returns the exact same numbers on each sub-summary line. It does not recalculate in consideration of the various sub-summary numbers.

Any suggestions?

Collins

• ###### 3. Re: Sub-Summary Percentage

Collins,

Apologies if I don't understand your predicament precisely enought to provide an exact calc, but I think you need to use the GetSummary function. Something along these lines:

PersonRatio =
GetSummary ( TotalCallsSold ; Person ) / TotalCallsSold

Does that help?

Debi Rubel
FullCity Consulting

• ###### 4. Re: Sub-Summary Percentage

Thank you for pointing me in the right direction, Debi!

My actual calculation was:

GetSummary ( Summary_CountSold ; _kf_TechNumber )  /  GetSummary ( Summary_CountCalls ; _kf_TechNumber )

This resulted in a closing ratio for each person in the found set, which was sorted by salesperson so the result was in the SubSummary Header.

This forum is awesome!

I didn't know / wasn't aware of how to use the "GetSummary" function.

Thanks again,

Collins

• ###### 5. Re: Sub-Summary Percentage

Glad that helped - thanks for letting us know!

~debi

• ###### 6. Re: Sub-Summary Percentage

Excellent, this is just what I was looking for.  Thanks!