Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / Analytics / InterSystems MDX Reference / MDX Functions / BOTTOMPERCENT
Previous section   Next section

BOTTOMPERCENT

Sorts a set and returns a subset from its lower-valued end, given a cutoff percentage that is applied to a total across members.

Returned Type

This function returns a set of members or tuples, depending on the set used.

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.
  • ordering_expression is a numeric-valued expression that determines the order of the set members.
    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

Previous section   Next section