DeepSee MDX Reference
BOTTOMSUM

 Server:docs2 Instance:LATEST User:UnknownUser

 [ Home ]  >  [ InterSystems DeepSee ]  >  [ DeepSee MDX Reference ]  >  [ MDX Functions ]  >  [ BOTTOMSUM ] Go to: Returned Type Syntax and Details Example See Also Search:

Sorts a set and returns a subset from its lower-valued end, given a cutoff value that is applied to a total across elements.
Returned Type
This function returns a set of members or tuples, depending on the set used.
Syntax and Details
`BOTTOMSUM(set_expression, cutoff_value, ordering_expression)`
• cutoff_value is a numeric literal.
The function uses this argument to determine the cutoff value for elements to return in the subset.
For all elements in the returned subset, the sum of the values of ordering_expression will be less than or equal to cutoff_value.
• ordering_expression is a numeric-valued expression that determines the order of the set elements.
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 an example in which the cutoff value is high enough to include all members:
```SELECT MEASURES.[%COUNT] ON 0,
BOTTOMSUM(birthd.decade.MEMBERS, 10000, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 1910s                                 71
2 2010s                                155
3 1920s                                223
4 1930s                                572
5 1940s                                683
6 1950s                              1,030
7 1980s                              1,400
8 1990s                              1,413
9 2000s                              1,433
10 1960s                              1,500
11 1970s                              1,520```
Now consider a variation in which the cutoff value is set to 2500:
```SELECT MEASURES.[%COUNT] ON 0,
BOTTOMSUM(birthd.decade.MEMBERS, 2500, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 1910s                                  71
2 2010s                                 155
3 1920s                                 223
4 1930s                                 572
5 1940s                                 683```