7 Replies Latest reply on Dec 5, 2013 12:47 PM by philmodjunk

    Calculations using summary fields

    mjroberson

      Title

      Calculations using summary fields

      Post

           I have a 15 year record of monthly water use data from four weather stations. Using a Summary Field I have an average value, by station.  

            

           I have a table with constants that are plant (tree or grass) dependent.

            

           Here is my question - How do I set things up so that I can multiply each month’s average value by one of the constants so that the final output is a table, by month and station of water use by a particular plant.

            

           thanks

        • 1. Re: Calculations using summary fields
          philmodjunk

               What value is returned by a summary field depends on the context in which it is used. If you are using a sub summary layout part to display an average for each station, take a look at the getSummary function, it can access the same total shown in the sub summary part in order to use it in a calculation. The "break" field parameter should be the same as the "when sorted by" field in your sub summary part.

          • 2. Re: Calculations using summary fields
            mjroberson

                 Perfect - thanks

            • 3. Re: Calculations using summary fields
              mjroberson

                   Now I have a Group Field (Jan, Feb etc) and monthly average.  Now I want to use these value in a calculation but in the get summary function there isn't a choice for which mon.

              • 4. Re: Calculations using summary fields
                philmodjunk

                     Correct. GetSummary is not designed to work that way. My answer assumed that you had a report like this:

                     Jan 2013
                         Station 1: average value
                         Station 2: average value
                         Station 3: average Value
                         Station 4: average Value

                     Feb 2013

                          Station 1: average value
                     and so forth...

                     The Row shown as Jan 2013 would be a sub summary layout part and a summary field placed in it would produce a value based on all 4 stations over Jan, 2013. The Station 1, 2 ... rows could also be sub summary layout parts where putting the same field in it produces a summary value based on the monthly data for just that specific station.  Get summary allows you to access the same summary field values as those shown in the sub summary layout parts such that you can use them in a calculation. You can then place such a calculation field in the sub summary layout part that uses the same break field to show a result computed from the same summary field produced sub total.

                     So it is very possible that you have a different report format in mind that needs a different approach.

                • 5. Re: Calculations using summary fields
                  mjroberson

                       I've used the Getsummary to calculate monthly totals by station and can display these values in a layout as sub-summary.  I can also use the AvgET in a calculation (i.e. PlantET).

                       What I want to do is get a sum of the Avg_ET for a single station for only a few months and then use this in a calculate to get PlantET.  For example, wine grapes for station 13 for April to Jul.  This value would then be in another layout that would go to a customer. 

                        

                       thanks - with your help I'm getting there

                  • 6. Re: Calculations using summary fields
                    philmodjunk

                         And how will the user specify "April to July"? Perhaps by entering/selecting data in a pair of fields?

                         In any case, you can set up self join relationships and/or SQL queries in ExecuteSQL that can compute an aggregate value such as a sum or average from a set of related records. A relationship can match ranges of date in two different ways:

                         Table1::DateStart < Table2::Date AND
                         Table1::DateEnd > Table2::Date

                         And you can narrow the group of records down by including additional match fields such as matching by a station ID.

                         The WHERE and JOIN clauses in SQL can also produce the same set of values to be averaged or summed.

                    • 7. Re: Calculations using summary fields
                      philmodjunk

                           Hmm I said "two different ways" and then only described one. Here's the other way such a "date range relationship" is often set up:

                           Table1::DateList = Table2::Date

                           Where DateList is a return separated list of all dates from date start to date end. Custom functions or looping scripts are typically used to put such a list of dates into DateList.