XL3RankLookup

Returns the member at the specified position of a ranking. To return the value of the member instead, use the XL3ValueRankLookup formula.

Use the XLCubed > Insert Formula > Ranking menu or ribbon item to insert the formula using a wizard. See here for details.

Syntax

XL3RankLookup( Connection, Position, TopOrBottom, MaxCount, Measure, RankHierarchy, RankMembers, Hierarchy1, Member1,…, [Hierarchy100], [Member100] )

Before XLCubed Version 9, this formula was limited to 30 parameters. This allowed up to 11 hierarchy-member pairs.

Parameters

Parameter Description
Connection Connection number to use
Position Position in the ranking to return
RankingType The ranking type. Use the sum of the following possibilities.
0 Use defaults
1 Retrieve the lowest values instead of the highest
2 Exclude nulls from the results
4 Exclude zeros from the results

For example, to return a ranking excluding both nulls and zeros, use 6.

MaxCount The maximum number of members to return in the ranking
Measure The measure on which to rank
RankHierarchy Name of the hierarchy that the following member applies to e.g. "Measures" or "[Customer].[Customer Geography]"
RankMembers An XL3MemberSet formula used to specify what to rank over
Hierarchy1,…, HierarchyN Name of the hierarchy that the following member applies to e.g. "Measures" or "[Customer].[Customer Geography]"
Member1,…, MemberN Either a single member unique name or an XL3Member formula to filter the ranking across

Examples (based on the Bicycle Sales cube)

Returns 'AS800/195', the 2nd ranked product in the tuple ([Time].[2003], [KeyFigures].[Revenue], [Scenario].[Budget]).

=XL3RankLookup( 1, 2, 0, 10, "[Measures].[Value]", "Product", XL3MemberSet( 1, "[Product]", "", "DescendantsAt", "[Product].[Product]" ), "Time", "2003", "KeyFigures", "Revenue", "Scenario", "Budget" )

See Also