Large DataWindow expression function

Description

Finds a large value at a specified ranking in a column (for
example, third- largest, fifth-largest) and returns the value of
another column or expression based on the result.

Syntax

<span>Large</span> ( <span>returnexp</span>, <span>column</span>, <span>ntop</span> { FOR <span>range</span> { DISTINCT { <span>expres1 </span><br>    {, <span>expres2</span> {, ... } } } } } )

1	<span>Large</span> ( <span>returnexp</span>, <span>column</span>, <span>ntop</span> { FOR <span>range</span> { DISTINCT { <span>expres1 </span><br> {, <span>expres2</span> {, ... } } } } } )

Argument	Description
returnexp	The value you want returned when the large value is found. Returnexp includes a reference to a column, but not necessarily the column that is being evaluated for the largest value, so that a value is returned from the same row that contains the large value.
column	The column that contains the large value you are searching for. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.
ntop	The ranking of the large value in relation to the column’s largest value. For example, when ntop is 2, Large finds the second-largest value.
FOR range (optional)	The data that will be included when the largest value is found. For most presentation styles, values for range are: ALL – (Default) The largest of all values in column. GROUP n – The largest of values in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1. PAGE – The largest of the values in column on a page. For Crosstabs, specify CROSSTAB for range: CROSSTAB – (Crosstabs only) The largest of all values in column in the crosstab. For Graph and OLE objects, specify one of the following: GRAPH – (Graphs only) The largest of values in column in the range specified for the Rows option. OBJECT – (OLE objects only) The largest of values in column in the range specified for the Rows option.
DISTINCT (optional)	Causes Large to consider only the distinct values in column when determining the large value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.
expresn (optional)	One or more expressions that you need to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.

Return Values

The datatype of returnexp. Returns the ntop-largest
value if it succeeds and –1 if an error occurs.

Usage

If you specify range, Large returns
the value in returnexp when the value in column is
the ntop-largest value in range.
If you specify DISTINCT, Large returns returnexp when
the value in column is the ntop-largest
value of the distinct values in column, or
if you specify expresn, the ntop-largest
for each distinct value of expresn.

For graphs and OLE objects, you do not select the range when
you call the function. The range has already been determined by
the Rows setting on the Data property page (the Range property),
and the aggregation function uses that range. Settings for Rows
are as follows:

For the Graph or
OLE presentation style, Rows is always All
For Graph controls, Rows can be All, Page, or Group
For OLE controls, Rows can be All, Current Row,
Page, or Group. The available choices depend on the layer the control
occupies

Max might be faster

If you do not need a return value from another column and
you want to find the largest value (ntop = 1),
use Max; it is faster.

You cannot use this or other aggregate functions in validation
rules or filter expressions.

Using an aggregate function cancels the effect of setting
Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves
all rows.

Examples

These expressions return the names of the salespersons
with the three largest sales (sum_sales is the sum of the
sales for each salesperson) in group 2, which might be the salesregion
group. Note that sum_sales contains the values being compared,
but Large returns a value in the name column:

<span>Large</span>(name, sum_sales, 1 for group 2)<br><span>Large</span>(name, sum_sales, 2 for group 2)<br><span>Large</span>(name, sum_sales, 3 for group 2)

1	<span>Large</span>(name, sum_sales, 1 for group 2)<br><span>Large</span>(name, sum_sales, 2 for group 2)<br><span>Large</span>(name, sum_sales, 3 for group 2)

This example reports the salesperson with the third-largest
sales, considering only the first entry for each person:

<span>Large</span>(name, sum_sales, 3 for all DISTINCT sum_sales)

1	<span>Large</span>(name, sum_sales, 3 for all DISTINCT sum_sales)

Large – PB Docs 150