Extended TOP and BOTTOM Ranks

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

Contents:

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

Ranking filters

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 Conditionals on XAE).

Examples

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.

What:

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.

If you use the IN statement, it requires SELECT and interprets the ranking metric as a sub-report.

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

Resulting table

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:

What:

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


How:
 Region, Sales Representative

Resulting report

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?

The cluster dimensionality (i.e. each Region represents a cluster) must be less than the dimensionality of the ranking metrics. Otherwise, an exception is thrown.

The following metric generates an exception:

SELECT # of Won Opps. 
WHERE TOP(5) IN (SELECT SUM(Amount) BY Year) 
WITHIN (Month)

Percentages

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:

What:

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