DeepSee MDX Reference
TOPPERCENT
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Sorts a set and returns a subset from its higher-valued end, given a cutoff percentage that is applied to a total across set elements.
Returned Type
This function returns a set of members or tuples, depending on the set used.
Syntax and Details
TOPPERCENT(set_expression, percentage, ordering_expression)
Example
First consider the following query and the results it returns:
SELECT MEASURES.[%COUNT] ON 0, 
TOPPERCENT(birthd.decade.MEMBERS, 100, MEASURES.[%COUNT]) ON 1 FROM patients
 
                             Patient Count
 1 2000s                                157
 2 1980s                                155
 3 1990s                                144
 4 1960s                                136
 5 1970s                                128
 6 1950s                                107
 7 1930s                                 56
 8 1940s                                 54
 9 2010s                                 44
10 1920s                                 13
11 1910s                                  6
Because percentage is 100, all members are returned.
Now consider a variation of the preceding, in which percentage is 50, so that we see the top 50 percent:
SELECT MEASURES.[%COUNT] ON 0, TOPPERCENT(birthd.decade.MEMBERS, 50, MEASURES.[%COUNT]) ON 1 FROM patients
 
                             Patient Count
1 2000s                                 157
2 1980s                                 155
3 1990s                                 144
4 1960s                                 136
The total for the %COUNT measure for these members is a little more than 50% of the total. (If the 1960s were omitted, the total count would be less than 50%.)
See Also