logo
Powered by QM on a Rpi server
Home
About OpenQM
Sales and Downloads
Help and Support
About
Login

KnowledgeBase 00099: Query Processor Averages

Last updated: 22 Jul 2016
Applies to: All versions, release 3.0-5 onwards
Search  
Top level index       Full Index Search Tips
Previous article     Next article

This article was originally published as a Tip of the Week.

Averages

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.


Alternative Averages

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.


Related Articles

See also the CALC keyword in the QM Reference Manual.



Please tell us if this article was helpful
Very     Slightly     Not at all
Comments
Email (optional)