Difference between revisions of "XL3RankLookup"
(Added reference to XL3ValueRankLookup) |
m (Text replacement - "XLCubed" to "FluenceXL") |
||
| (8 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
Returns the member at the specified position of a ranking. To return the value of the member instead, use the [[XL3ValueRankLookup|XL3ValueRankLookup formula]]. | Returns the member at the specified position of a ranking. To return the value of the member instead, use the [[XL3ValueRankLookup|XL3ValueRankLookup formula]]. | ||
| − | Use the {{Menu| | + | Use the {{Menu|FluenceXL|Insert Formula|Ranking}} menu or ribbon item to insert the formula using a wizard. See [[Insert Ranking|here]] for details. |
==Syntax== | ==Syntax== | ||
| − | {{Code|XL3RankLookup( Connection, Position, TopOrBottom, MaxCount, Measure, RankHierarchy, RankMembers, Hierarchy1, Member1,…, [ | + | {{Code|XL3RankLookup( Connection, Position, TopOrBottom, MaxCount, Measure, RankHierarchy, RankMembers, Hierarchy1, Member1,…, [Hierarchy100], [Member100] )}} |
| + | |||
| + | {{Excel Parameter Limitation|This allowed up to 11 hierarchy-member pairs.}} | ||
==Parameters== | ==Parameters== | ||
| Line 18: | Line 20: | ||
| Position in the ranking to return | | Position in the ranking to return | ||
|- | |- | ||
| − | | {{Code| | + | | {{Code|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 {{Tooltip|6|2 + 4}}. | ||
|- | |- | ||
| {{Code|MaxCount}} | | {{Code|MaxCount}} | ||
| Line 28: | Line 40: | ||
|- | |- | ||
| {{Code|RankHierarchy}} | | {{Code|RankHierarchy}} | ||
| − | | | + | | {{Standard Hierarchy Parameter Description}} |
|- | |- | ||
| {{Code|RankMembers}} | | {{Code|RankMembers}} | ||
| − | | | + | | An [[XL3MemberSet|XL3MemberSet formula]] used to specify what to rank over |
| − | + | {{Standard HierarchyMember List|to filter the ranking across}} | |
| − | |||
| − | | | ||
| − | |||
| − | |||
| − | |||
|} | |} | ||
| Line 44: | Line 51: | ||
''Returns 'AS800/195', the 2<sup>nd</sup> ranked product in the tuple {{Tuple|[Time].[2003]|[KeyFigures].[Revenue]|[Scenario].[Budget]}}.'' | ''Returns 'AS800/195', the 2<sup>nd</sup> ranked product in the tuple {{Tuple|[Time].[2003]|[KeyFigures].[Revenue]|[Scenario].[Budget]}}.'' | ||
| − | {{Code|1==XL3RankLookup( 1, 2, 0, 10, "[Measures].[Value]", "Product", "[Product].[Product] | + | {{Code|1==XL3RankLookup( 1, 2, 0, 10, "[Measures].[Value]", "Product", XL3MemberSet( 1, "[Product]", "", "DescendantsAt", "[Product].[Product]" ), "Time", "2003", "KeyFigures", "Revenue", "Scenario", "Budget" )}} |
==See Also== | ==See Also== | ||
* [[Formula Reference]] | * [[Formula Reference]] | ||
* [[XL3ValueRankLookup|XL3ValueRankLookup formula]] | * [[XL3ValueRankLookup|XL3ValueRankLookup formula]] | ||
| + | * [[Insert Ranking|Insert Ranking wizard]] | ||
| + | |||
| + | [[Category:Formulae]] | ||
| + | [[Category:OLAP Formulae]] | ||
Latest revision as of 08:52, 4 July 2023
Returns the member at the specified position of a ranking. To return the value of the member instead, use the XL3ValueRankLookup formula.
Use the FluenceXL > 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.
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" )
