8 Replies Latest reply on Dec 28, 2010 11:07 AM by philmodjunk

    Average during one month

    AndersNordkvist

      Title

      Average during one month

      Post

      hello

      I have a date field (YYYY-MM-DD) and quantity field (Number). I would like to get an average field which will be the average of the quantity field of the month. When a new month starts the average should then be for this month. also when it is a change of year the average should be for example jan 2011. Could anyone help me with this calcutation ??

        • 1. Re: Average during one month
          Rajkumar

          hi!

          try this,

          new window();

          perform quick find [month(date)=1]; //say, check for jan......in new window, now it'll show only records of 1st month

          go to record/request(first);

          set variable($totalRecord;value:get(totalRecordCount))

          set variable(sum=0);

          loop

             set variable(sum=sum+quantity field);

             goto record/request(next;exit after last);

          end loop;

          average= sum/$totalRecord;

          check it, hope it'll work................

          • 2. Re: Average during one month
            AndersNordkvist

            Hello,

            it did not work, I tried to put into a calculation but could not get into the calculation. Also tried to do a script but wothout success.

            • 3. Re: Average during one month
              Rajkumar

              Hey,

              Can u tell ur problem in detail??

              • 4. Re: Average during one month
                AndersNordkvist

                hello,

                I assume that the solution you gave me was for a script ?

                When I was doiing the script I needed a new field called sum to be able to set variable. When I came to the end of the script after End loop I could not find the choice of average in script. sp I could not enter average= sum/$totalRecord;

                I have during the day tried different solutions but either it calculates Januari in year 2010 and 2011 as an average. What I want is to calculate the average in januari 2011 only. When 2011 februari a new average should be calculated.

                Would it be possible to make the calculation in the manage database ? then the solution you gave me I cannot understand how to get into the calculation field.

                • 5. Re: Average during one month
                  philmodjunk

                  Item 1, make sure that your date field is of type date. You can specify the YYYY-MM-DD data format in the inspector, but keep the field type date as it will be much easier to work with in many ways.

                  Define a calculation field, cMonth, as DateField - Day ( DateField ) + 1. This expression, posted some time ago by Comment, computes the date of the first day of the month for the month specified in DateField. Thus, all records with a January 2011 date will have a date of Jan 1, 2011 in this field, for example.

                  Now you can use this field in one of two ways to compute a monthly average:

                  Define a summary field, sAverage, as the average of your quantity field. If you sort your records by cMonth you can place this summary field in a sub summary layout part (when sorted by cMonth) to show the monthly average. Also, when sorted by cMonth, you can use GetSummary ( sAverage ; cMonth ) to compute the monthly average as part of a calculation field or in a script.

                  Define a relationship:

                  YourTable::cMonth = YourTable 2:cMonth    (YourTable 2 is an additional table occurrence of YourTable)

                  Now this aggregate function: Average ( YourTable 2::QuantityField ) can also compute the monthly average.

                  • 6. Re: Average during one month
                    AndersNordkvist

                    Thanks a lot you made my day Laughing

                    • 7. Re: Average during one month
                      raybaudi

                      @Phil

                      as I said in another forum ( FMForums ) in response to the same question, I would prefer the last example due to the too much dependence of the result from the found set when using the first example.

                      • 8. Re: Average during one month
                        philmodjunk

                        A very good point, but depends on how it is implemented and what you need.

                        That dependence on a found set may be exactly the effect needed. Example: You want a break down by month with a a monthly average for records that match other criteria.