Limiting to the Top or Bottom Values

You can set the dimension values that are currently in status to the top or bottom performers based on a criterion represented as an expression. The simplified syntax for using the LIMIT command in this way is shown below:

LIMIT dimension TO [BOTTOM|TOP] n BASEDON expression

You can also set the dimension values that are currently in status to the top or bottom performers, by percentage, based on a criterion represented as an expression. The simplified syntax for using the LIMIT command in this way is shown below.

LIMIT dimension TO [BOTTOM|TOP] percent PERCENTOF expression

This construction sorts values based on their contribution, by percentage, to an expression and then places the identified values in status.

It can happen that the last item in status, based on a PERCENTOF criterion, is one of a number of dimension values having the same associated criterion value. In this case, LIMIT includes all dimension values with that criterion value in the resulting status, even when that causes the total of the criterion value to far exceed the specified percentage.

Note: Do not use a criterion expression that changes its own value.

Example 6-2 Limiting to the Top or Bottom Values Based on Criterion

Suppose the status list is sorted in descending order according to the values of sales, and only the top two performers are kept in status. Here the TOP and BASEDON keywords are used to limit the status of a dimension, using the values of a variable as a criterion.

LIMIT product TO 'SPORTSWEAR'
LIMIT month TO 'JUL96'
LIMIT district TO TOP 2 BASEDON sales

Suppose that you issue the following REPORT command.

REPORT DOWN district sales

The following report is produced, which shows the results of the LIMIT commands.

PRODUCT: SPORTSWEAR
               --SALES---
               --MONTH---
DISTRICT         JUL96
-------------- ----------
DALLAS         220,416.81
ATLANTA        211,666.14

Example 6-3 Limiting to the Top or Bottom Values Based on Percentage

Suppose you want to sort products in descending order by the contribution of each product to TOTAL(sales) and then add values to the status list, starting from the top, until the cumulative total of sales by product reaches or exceeds 30 percent of all sales. To limit the dimension in this way, you can use the following command.

LIMIT product TO TOP 30 PERCENTOF TOTAL(sales, product)

The following commands produce a report for January through March 2002 of products in the Boston district that reached or exceeded 30 percent of all sales.

LIMIT month TO 'JAN02' 'FEB02' 'MAR02'
LIMIT district TO 'BOSTON'
LIMIT product TO TOP 30 PERCENTOF TOTAL(sales, product)
REPORT sales

This output of the report is shown below.

DISTRICT: BOSTON
               -------------SALES--------------
               -------------MONTH--------------
PRODUCT          JAN02      FEB02      MAR02
-------------- ---------- ---------- ----------
FOOTWEAR        91,406.82  86,827.32 100,199.46
CANOES          66,013.92  76,083.84  91,748.16