You are viewing our older product's guide. Click here for the documentation of GoodData Cloud, our latest and most advanced product.

Using Ranking Filters

In this article, you will learn how to use the MAQL’s TOP and BOTTOM ranking filters.

So far we have discussed two types of filters:

Attribute filters in expressions such as the original definition of Revenue:
```
SELECT Revenue WHERE Order Status NOT IN (Cancelled, Returned)
```

Numeric filters defined using submetrics such as

SELECT Revenue WHERE (SELECT Revenue BY Product) > 100000

What if you want to compute only the revenue for the top 10 highest selling products? Or top 10%?

This is why MAQL includes the TOP keyword (and the corresponding BOTTOM).

Let’s try to create a few metrics to see these keywords in action:

Revenue / Top 10

SELECT Revenue WHERE TOP(10) OF (Revenue)

Let’s put this metric into a table together with the original Revenue metric and slice it by Product. While the Revenue metrics shows Revenue for each product, the cells in the Revenue / Top 10 column are empty except for the top highest numbers. However, if we replace the Product attribute with Customer, the Top 10 metric gives us non-zero numbers for the top 10 customers.

That’s cool and flexible but not exactly what we want to achieve. If we want to compute Revenue from the top 10 highest selling products, we have to express that we care only about top Products somewhere in our metric. We can achieve that using the submetric aggregated by Product using the BY keyword. Let’s define our Revenue / Top 10 Products as

SELECT Revenue WHERE (SELECT Revenue / Top 10 BY Product ID) > 0

Alternatively, if we don’t see any use for having the separate Revenue / Top 10 metric, we can delete it and define our Revenue / Top 10 Products as

SELECT Revenue
WHERE (SELECT (SELECT Revenue WHERE TOP(10) OF (Revenue)) BY Product ID) > 0

Do you care about the top 10% rather than about just the top 10? Just add the percent sign:

SELECT Revenue
WHERE (SELECT (SELECT Revenue WHERE TOP(10%) OF (Revenue)) BY Product ID) > 0

We will leave the BOTTOM keyword up to the kind reader as homework and focus on yet another feature of the ranking metrics.

WHERE TOP or BOTTOM(n) OF

PERCENTILE