Revision as of 13:53, 28 April 2016 by Antonio.remedios (talk) (>30 parameters allowed)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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.


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 the value of 'AS800/195', the 2nd ranked product in the tuple ([Time].[2003], [KeyFigures].[Revenue], [Scenario].[Budget]).

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

See Also