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

    Report / Calculation Help

    mikethecreative

      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
          philmodjunk

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

          • 2. Re: Report / Calculation Help
            mikethecreative

                 @PhilModJunk

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

            • 3. Re: Report / Calculation Help
              philmodjunk

                   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
                mikethecreative

                     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
                  philmodjunk

                       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
                    mikethecreative

                         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
                      philmodjunk

                           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
                        mikethecreative

                             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
                          philmodjunk

                               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
                            mikethecreative

                                 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
                              mikethecreative

                                   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
                                philmodjunk

                                     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.)