|Powered by QM on a Rpi server|
KnowledgeBase 00099: Query Processor Averages
This article was originally published as a Tip of the Week.
The query processor AVERAGE field qualifier is widely supported by multivalue environments but just what does it do?
The default behaviour of the AVERAGE keyword is to accumulate a total of the value of the field to which it is attached and then report this value divided by the number of records on the "total" line. If a record has this field empty or non-numeric, it is treated as zero.
Most spreadsheet tools do not behave this way. If a cell in a column to be averaged in empty or non-numeric, the normal action is to ignore the cell both in terms of accumulating the total and also when determining the number of data items for the division. In QM and many other multivalue products this behaviour can be enabled by use of the NO.NULLS qualifier.
The AVERAGE keyword described above calculates what is usually referred to as the mean value. There are other ways in which mathematics defines an average to be calculated.
The median value is defined as the middle value in a sorted list of all values, including repeated values, in the data being averaged. Where there are an even number of values, the median value is the average of the two values in the centre of the list.
The mode value is the most frequently occuring value in the data being averaged.In QM, these alternative averaging methods are provided by the MEDIAN and MODE field qualifiers. Whilst not strictly an average, the related RANGE field qualifer shows the difference between the minimum and maximum field value. All three of these field qualifiers support use of NO.NULLS to ignore empty and non-numeric data.
See also the CALC keyword in the QM Reference Manual.