MicroStrategy ONE

Rank and NTile functions

Although Rank belongs to the OLAP functions group, it differs from the other OLAP function in that its SortBy (ByValue) parameter uses metrics, instead of attributes. In the VLDB Properties, a ranking option can be set in order to designate ranking by ODBC, the Analytical Engine, or the database.

NTile functions are used to group the values in an ordered list into one of several buckets or NTiles. Each element in the list is assigned an integer corresponding to the bucket to which it belongs. The various NTile functions 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 on.

For additional information on Rank and NTile functions, refer to the MicroStrategy Functions Reference.

N-Tile

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)),...,ceiling(remainder*(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.

Information returned

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)

Syntax

NTile<Ascending=True, Tiles=4, BreakBy = {}>(Argument)

  • Ascending is a true/false parameter that designates the organization of data within the NTiles.

  • Tiles is a positive integer that designates the number of buckets or NTiles.

  • BreakBy is the parameter that sets the attribute determining where the calculation restarts.

  • Argument is a metric representing a list of values to be distributed equally (if possible) into n buckets.

N-Tile Size

Distributes the values in the specified metric, sorted in either ascending or descending order, with the same number of element in each bucket. The number of elements in each bucket is user-defined.

Syntax

NTileSize<Ascending = True, BreakBy = {}>(Argument, Size)

  • Argument is a metric representing a list of values to be distributed in buckets.

  • Ascending is a true/false parameter that designates the organization of data within the NTiles.

  • BreakBy is the parameter that sets the attribute determining where the calculation restarts.

  • Size is a positive integer that designates the number of elements per bucket.

N-Tile Value

Distributes the values in the metric by value ranges over a user-defined number of buckets, sorted in either ascending or descending order. Each bucket is the same size in terms of the range of values contained in the bucket, but the number of elements per bucket is not equal.

Syntax

NTileValue<Ascending = True, Segments = 4, BreakBy = {}>(Argument)

  • Ascending is a true/false parameter that designates the organization of data within the NTiles.

  • Segments is a positive integer designating the number of buckets in which the values are distributed.

  • BreakBy is the parameter that sets the attribute determining where the calculation restarts.

  • Argument is a metric representing a list of values to be distributed into buckets.

N-Tile Value Size

Distributes the values in the metric, sorted in either ascending or descending order, and distributed across buckets based on a user-specified value range.

Syntax

NTileValueSize<Ascending = True, BreakBy = {}>(Argument, Size)

  • Ascending is a true/false parameter that designates the organization of data within the Ntiles.

  • BreakBy is the parameter that sets the attribute determining where the calculation restarts.

  • Argument is a metric representing the list of values to be distributed across buckets.

  • Size is a real number designating the size of the range of values for each bucket.

Percentile

Returns the percent-th percentile of values in a given range. This function can be used to establish thresholds. For example, which states have revenue above the 75th percentile for their region? This is a group-value function.

Syntax

Percentile<Ascending = True, FactID>(Argument, Percent)

  • Argument is an attribute, fact or metric representing a list of numbers.

  • Ascending is a true/false parameter that indicates the organization of the data.

  • Percent is the decimal value of the percent for which you want to find the threshold of the percentile value.

Rank

Returns the rank of the values in a list, relative to the other values. The calculation can restart based on attributes identified in the parameter settings.

Rank metrics must have an outer join assigned to them so they return all attribute elements that have been ranked, regardless of whether the element has a null value or not.

Unless the defaults are changed, the function ranks the values in ascending order by the value of the metric, and the rank is an integer.

Syntax

Rank<Asc = True, ByValue, BreakBy, NullInclude>(Argument)

  • Asc is a true/false parameter that indicates the order of ranking (meaning is 1 the lowest or highest value).

  • ByValue is a true/false parameter that indicates if the ranking is done by integer values (1,2,3,4) or by percentage (10%, 50%, 75%, 100%).

  • BreakBy is the parameter that designates where the calculation should restart.

  • Argument is a fact or metric representing a list numbers.

  • NullInclude is a parameter that determines how NULL values are included in the rank calculation.

    The NullInclude parameter only affects the rank of NULL values if the Rank function is performed by the MicroStrategy Analytical Engine. The Rank function is performed by the Analytical Engine for smart metrics, derived metrics, and other metric scenarios. To determine whether the Rank function for a metric is performed by the Analytical Engine, view the SQL statement for the report. If the metric is listed in the Analytical Engine calculation steps, this verifies that the Rank function is performed by the Analytical Engine.

    If the Rank function is performed on a database, the NullInclude parameter is ignored and NULL values are included in the rank calculation based on the database standards.

    For Rank functions that are performed by the Analytical Engine, you have the following options for this parameter:

  • 1: If you define NullInclude=1, NULL values are given a rank value equal to the number of other rank values, plus one. For example, the rank of the Profit metric in the report below ranks four separate profit values.

    There is one NULL value, which is given the rank of 4. The Rank (Profit) metric in this example uses an ascending rank. If you define the metric with a descending rank, the other rank values change but the rank value for any NULL values remains the same. This is shown in the report below.

     

  • -1: If you define NullInclude=-1, NULL values are given the rank value of one. For example, the reports shown below both define the Rank (Profit) metric with NullInclude=-1. The report on the left uses an ascending rank, while the report on the right uses a descending rank.

    As shown in the reports below, the NULL values for both reports are ranked with the value of one.

     

  • 0 (default): If you define NullInclude=0, NULL values are included in the rank calculation based on the NULL value handling defined using the Null checking for Analytical Engine VLDB property. For information on VLDB properties, including steps to access and modify them for various MicroStrategy objects, refer to the System Administration Help.

  • If you define the Null checking for Analytical Engine property as True, NULL values are treated as zero values in the rank calculation. For example, the report shown below ranks the NULL values with a rank of two, because zero is greater than -10 and less than 40.


  • If you define the Null checking for Analytical Engine property as False, NULL values are treated as NULL values, which means NULL values are also displayed for the rank values. For example, the report shown below displays the NULL values as NULL in the rank.