Returns a set of n elements ordered from largest to smallest, optionally based on an evaluation.
This function ignores missing values.
TopCount ( set , index [,numeric_value_expression ] )
| set | The set from which to select the top n elements. |
| index | The number of elements to include in the set (n). |
| numeric_value_expression | Optional. An expression further defining the selection criteria. |
The following query selects the five top-selling markets in terms of yearly Diet products sales, and displays the quarterly sales for each Diet product.
SELECT
CrossJoin(
[Product].[Diet].Children,
[Year].Children
)
ON COLUMNS,
TopCount(
[Market].Levels(0).Members,
5,
[Product].[Diet]
)
ON ROWS
FROM Sample.Basic
WHERE ([Scenario].[Actual], [Measures].[Sales])
This query returns the grid:
| 100-20 | 200-20 | 300-30 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 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 |
| Washington | 637 | 712 | 837 | 704 | 459 | 498 | 597 | 514 | 944 | 799 | 708 | 927 |
| Iowa | 162 | 153 | 121 | 70 | 129 | 129 | 129 | 129 | 1658 | 1833 | 1954 | 1706 |
| ©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |