TopPercent
The MDX TopPercent() function for Essbase returns the smallest possible subset of a set for which the total results of a numeric evaluation are at least a given percentage. Elements in the result set are listed from largest to smallest. This function ignores negative and missing values.
Syntax
TopPercent ( set, percentage, numeric_value_expression )Parameters
- set
-
The set from which the top-percentile elements are selected.
- percentage
-
The percentile. This argument must be a value between 0 and 100.
- numeric_value_expression
-
The expression that defines the selection criteria (see MDX Grammar Rules).
Example
The following MDX query selects the top-selling markets that contribute 25% of the total yearly Diet products sales, and displays the quarterly sales for each Diet product.
SELECT
CrossJoin(
[Product].[Diet].Children,
[Year].Children
)
ON COLUMNS,
TopPercent(
[Market].Levels(0).Members,
25,
[Product].[Diet]
)
ON ROWS
FROM Sample.Basic
WHERE ([Scenario].[Actual],
[Measures].[Sales])The above query returns the following grid:
Table 4-133 Output Grid from MDX Example
| (axis) | 100-20 | 100-20 | 100-20 | 100-20 | 200-20 | 200-20 | 200-20 | 200-20 | 300-30 | 300-30 | 300-30 | 300-30 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| (axis) | Qtr1 | Qtr2 | Qtr3 | Qtr4 | Qtr1 | Qtr2 | Qtr3 | Qtr4 | Qtr1 | Qtr2 | Qtr3 | Qtr4 |
| Illinois | 755 | 958 | 1050 | 888 | 1391 | 1520 | 1562 | 1402 | 675 | 755 | 859 | 894 |
| California | 367 | 491 | 506 | 468 | 1658 | 1833 | 1954 | 1706 | 700 | 802 | 880 | 673 |
| Colorado | 700 | 802 | 880 | 673 | 549 | 465 | 412 | 539 | 1006 | 921 | 892 | 991 |