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(sum=sum+quantity field);
goto record/request(next;exit after last);
check it, hope it'll work................
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.
Can u tell ur problem in detail??
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.
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.
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.
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.