| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3ValueRankLookup"
(→See Also) |
(Added links to wizard) |
||
| Line 1: | Line 1: | ||
Returns the value at the specified position of a ranking. To return the member name instead, use the [[XL3RankLookup|XL3RankLookup formula]]. | Returns the value at the specified position of a ranking. To return the member name instead, use the [[XL3RankLookup|XL3RankLookup formula]]. | ||
| − | Use the {{Menu|XLCubed|Insert Formula|Ranking}} menu or ribbon item to insert the formula using a wizard. | + | Use the {{Menu|XLCubed|Insert Formula|Ranking}} menu or ribbon item to insert the formula using a wizard. See [[Insert Ranking|here]] for details. |
==Syntax== | ==Syntax== | ||
| Line 44: | Line 44: | ||
* [[Formula Reference]] | * [[Formula Reference]] | ||
* [[XL3RankLookup|XL3RankLookup formula]] | * [[XL3RankLookup|XL3RankLookup formula]] | ||
| + | * [[Insert Ranking|Insert Ranking wizard]] | ||
* [[Breakout Value]] | * [[Breakout Value]] | ||
[[Category:Formulae]] | [[Category:Formulae]] | ||
[[Category:OLAP Formulae]] | [[Category:OLAP Formulae]] | ||
Revision as of 16:20, 13 October 2010
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.
Syntax
XL3ValueRankLookup( Connection, Position, TopOrBottom, MaxCount, Measure, RankHierarchy, RankMembers, Hierarchy1, Member1,…, [Hierarchy11], [Member11] )
Parameters
| Parameter | Description |
|---|---|
| Connection | Connection number to use |
| Position | Position in the ranking to return |
| TopOrBottom | Whether to return the highest values or the lowest. 0=Top 1=Bottom |
| 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" )
