12 Replies Latest reply on Jan 11, 2014 10:11 AM by philmodjunk

# Report / Calculation Help

### Title

Report / Calculation Help

### Post

I need help creating a calculation or report. I've tried a few different things but nothing has worked.

Example:

Station 1 | 2003 - 2013

Month                     Average of Field #1                     Average of Field #2
1                     1.94                     5.02
2                     3.33                     5.63
3                     4.26                     2.01
4                     6.52                     4.18
5                     7.73                     0.564
6                     8.99                     0.0458
7                     8.26                     0.0391
8                     6.26                     0.28
9                     3.82                     2.228
10                     1.89                     3.533
11                     1.15                     8.14
12                     1.59                     4.17

I have data entered for each month for the last ten years in Field #1 and Field #2. The above table shows the Average of the past ten years for Field #1 and Field #2 (these Averages are Summary fields).

Problem:

I want to create a few calculations off of this data.

Example Calculation:

"Spring" = Sum ( Average of Data #1 ( Months 3-5 ) - .25 * Average of Data #2 ( Months 3-5 ) ) / Rate * 60

Then I want to create the same thing for Summer, Fall, Winter:

"Summer" = Sum ( Average of Data #1 ( Months 6-8 ) - .25 * Average of Data #2 ( Months 6-8 ) ) / Rate * 60

and so on.......

### I'm stumped on how to do this. Any suggestions?

• ###### 1. Re: Report / Calculation Help

What exact method are you using to get the monthly averages?

• ###### 2. Re: Report / Calculation Help

@PhilModJunk

Just updated my post. These Averages are Summary fields, not Calculations as I originally posted.

• ###### 3. Re: Report / Calculation Help

Does this mean that you are sorting records by month and placing the summary fields in sub summary layout parts?

• ###### 4. Re: Report / Calculation Help

This is the layout I am currently trying to work with. I'm not 100% sure if I'm going about it right now.

Sub-summary by Station (Leading) - Station #1

Sub-Summary by Months (Trailing) - Month 1, Month 2, and so on thru Month 12.... (These calculations are the Average Summary Fields)

I'm not too sure what the next step should be.

• ###### 5. Re: Report / Calculation Help

I think that you mean that each row in the table that you uploaded represents  on instance of a sub summary layout part when sorted by the Month field/column shown in the same table.

If so, you can define a cSeason field as:

Case ( Month = 12 or Month < 3 ; 1  // Winter
Month < 6 ; 2 // Spring
Month < 9 ; 3 // Summer
Month < 12 ; // Fall
)

If your sort your records by cSeason, you can set up a sub summary layout part that uses your existing summary fields to get averages for Field 1 and 2 but for each season instead of each month.

It would also be possible to use cSeason in combination with a year and a station field in a self join relationship to access the same groups of record in order to get these same seasonal averages. This is sometimes used in layouts where the sub summary layout part can't be used.

• ###### 6. Re: Report / Calculation Help

I have tried this method but I'm just not understanding it. I think I'm missing something. (I've never used a Case calculation so I'm sure it's me.)

----------

I first tried to copy and paste your calculation above but that doesn't work in a calculation; I get the "An operator is expected here" error (see photo below). So then I modified the calculation a bit and came up with this which actually correctly notes each season:

Case ( Month = 12 or Month < 3 ; "Winter" ; Month < 6 ; "Spring" ; Month < 9 ; "Summer" ; "Fall" )

But now I'm not sure how to actually use this new field to help me get the averages for each season.

• ###### 7. Re: Report / Calculation Help

Sorry, I left out some stuff:

Case ( Month = 12 or Month < 3 ; 1 ; // Winter
Month < 6 ; 2 ; // Spring
Month < 9 ; 3 ; // Summer
Month < 12 ; 4 // Fall
)

• ###### 8. Re: Report / Calculation Help

Okay, so I actually got the Monthly Averages using a Case Calculation Field and Summarizing a report. I even tested it out by hand.

Now part 2 of my question - how do I create the following calculations below?

Example Calculation:

"Spring" = Sum ( Average of Data #1 ( Months 3-5 ) - .25 * Average of Data #2 ( Months 3-5 ) ) / Rate * 60

Then I want to create the same thing for Summer, Fall, Winter:

"Summer" = Sum ( Average of Data #1 ( Months 6-8 ) - .25 * Average of Data #2 ( Months 6-8 ) ) / Rate * 60

and so on.......
• ###### 9. Re: Report / Calculation Help

It depends on how you want to present those totals in your report.

You can use a sub summary layout part with your current summary fields and use this new field as the "when sorted by" field. This then becomes just a minor variation of the method you are already using to get a monthly total.

You can use this field as a match field in a relationship and then Sum ( RelatedTable::Field ) will return a total for all records of that season. (Additional match fields can be included to narrow this down to a specific year etc.)

In FileMaker 12 or newer, ExecuteSQL can use this value to get the needed totals.

• ###### 10. Re: Report / Calculation Help

I have been trying for hours but I'm just not understanding how to do it.

Is it possible to give an example new field I would create? Or type out exactly what I'm supposed to do?

• ###### 11. Re: Report / Calculation Help

Spoke too soon. This is what I randomly figured out using the GetSummary function.

Original Calculation I wanted:

Sum ( Average of Data #1 ( Months 6-8 ) - .25 * Average of Data #2 ( Months 6-8 ) ) / Rate * 60

New Field in Filemaker:

GetSummary ( Data1 Ave ; cSeason ) - ( .25 * GetSummary ( Data2 Ave ; cSeason ) ) / ( .5 * 60 )

It actually worked. Is this the best solution??

• ###### 12. Re: Report / Calculation Help

There is no best solution that I can recommend from the information that you have provided. GetSummary is an excellent choice if your summary totals are based on a found set of records pulled up to produce your report. But if you refer back a few posts, I listed a number of different methods, some of which do not require a found set based set of data from which to compute totals and sub totals that can be used in layout setups where the found set based method isn't practical.

That, BTW, is why I didn't spell the methods out in detail. each of those methods has a fairly complex "step by step" of their own. I was waiting to learn more about your database before selecting one to describe in detail. (And then personal business pulled me away from the forum for a day and I am only just now getting caught back up with some of these threads.)