Extended TOP and BOTTOM Ranks

In GoodData, you can specify Ranking Filters. These filters may be TOP rank or BOTTOM rank.

The image below shows where you can specify the Ranking Filters in the Filter tab.

You can use the ranking filters in other computations as an inner metric or conditional metric thanks to the Extensible Analytics Engine (XAE) in GoodData (see the XAE - Extensible Analytics Engine section).


The following example use the GoodSales demo project.

TOP Example

You want to know the Top 5 salespeople by region for the last quarter, based on the number of closed opportunities. Create a metric that you can use as a multiple of a bonus calculation later.

To generate the metric value, use the MAQL TOP statement, sliced by Region and Sales Representative.


SELECT # of Won Opps. WHERE TOP(5) OF (# of Won Opps.)

How: Region, Sales Representative

The OF keyword in this metric allows ranking by multiple metrics and interprets the metric as a sub-metric.

Below, you can see the results of the metric defined above:

Using WITHIN in a rank statement

The above shows only one region in the display. 

To display a report with the top 5 salesperson for each region, insert the WITHIN (Region) statement into the previous metric:


SELECT # of Won Opps. 
WHERE TOP(5) OF (# of Won Opps. ) 
WITHIN (Region)

How: Region, Sales Representative

The WITHIN (Region) statement locks the granularity level for the metric on the Region level. If the above metric is used in context with region, it represents the top 5 selections for each region.

This calculation is part of a metric and you can use it for any further computations or as a nested metric in which another top or bottom report filter is applied to it. Neat, huh?


You can also rank by percentages. Instead of inserting the ranking number, change the value to a percent and include the percent sign.

To display the top 5 percent of the salespeople by region for the last quarter, add the % after the 5:


SELECT # of Won Opps. 
WHERE TOP(5%) OF (# of Won Opps.) 
WITHIN (Region)