WITHIN Clause

You can add variations of the WITHIN clause to any supported Rank Variations. This syntax lets you rank values within subgroups defined by a report’s attribute values. Subgroup ranking is useful, for example, if you want to rank the top sales reps (in terms of amount won) within each sales region.

Ranking functions without WITHIN

When you use a ranking function without an accompanying WITHIN clause (for example, SELECT RANK(Avg. Won)), the function sequentially ranks all metric values across the rows in a report. The output is identical to RANK(...) WITHIN(ALL IN ALL OTHER DIMENSIONS), as displayed below.

SELECT RANK (Amount_Won)

Ranking functions with WITHIN

When you use ranking functions with an accompanying WITHIN clause, any number of parameter formulations are possible, resulting in different outputs.

WITHIN (attribute1, attribute2, ...)

Ranks metric values within one or more subgroups defined by the report’s attributes.

SELECT RANK (AMOUNT_WON) DESC WITHIN (Vertical, Region)

WITHIN (ALL ATTRIBUTE)

Ranks metric values within one or more subgroups defined by all report attributes other than the attribute specified in the metric (and any other attributes in the same dataset as the attribute specified in the metric).

If the ranking metric is a stand-alone metric, then it will rank as described in Rank Variations.

SELECT RANK (AMOUNT_WON) DESC WITHIN (ALL Vertical)

For example, if Vertical, Region, and Sales Rep attributes are included in the report, the above metric ranks metric values sequentially by Region and Sales Rep, but not by Vertical. 

Notice that if we remove Vertical from the insight, the ranking of Amount Won remains the same as before because the WITHIN ALL clause omits Vertical from the rankings.

WITHIN (ALL OTHER EXCEPT ATTRIBUTE)

Ranks metric values within one or more subgroups defined by the attribute specified in the metric (or any other attributes in the same dataset as the attribute specified in the metric), but overrides all other report attributes.

SELECT RANK (AMOUNT_WON) DESC WITHIN (ALL OTHER EXCEPT Vertical)

If Vertical and Region attributes are included in the report, the above metric ranks Amount Won sequentially by Vertical, but not by Region.

WITHIN (CURRENT)

Ranks metric values within current report’s dimensionality, as specified by the report’s attributes, where every row of the report is handled as a separate group and thus each value is ranked ‘1.’ 

SELECT RANK (AMOUNT_WON) DESC WITHIN (CURRENT)

An exception to the above would occur if you used WITHIN (CURRENT) with the PERCENT_RANK function. That combination would return the percentage of metric values that are less than the current value, which is necessarily zero in a single-value “group.”