DeepSee MDX Reference
BOTTOMPERCENT


Sorts a set and returns a subset from its lowervalued end, given a cutoff percentage that is applied to a total across members.
Returned Type
Syntax and Details
BOTTOMPERCENT(set_expression, percentage, ordering_expression)


percentage is a numeric literal equal to or less than 100. For example, 15 represents 15 percent.
The function uses this argument to determine the cutoff point for elements to return in the subset.
There is usually a member that straddles the cutoff point; this member is assigned to the upper set, rather than the lower set. As a result, in the returned subset, the cumulative total for
ordering_expression could be less than
percentage, as a percentage of the entire set.

The function evaluates this expression for each element of the set and sorts the elements of the set in ascending order according to this value. Any hierarchies are ignored.
Example
First consider the following query and the results it returns:
SELECT MEASURES.[%COUNT] ON 0,
BOTTOMPERCENT(birthd.decade.MEMBERS, 100, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 1910s 6
2 1920s 13
3 2010s 44
4 1940s 54
5 1930s 56
6 1950s 107
7 1970s 128
8 1960s 136
9 1990s 144
10 1980s 155
11 2000s 157
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 bottom 50 percent:
SELECT MEASURES.[%COUNT] ON 0, BOTTOMPERCENT(birthd.decade.MEMBERS, 50, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 1910s 6
2 1920s 13
3 2010s 44
4 1940s 54
5 1930s 56
6 1950s 107
7 1970s 128
The total for the
%COUNT measure for these members is slightly less than the specified threshold (50% of the total).
See Also
Copyright © 19972019 InterSystems Corporation, Cambridge, MA