5 Replies Latest reply on Dec 23, 2010 4:45 AM by stcav

# Average Calculations - same field gives different result on chart and layout

### Title

Average Calculations - same field gives different result on chart and layout

### Post

I have come across an odd problem: a summary Average field gives a different result when used in a chart than when the same field is used on the body part of a layout.

I have an application that records fuel used in a vehicle. For each fill up, a record is made in a fuel table recording (among other things) volume of fuel, mileage, and a calculation field working out the mpg since the last fill up. (The calculation uses 'GetNthRecord -1' so depends on a sort beforehand.)

I wanted to chart the mpg over time together with an average, for which I created a summary Average field. Ideally, I wanted a moving average but I'm struggling with that using GetNthRecord because it always gives the same result. I started using the Data Viewer to resolve this and noticed that on the chart the summary field contains "22.21", but when the focus is switched to an ordinary layout it shows "19.44". When I exported the data to a spreadsheet the correct answer is shown as "22.21", the same as the chart.

When the summary Average field is shown on the layout body the result "19.44" is shown (and is constant for all records), whereas when it is placed on a sub-summary part it shows yet a third figure, which is identical to the simple mpg calculated field for that record.

I assume I'm misusing the summary field in some way, but I can't see where or how. Any clues, please?

FM Pro Advanced 11.0v2; Mac OSX Leopard; MacBook Pro.

• ###### 1. Re: Average Calculations - same field gives different result on chart and layout

An "average" summary field will compute the average of all the records in the found set when you put this field in any layout part but a sub summary part. It will compute the average of a sub group of records when you put it in a sub Summary part "sorted by" a field that groups the records and you then sort the current found set by this same field. Different finds and sorts will group your records differently so this can affect the average you see in a sub summary -- assuming the sub summary is even visible. Summary fields placed in other parts and referred to in charts, calculation fields and scripts will compute an average of all the records in your found set.

That reliance on current layout design, found set and sort order may give you a clue.

You may need to define a calculation field that uses GetSummary to compute the sub-group average for charting purposes.

• ###### 2. Re: Average Calculations - same field gives different result on chart and layout

Thanks Phil, that does give me a clue - a nice simple explanation.

Could you elaborate on the GetSummary function, please? I'm after a 3-point moving average on the chart, which depends on looking back over the fill up events rather than depending on a break field. It's dead easy on spreadsheet - something like: average the mpg figures from the last three rows. I think I recall reading on these forums that GetNthRecord won't work because it returns only the first value, not all three.

Thanks,

Stephen

• ###### 3. Re: Average Calculations - same field gives different result on chart and layout

I think I recall reading on these forums that GetNthRecord won't work because it returns only the first value, not all three.

True, but you can combine several calls to GetNthRecord to combine values from the current record with the previous two.

( GetNthRecord ( Gallons ; Get ( RecordNumber ) - 2 ) + GetNthRecord ( Gallons ; Get ( RecordNumber ) - 1 ) + Gallons ) / 3

GetSummary won't cut it unless you can sort your records into groups--which isn't what you are after here with your moving average.

• ###### 4. Re: Average Calculations - same field gives different result on chart and layout

And you'll likely need some special handling code in that calculation for the first and second records.

• ###### 5. Re: Average Calculations - same field gives different result on chart and layout

Thanks again Phil. Great service.

Stephen