6 Replies Latest reply on Sep 15, 2010 12:27 PM by Lorette

# Report Question: Minimum and Maximum Reporting Issue

### Title

Report Question: Minimum and Maximum Reporting Issue

### Post

Hi Everyone,

I am having a problem getting the  minimum and maximum per structure type. I have tried everything, please help.  Thanks.

The following did not work:

sumHours: Summary Field =Total of Hours

StructureTotal: Calculation Field = GetSummary (sumHours;StructureID)

StructureTypeMinimum: Calculation Field =Min (StructureTotal)

-----------------------------

Example:

Structure Type,    Structure ID, Hours

K, 1, 8

K, 1, 10

K, 2, 20

K, 2, 30

V, 3, 15

V, 3, 20

V, 4, 55

V, 5, 50

-----------------------------

Expected Result:

K     Minimum 18, Maximum 50

V     Minimum 35, Maximum 55

-----------------------------

----------------------------------------------------------------------------------------------------------------------------------

Part 2:

Thanks for your quick response.  I am very new to filemaker and appreciate any help you can provide. Unfortunately, I still need some additional assistance.

I need to determine the minimum,maximum & average total hours per structure for each category. Below I will explain the logic of what I need to do.....

Step 1:  Add up all the hours per structure ID

K - 1 = 18 hours

K - 2 = 50 hours

V - 3 = 35 hours

V - 4 = 55 hours

V - 5 = 50 hours

Step 2: For each structure type (e.g. K & V), determine the minimum, maximum and average...

K: Min = 18 (structure 1), Max = 50 (structure 2) , Average = 34 [(18 + 50) / 2]

V:Min = 35, Max = 55, Average = (35 + 55 + 50) / 3 = 46.67

Any suggestions? Thanks!

----------------------------------------------------------------------------------------------------------------------------------

• ###### 1. Re: Report Question: Minimum and Maximum Reporting Issue

Looks like getSummary is what you need here, but you need to specify the right type of summary field and the correct break field: Structure Type and sort your records by that field.

I don't understand your expected results.

The minimum hours for structure type K would be 8, the maximum (which is not the same as the total) would be 30

For type V, it would be 15 and 55 respectively.

• ###### 2. Re: Report Question: Minimum and Maximum Reporting Issue

Thanks for the response Phil. I don't think I explained the question properly the first time. I added additional information to the question and reposted it. Thanks.

• ###### 3. Re: Report Question: Minimum and Maximum Reporting Issue

Thanks for the update. I held off on specifics until I could see the actual results you wanted.

You have two different ways to use a summary field to get the sub totals you want.

You can construct a summary report with sub summary parts and you can then place total, maximum, average and minimum summary fields in these sub summary parts. You can include the individual records under or above each sub total entry, or you can delete the body part and just have the sub totals be visible.

You can also use calculations with the getSummary function to compute the sub totals.

To compute the total hours for a given structure ID:
Define a summary field sTotalHours as the Total of your hours field.
Define a calculation field, cTotalHoursStructureID, as GetSummary ( sTotalHours; StructureID )

This field will return the desired sub total when it is placed in the body of your layout or in the sub summary part, provided you sort your records by StructureID.

To get your type subtotals, define two more fields. sMinHours as the Minimum of cTotalHoursStrutureID. Then GetSummary ( sMinHours ; StructureType ) will return the minium subtotal for a given structure type.

These calculations depend on sorting the found set correctly. They should be sorted first by structureType, then structureID.

• ###### 4. Re: Report Question: Minimum and Maximum Reporting Issue

Thanks for the response, Phil. You were a great help. I have been working on this all morning. I now have the min, max and average working.  However, using the same method, I can not get the count of structures working.

It might be obvious to you what I'm doing wrong.  Any help is appreciated.

Thanks!

Expected Results

K  Count = 2

V Count = 3

• ###### 5. Re: Report Question: Minimum and Maximum Reporting Issue

That's called a unique value count.  See this thread:

How to count the number of unique occurences in field.

• ###### 6. Re: Report Question: Minimum and Maximum Reporting Issue

Wow it worked!  Thank you very much :)