MicroStrategy Functions Reference
Rank and NTile functions
© 2011 MicroStrategy, Inc.
differ in how the buckets are defined. Some functions allow you to define the
number of buckets, others allow you to define the size of the buckets, and so
NTile functions are used to group the values in an ordered list into one of
several buckets or NTiles. For the Ntile function, the buckets are calculated
so that each bucket has exactly the same number of rows assigned to it or at
most one row more than the others (the exception is that identical value rows
are placed in the same bucket). For example, if you have 100 rows in a
partition and define an NTile function with four buckets, 25 rows will be
assigned a value of 1, 25 rows will have value 2, and so on. These buckets are
referred to as equiheight buckets.
If the number of rows in the partition does not divide evenly into the number
of buckets, then, barring identical value rows, the number of rows assigned
per bucket will differ by one at most. The extra rows are added to buckets
using the calculations ceiling(1*(buckets/remainder)),...,
For example, if there are 103 distinct value rows in a partition which has an
NTile<Tiles=5>() function, the first 20 rows will be in the first bucket,
the next 21 in the second bucket, the next 20 in the third bucket, the next 21
in the fourth bucket, and the final 21 in the fifth bucket. The calculations
ceiling(1*(5/3))=2, ceiling(2*(5/3))=4, and
ceiling(3*(5/3))=5 include one of the extra three rows each in the
second, fourth, and fifth buckets.
NTile distributes the values in the specified metric, sorted in either
ascending or descending order, over a user-defined number of buckets. Each
bucket has an equal number of elements (if possible). This is an OLAP
NTile <Ascending, Tiles, BreakBy> (Argument)
• Ascending is a TRUE/FALSE parameter that designates the organization
of data within the NTiles.