Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • XL3ValueRankLookup

    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