13 Replies Latest reply on Aug 19, 2012 11:07 PM by Malcolm

# How to Calculate a percentage across two subsummaries?

I have a reporting problem that I can't seem to resolve. I'm printing a report that has two sub-summary parts and no body part.

We sort by Groups and by Staff Name and it looks like this:

Group Name

-- Staff Name: staff summary data

Group summary data

at the record level we have a number field called "workload"

At the staff level we sum "workload" and express it as a percentage

At the group level we want to express workload as the sum of the percentages divided by the number of staff in the group.

It seemed simple in concept but I can't make it work.

Any suggestions?

• ###### 1. Re: How to Calculate a percentage across two subsummaries?

Malcolm wrote:

the sum of the percentages divided by the number of staff in the group.

Isn't this the average of workload?

• ###### 2. Re: How to Calculate a percentage across two subsummaries?

the sum of the percentages divided by the number of staff in the group.

Isn't this the average of workload?

No. The average is calculated on the total number of records.

The sum of all the workload values is expressed as a percentage.

When the average is calculated in the group part the value is much lower than the average of  the sum of records for each staff member.

Malcolm

• ###### 3. Re: How to Calculate a percentage across two subsummaries?

I don't see what "expressing as a percentage" has to do with this - a numeric operation is a numeric operation, no matter how the values are expressed.

However, I do see where I misunderstood you. You want to divide the group's subtotal of workload by the number of staff, which is actually the number of subgroups in a group. While the numerator is easily obtained by =

GetSummary ( sTotalWorkload ; Group )

the denominator is more problematic - see:

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__view__findpost__p__289204

and:

http://fmforums.com/forum/topic/41243-count-unique-value-for-report/page__st__-20#entry192261

• ###### 4. Re: How to Calculate a percentage across two subsummaries?

I wish it was as easy as  ( summary Sum of workload / summary Count of staff )

1. sCountSerialID, Summary = Count of SerialID (or any field that's never empty);

2. cInverseCount, Calculation = 1 / GetSummary ( sCountSerialID ; EmployeeID )

3. sCountEmployees, Summary = Total of cInverseCount

I had tried using GetSummary earlier in the day but the results reflected the entire found set, not the group they were in. It seems like this may be a problem with the separated solution. The break fields in the report come from two different TOs in one file (staff_data) the numerical data comes from another file (work_data). There is no relationship in the data file that will allow me to express the break fields being used in the report.

I was able to get use GetSummary() when the break fields were in the same table as the summary fields but by that stage I had convinced myself that using summary fields in calculations wasn't generating the right results so I didn't go further.

thanks

Malcolm

• ###### 5. Re: How to Calculate a percentage across two subsummaries?

When using GetSummary(), the breakfield must come from the local table. Even if it's only a calculation field = related::field. Don't forget to adjust the sub-summary parts and the sort order accordingly.

• ###### 6. Re: How to Calculate a percentage across two subsummaries?

Yes... the base sort-order of the table instances is important.

I've done this before. I've been racking my brain to think "where?". If I remember I'll update this.

If the work table is permanently sorted by Group and there are summary fields for each of the components and there is a self-relationship by group and summary calculations by group, that should give you the basic tools to draw upon in the staff table.

- Lyndsay

• ###### 7. Re: How to Calculate a percentage across two subsummaries?

I wouldn't bring relationships into it, if at all possible. You may get a nasty suprise when the first related record is not in the found set.

• ###### 8. Re: How to Calculate a percentage across two subsummaries?

Why? How? You just have an empty value so 0/0 is still 0.

- Lyndsay

• ###### 9. Re: How to Calculate a percentage across two subsummaries?

Perhaps I have misunderstood your suggestion; I thought you meant something along the lines of:

https://fmdev.filemaker.com/message/68395#68395

If not, please elaborate. BTW, 0/0 is not 0.

• ###### 10. Re: How to Calculate a percentage across two subsummaries?

This is a real limitation. In this report the grouping is done by name and the local table contains IDs. We are going to have to give up the alphabetical name sorting. The calculation has priority.

• ###### 11. Re: How to Calculate a percentage across two subsummaries?

Malcolm wrote:

We are going to have to give up the alphabetical name sorting.

I don't see why - all you need is an unstored calc to pull the name into the local table. Of course, there is an assumption here that the names are unique - but that's true in your current arrangement, too.

• ###### 12. Re: How to Calculate a percentage across two subsummaries?

Or sort by Parent::Name and by ParentID. That would also take care of parents with duplicate names.

• ###### 13. Re: How to Calculate a percentage across two subsummaries?

That solution is going to be easier to implement and I like the extra level of safety. The database isn't "mine" so I have to take a conservative attitude toward adding new fields.