DeepSee MDX Reference
TOPSUM
[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 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
TOPSUM(set_expression, cutoff_value, ordering_expression)
Example
First consider an example in which the cutoff value is high enough to include all members:
SELECT MEASURES.[%COUNT] ON 0, 
TOPSUM(birthd.decade.MEMBERS, 10000, MEASURES.[%COUNT]) ON 1 FROM patients
                             Patient Count
 1 1970s                              1,520
 2 1960s                              1,500
 3 2000s                              1,433
 4 1990s                              1,413
 5 1980s                              1,400
 6 1950s                              1,030
 7 1940s                                683
 8 1930s                                572
 9 1920s                                223
10 2010s                                155
11 1910s                                 71
Now consider a variation in which the cutoff value is set to 2500:
SELECT MEASURES.[%COUNT] ON 0, 
TOPSUM(birthd.decade.MEMBERS, 2500, MEASURES.[%COUNT]) ON 1 FROM patients
                             Patient Count
1970s                                 1,520
See Also