Returns the value at the specified position of a ranking. To return the member name instead, use the XL3RankLookup formula.
Use the XLCubed > Insert Formula > Ranking menu or ribbon item to insert the formula using a wizard. See here for details.
XL3ValueRankLookup( 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.
|Connection||Connection number to use|
|Position||Position in the ranking to return|
|RankingType|| The ranking type. Use the sum of the following possibilities.
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 the value of 'AS800/195', the 2nd ranked product in the tuple ([Time]., [KeyFigures].[Revenue], [Scenario].[Budget]).
=XL3ValueRankLookup( 1, 2, 0, 10, "[Measures].[Value]", "Product", XL3MemberSet( 1, "[Product]", "", "DescendantsAt", "[Product].[Product]" ), "Time", "2003", "KeyFigures", "Revenue", "Scenario", "Budget" )