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).
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.
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:
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
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)