## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# Chapter 6. Sorting and Ranking in MDX

A very common requirement for analytical applications is providing sorted and ranked views of data. Users are always interested in Top−10 lists, and often in seeing who or what was #1 last month or year. We'll cover the basics of sorting and ranking in this chapter, and also look at some ins and outs for more advanced kinds of sorting and ranking calculations.

Functions used in this chapter are `Order(), Hierarchize(), Top-Count(), BottomCount(), TopSum(), BottomSum(), TopPercent(), BottomPercent(), YTD(), CoalesceEmpty(), Rank(), ParallelPeriod(), Generate(), Ancestor(), Descendants(),.Properties(), DrillDownLevelTop()`.

# The Function Building Blocks

We took a brief look at the `Order()` function in Chapter 1, so we've seen one building block already. The full set of functions provided for sorting and ranking purposes are as follows:

FUNCTION

PURPOSE

`Order()`

Sort a set of tuples

`Hierarchize()`

Sort a set of tuples into hierarchical order.

`TopCount()`

Select the top N tuples of a set.

`BottomCount()`

Select the bottom N tuples of a set.

`TopSum()`

Select the top tuples of a set whose sum meets a threshold.

`BottomSum()`

Select the bottom N tuples of a set whose sum meets a threshold.

`TopPercent()`

Select the tuples of a set corresponding to the top N% of all in set.

`BottomPercent()`

Select the tuples of a set corresponding to the bottom N% of all in set.

`Rank()`

Find the ordinal position of a tuple in a set.

Note that all of these functions operate on sets of tuples, not just one-dimensional ...

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required