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().
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:
Sort a set of tuples
Sort a set of tuples into hierarchical order.
Select the top N tuples of a set.
Select the bottom N tuples of a set.
Select the top tuples of a set whose sum meets a threshold.
Select the bottom N tuples of a set whose sum meets a threshold.
Select the tuples of a set corresponding to the top N% of all in set.
Select the tuples of a set corresponding to the bottom N% of all in set.
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 ...