Using Ranking Filters

Ranking filters let you calculate a metric only for the highest or lowest ranked values. In MAQL, ranking filters use the TOP and BOTTOM keywords.

Use ranking filters when you want to focus a metric on the values that matter most, such as the top 10 products by revenue, the bottom 5 salespeople by quota attainment, or a revenue trend limited to the top 10 products.

This article shows how to use MAQL ranking filters and how to make the ranking apply to a specific attribute, such as Product.

Before You Start

The examples in this article use the Revenue metric:

SELECT {metric/revenue}
WHERE {label/order_status} NOT IN ("Cancelled", "Returned")

They also build on numeric filters that use submetrics, such as:

SELECT {metric/revenue}
WHERE (SELECT {metric/revenue} BY {label/product_id}) > 100000

Use a Basic TOP Filter

To calculate revenue only for the top 10 values, use TOP(10):

SELECT {metric/revenue}
WHERE TOP(10) OF ({metric/revenue})

If you add this metric to a table next to the original Revenue metric and slice it by Product, the Revenue / Top 10 column shows values only for the 10 products with the highest revenue. The other rows are empty.

If you replace Product with Customer, the same metric shows values for the top 10 customers instead.

This behavior is flexible, but it may not always match what you need. The ranking is evaluated in the context of the attributes used in the visualization. To calculate revenue from the top 10 products regardless of the visualization context, you must make Product part of the metric definition.

Rank by a Specific Attribute

To calculate revenue from the top 10 products, rank the revenue by Product in a submetric.

First, create a Revenue / Top 10 metric:

SELECT {metric/revenue}
WHERE TOP(10) OF ({metric/revenue})

Then create Revenue / Top 10 Products:

SELECT {metric/revenue}
WHERE (SELECT {metric/revenue} / Top 10 BY {label/product_id}) > 0

The BY {label/product_id} clause makes Product the attribute used for the ranking.

Define the Ranking in One Metric

You can also define the same logic in a single metric without creating a separate Revenue / Top 10 metric:

SELECT {metric/revenue}
WHERE (SELECT (SELECT {metric/revenue} WHERE TOP(10) OF ({metric/revenue})) BY {label/product_id}) > 0

This metric calculates revenue only for products that are in the top 10 by revenue.

Rank by a Different Metric

The metric that determines the ranking does not have to be the metric that the final metric returns.

For example, you can calculate revenue only for the top 5 products by quantity sold. The final metric returns Revenue, but the ranking is based on Quantity Sold.

SELECT {metric/revenue}
WHERE (SELECT (SELECT {metric/quantity_sold} WHERE TOP(5) OF ({metric/quantity_sold})) BY {label/product_id}) > 0

Use this pattern when you want to display one metric but use another metric to decide which values are included.

Use a Different Level of Detail

The ranking can be calculated at a different level of detail than the visualization.

For example, you can show a revenue trend by month but include only the products that are in the top 10 by total revenue. In this case, the visualization is sliced by month, but the ranking is calculated by product.

SELECT {metric/revenue}
WHERE (SELECT (SELECT {metric/revenue} WHERE TOP(10) OF ({metric/revenue})) BY {label/product_id}) > 0

When you use this metric in a visualization by month, the visualization shows the revenue trend for products that match the top 10 product ranking condition.

Use Percentage-Based Ranking

To rank by percentage instead of a fixed number of items, add % to the ranking value.

For example, to calculate revenue from the top 10% of products:

SELECT {metric/revenue}
WHERE (SELECT (SELECT {metric/revenue} WHERE TOP(10%) OF ({metric/revenue})) BY {label/product_id}) > 0

Use BOTTOM Filters

Use BOTTOM the same way as TOP when you want to calculate a metric for the lowest ranked values.

For example, to calculate revenue from the bottom 10 products by revenue:

SELECT {metric/revenue}
WHERE (SELECT (SELECT {metric/revenue} WHERE BOTTOM(10) OF ({metric/revenue})) BY {label/product_id}) > 0

To calculate revenue from the bottom 10% of products:

SELECT {metric/revenue}
WHERE (SELECT (SELECT {metric/revenue} WHERE BOTTOM(10%) OF ({metric/revenue})) BY {label/product_id}) > 0

Ties in Ranking Results

TOP and BOTTOM preserve ties at the ranking boundary. This means the result can include more items than the number you set.

For example, if you use TOP(5) and the fifth highest value is shared by two products, both products are included.

In Analytical Designer, ranking filters also support strict ranking directions: