Useful functions - Microstrategy

Some useful functions to use in metrics include

  • rank
  • count
  • running and moving sums and averages (or OLAP functions)
  • N-tile
  • first and last

All of the above except count and first/last are non-group functions.


In rank functions, you specify the metric to be ranked and whether to rank in ascending or descending order. You can also specify whether to break by an attribute.

The level of the rank depends on the level in the report. For example, the following metric combined with customer on a report shows the highest revenue customer as number one.

Rank (Revenue) <Ascending+False>

Adding a parameter to break by year displays the customer generating the highest revenue in each year as number one.


The count function is usually used with an attribute, although a fact can also be counted. You can specify whether to count from a fact or a lookup table and whether to count distinct occurrences of the target.

For example, a metric can count the number of customers in a specified region, based on the lookup table. Another metric calculates the number of customers who generated revenue in a particular month. This calculation is based on a fact table containing the revenue fact.

Running and moving sums and averages

These functions include

  • moving average
  • moving sum
  • running average
  • running sum

All these functions are similar and are called OLAP functions in the Metric Editor. The running sum function uses a metric as input and calculates a cumulative total of values based on a sort order specified in the metric definition. The sort order is not based on the report level. For example, a report with dates, revenue, and month-to-date revenue is needed. The month-to-date revenue is defined as

RunningSum(Revenue) <Sort Ascending by Date>.

For input, the moving sum and average require a metric and a window size, that is, a date range.


The N-tile function, which is also referred to as segmentation, sets up numbers of groups, or tiles, for a metric. Examples of required parameters are the number of groups and whether they should be sorted in ascending or descending order.

An example of an N-tile function in use is displaying what items are in the top 25% of sales, the next 25%, and so on. Use the N-tile function with the Revenue metric. Because the results are in quartiles (four groups of 25 each), the number of tiles is four.

First and Last

The First and Last functions provide the ability to use sort-by inside aggregation functions, that is, functions where the value depends on the sort order. First returns the First value in a sorted set of values, while Last returns the last value. You can define the sort attributes in the function parameters.

For example, an inventory report lists the on-hand supply for each day. The report subtotals are the last day’s inventory. Creating a user-defined subtotal that uses the Last function provides the needed last day inventory subtotal. If the sort parameters of the function are not set to sort by Day, the function may not provide the correct answer.

For a sample scenario using the First function, see User-defined subtotal example (First function) in Chapter 2, Reports. For more details on the functions themselves, see the MicroStrategy Analytical Engine Functions Reference.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Microstrategy Topics