This is the last one in the series“Top to Bottom in MDX”. In the first part we have seen Topcount and Bottomcount functions and in the second part, Toppercent and Bottompercent (Toppercent and Bottompercent in MDX). In this part lets check out Topsum and Bottomsum.

Topsum

Like Toppercent, Topsum returns the topmost tuples in the set whose cumulative total hits the specified value by sorting the data in descending order.

1_SQL_Server_Topsum_and_Bottomsum_in_MDX

In the query we have specifies value of 2500000 for the second parameter. The query first sorts the Resellers by Sales Amount from largest to smallest and then return the top N Resellers till the sum of their Sales Amount is at least $2500000. In our example, the first two Resellers sold worth $1730956.37 (877107.19 + 853849.18) which is still less than $2500000. So third Reseller is added with Sales Amount $841908.77 which brings the total sum to $2572865.14and we got ourbest Resellers.

Bottomsum

This is almost as simple and works exactly opposite to Topsum. It returns the tuples with the smallest values whose cumulative total hits the specified value by sorting the data in ascending order. The following example will illustrates the same.

2_SQL_Server_Topsum_and_Bottomsum_in_MDX

Again the nonempty() functionis added to eliminate null. The result showing is the worst eight Resellers with total sales of only $500.

Summary

Hope you have enjoyed the series. Below is the quick recap of what we have learned so far.

Function Description
Topcount () Selects the top N tuples of a set by sorting a set in descending order.
Bottomcount () Selects the bottom N tuples of a set by sorting a set in ascending order.
Toppercent () Selects the tuples of a set corresponding to the top N% of all in set by sorting a set in descending order
Bottompercent () Selects the tuples of a set corresponding to the bottom N% of all in set by sorting a set in ascending order.
Topsum () Selects the top tuples of a set whose sum meets a threshold by sorting a set in descending order.
Bottomsum () Selects the bottom N tuples of a set whose sum meets a threshold by sorting a set in ascending order.

 

Regards

Amit Karkhanis

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook