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
|
1 2 |
Large ( returnexp, column, ntop { FOR range { DISTINCT { expres1 {, expres2 {, ... } } } } } ) |
|
Argument |
Description |
|---|---|
|
returnexp |
The value you want returned when the large value is |
|
column |
The column that contains the large value you are |
|
ntop |
The ranking of the large value in relation to the |
|
FOR range (optional) |
The data that will be included when the largest value
For Crosstabs, specify CROSSTAB for
For Graph and OLE objects, specify one of
|
|
DISTINCT (optional) |
Causes Large to consider only the distinct values in |
|
expresn (optional) |
One or more expressions that you need to evaluate to |
Return value
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:
|
1 2 3 |
Large(name, sum_sales, 1 for group 2) Large(name, sum_sales, 2 for group 2) Large(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:
|
1 |
Large(name, sum_sales, 3 for all DISTINCT sum_sales) |
See also