Limiting an Application's View of the Data, 4 of 11

Limiting Using a Boolean Expression

Overview: Limiting using a Boolean expression

You can use the LIMIT command to limit a dimension according to the result of a Boolean expression. The simplified syntax for using the LIMIT command in this way is shown below:

LIMIT dimension TO Boolean-expression

When you use this form of the LIMIT command, the values that are currently in status are replaced with those dimension values for which the Boolean expression is TRUE.

Example: Limiting using a Boolean expression

In this example, the values of the TOTAL function are broken out by PRODUCT and compared to a literal (that is, the number 12000000). The LIMIT command replaces the values that are currently in status for the PRODUCT dimension with the values of the PRODUCT dimension whose sales, totaled for all months and districts, are greater than 12 million.

limit product to total(sales product) gt 12000000

How to construct a Boolean expression

When you are constructing a Boolean expression, keep the following points in mind:

The Boolean expression must be dimensioned by the dimension whose status is being set.
The data types of the expressions you are comparing in the Boolean expression must be similar.

For example, the following Boolean expression has similar data types on both sides of the Boolean operator GT.
```
limit market to units.m gt 50000
```

How LIMIT handles Boolean expressions with more than one dimension

An understanding of how the LIMIT command handles Boolean expressions with more than one dimension is important to the successful use of the command.

The result of a simple Boolean expression is a single value. When you use the LIMIT command with a Boolean expression, no looping is performed through the dimensions to create and return an array of values for the expression. Instead, the first value in the dimension's status list is identified for each dimension in the expression, the expression using those values is evaluated, and a single value is returned.

If you want the result of the Boolean expression to have dimensionality, then use the EVERY, ANY, or NONE functions, which let you specify the dimensions of the result of the Boolean expression.

Example: How LIMIT handles Boolean expressions with many dimensions

Suppose that the MONTH, DISTRICT, and PRODUCT dimensions of the demo analytic workspace have the dimension status shown below.

The current status of MONTH is:
JAN95 TO MAR95
The current status of DISTRICT is:
BOSTON
The current status of PRODUCT is:
ALL

Now you want products that have more than $90,000 worth of sales in at least one of the months to be in status for the PRODUCT dimension. By issuing the following command, you can see which values in the current dimension status meet this condition.

report sales gt 90000

As shown below, the report displays YES in both the FOOTWEAR and CANOES rows. Both of these products have sold more than $90,000 on at least one occasion during January through March 1995.

DISTRICT: BOSTON
               ---------SALES GT 90000---------
               -------------MONTH--------------
PRODUCT          JAN95      FEB95      MAR95
-------------- ---------- ---------- ----------
TENTS                  NO         NO         NO
CANOES                 NO         NO        YES
RACQUETS               NO         NO         NO
SPORTSWEAR             NO         NO         NO
FOOTWEAR              YES         NO        YES

You might think that limiting the PRODUCT dimension using only the simple Boolean expression sales gt 90000 (as shown below) would give you your desired result.

limit product to sales gt 90000
status product
report sales

However, when the Boolean expression is evaluated, no looping is performed through the SALES variable to create and return an array of values for the PRODUCT dimension. Instead, only the first value in the dimension's status list is used for each dimension in SALES other than the PRODUCT dimension. In this case, JAN95 is used for the value of the MONTH dimension of the SALES variable and BOSTON is used for the value of the DISTRICT dimension.

For JAN95 and BOSTON, the Boolean expression evaluates to TRUE only for the FOOTWEAR product. Consequently, only FOOTWEAR is in status for the PRODUCT dimension.

As shown below, a report of sales in Boston only displays values for the FOOTWEAR product that have sold more than $90,000 on at least one occasion during January through March 1995.

The current status of PRODUCT is:
FOOTWEAR
DISTRICT: BOSTON
               -------------SALES--------------
               -------------MONTH--------------
PRODUCT          JAN95      FEB95      MAR95
-------------- ---------- ---------- ----------
FOOTWEAR        91,406.82  86,827.32 100,199.46

Limiting a dimension to all dimension values that match the expression

The way to limit a dimension to all dimension values that match a Boolean expression is to use the ANY function with the Boolean expression.

Example: Limiting using the ANY function with a Boolean expression

The LIMIT command (shown below) illustrates how to use the ANY function to limit the PRODUCT dimension to all dimension values that have a value of more than $90,000 in the SALES variable (that is, CANOES and FOOTWEAR):

The first argument for the ANY function (that is, sales gt 90000) is the Boolean expression you want to evaluate.
The second argument for the ANY function (that is, product) indicates the dimensionality of the result of the Boolean expression.

In this example, when the Boolean function is evaluated, a test is performed for TRUE values along the PRODUCT dimension, and returns an array of values.

limit product to any(sales gt 90000, product)
status product
report sales

The PRODUCT dimension has both CANOES and FOOTWEAR in status. Both of these products sold more than $90,000 on at least one occasion during January through March 1995.

As shown below, a report for sales in Boston displays both the CANOES and FOOTWEAR products.

The current status of PRODUCT is:
CANOES, FOOTWEAR
DISTRICT: BOSTON
               -------------SALES--------------
               -------------MONTH--------------
PRODUCT          JAN95      FEB95      MAR95
-------------- ---------- ---------- ----------
CANOES          66,013.92  76,083.84  91,748.16
FOOTWEAR        91,406.82  86,827.32 100,199.46

Related information

For more information, see the following table.

IF you want documentation about . . . THEN see . . .

limiting dimensions,

the rest of this chapter
the entry for the LIMIT command in OLAP DML Reference

creating expressions,

Chapter 4

converting data types,

the entry for the CONVERT function in OLAP DML Reference

the ANY function,

the entry for the function in OLAP DML Reference

IF you want documentation about . . .	THEN see . . .
limiting dimensions,	the rest of this chapter the entry for the LIMIT command in OLAP DML Reference
creating expressions,	Chapter 4
converting data types,	the entry for the CONVERT function in OLAP DML Reference
the ANY function,	the entry for the function in OLAP DML Reference