Returns the smallest possible subset of a set for which the total results of a numeric evaluation are at least a given sum. Elements of the result set are listed from largest to smallest.
TopSum ( set, numeric_value_expression1, numeric_value_expression2 )
set | The set from which to select the highest-summing elements. |
numeric_value_expression1 | The given sum. |
numeric_value_expression2 | The numeric evaluation. |
The following query selects the top-selling markets that collectively contribute 60,000 to the total yearly Diet products sales, and displays the quarterly sales for each Diet product.
SELECT CrossJoin( [Product].[Diet].Children, [Year].Children ) ON COLUMNS, TopSum( [Market].Levels(0).Members, 60000, [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 |
Florida | 620 | 822 | 843 | 783 | 548 | 611 | 657 | 577 | 332 | 323 | 260 | 159 |
Oregon | 389 | 303 | 277 | 322 | 1006 | 921 | 892 | 991 | 263 | 231 | 197 | 184 |
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |