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.

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).

Notes

This function ignores negative and missing values.

Example

The following 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])

This query returns the grid:

Table 4-134 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