| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XLCubed MDX Calculation Support"
(→Set functions) |
(→Logical functions) |
||
| (22 intermediate revisions by the same user not shown) | |||
| Line 9: | Line 9: | ||
===Set functions=== | ===Set functions=== | ||
* <code>+</code> - union operator | * <code>+</code> - union operator | ||
| + | * <code>*</code> - crossjoin operator | ||
* <code>{ }</code> - braces | * <code>{ }</code> - braces | ||
** Multiple members/tuples can be combined | ** Multiple members/tuples can be combined | ||
| Line 14: | Line 15: | ||
* Ancestors | * Ancestors | ||
* Children | * Children | ||
| + | * Crossjoin | ||
* Except | * Except | ||
| + | * Filter | ||
* Head | * Head | ||
* Intersect | * Intersect | ||
* Item | * Item | ||
* LastPeriods | * LastPeriods | ||
| + | * Mtd | ||
* PeriodsToDate | * PeriodsToDate | ||
| + | * Qtd | ||
* Siblings | * Siblings | ||
* Subset | * Subset | ||
* Tail | * Tail | ||
* Union | * Union | ||
| + | * Ytd | ||
===Member functions=== | ===Member functions=== | ||
| Line 33: | Line 39: | ||
* DefaultMember | * DefaultMember | ||
* Descendants | * Descendants | ||
| − | * | + | * FirstLast |
* FirstSibling | * FirstSibling | ||
| + | * Is (keyword) | ||
* Item | * Item | ||
* Lag | * Lag | ||
| − | * | + | * LastChild |
* LastSibling | * LastSibling | ||
* Lead | * Lead | ||
* Level | * Level | ||
* NextMember | * NextMember | ||
| + | * NextSibling | ||
* OpeningPeriod | * OpeningPeriod | ||
* ParallelPeriod | * ParallelPeriod | ||
* Parent | * Parent | ||
* PrevMember | * PrevMember | ||
| + | * PrevSibling | ||
===Aggregation funtions=== | ===Aggregation funtions=== | ||
| Line 53: | Line 62: | ||
* Max | * Max | ||
* Min | * Min | ||
| + | * Median | ||
* Sum | * Sum | ||
| Line 73: | Line 83: | ||
** +, -, *, / | ** +, -, *, / | ||
* Abs | * Abs | ||
| + | * CDate | ||
* CDbl | * CDbl | ||
* CInt | * CInt | ||
* Divide | * Divide | ||
| + | * Log | ||
| + | * Log10 | ||
* Mod | * Mod | ||
* Power | * Power | ||
* Round | * Round | ||
| + | * Sqrt | ||
===Logical functions=== | ===Logical functions=== | ||
* IIf | * IIf | ||
| + | * IsAncestor | ||
| + | * IsEmpty | ||
| + | * IsLeaf | ||
| + | * IsSibling | ||
===Hierarchy functions=== | ===Hierarchy functions=== | ||
| Line 97: | Line 115: | ||
* DDB | * DDB | ||
* FV | * FV | ||
| + | * MIRR | ||
* IPmt | * IPmt | ||
| + | * IRR | ||
* NPer | * NPer | ||
| + | * NPV | ||
* Pmt | * Pmt | ||
* PPmt | * PPmt | ||
| Line 105: | Line 126: | ||
* SLN | * SLN | ||
* SYD | * SYD | ||
| + | |||
| + | ===XLCubed custom functions=== | ||
| + | * XL3TagToMember | ||
| + | ** <code>XL3TagToMember(hierarchy, tag)</code> | ||
| + | ** e.g. XL3TagToMember([Account], "#NetIncome") | ||
| + | * XL3TagToSet | ||
| + | ** <code>XL3TagToSet(hierarchy, tag)</code> | ||
| + | ** e.g. XL3TagToSet([Account], "#MyFlag") | ||
==Sample Calculations== | ==Sample Calculations== | ||
| Line 111: | Line 140: | ||
===todo=== | ===todo=== | ||
| + | |||
| + | ===Item=== | ||
| + | |||
| + | Many functions return a set and you might want to choose a particular member from that set. A set is always a set of tuples (not members) so you might need to use the Item() function twice to get a member from the set. | ||
| + | |||
| + | For example in this calculation gets the fourth child of the current Date member. As Children returns a set we pick out the tuple, and then use Item(0) to get the only member in that tuple. | ||
| + | |||
| + | <code> | ||
| + | ( [Measure].[Amount], [Date].CurrentMember.Children.Item(3).Item(0) ) | ||
| + | </code> | ||
| + | |||
| + | ===IRR, MIRR, NPV=== | ||
| + | These functions accept a list of values as an argument, not a set of tuples and a value expression like Sum(), Avg() etc. | ||
| + | |||
| + | For these to work you should specify the member of measures to use so that the calculation does not reference itself. | ||
| + | |||
| + | This will evaulate the values for "My Set" in the context of the IRR measure which can not work. | ||
| + | |||
| + | <code>IRR( MySet )</code> - wrong! | ||
| + | |||
| + | Instead reference the measure you want to calculate for and create a set with that member in. | ||
| + | |||
| + | <code>IRR( { [Measures].[My Measure] } * MySet )</code> | ||
Latest revision as of 12:22, 7 June 2023
For some data sources that do not natively support MDX XLCubed can execute calculations on top of the source database.
Currently this is available for Fluence and Anaplan connection types.
Contents
[hide]Supported Functions
Documentation for these functions can be found in the MDX function reference.
Set functions
+- union operator*- crossjoin operator{ }- braces- Multiple members/tuples can be combined
- Range operator
:is supported [1]
- Ancestors
- Children
- Crossjoin
- Except
- Filter
- Head
- Intersect
- Item
- LastPeriods
- Mtd
- PeriodsToDate
- Qtd
- Siblings
- Subset
- Tail
- Union
- Ytd
Member functions
- Ancestor
- Ascendants
- ClosingPeriod
- Cousin
- CurrentMember
- DefaultMember
- Descendants
- FirstLast
- FirstSibling
- Is (keyword)
- Item
- Lag
- LastChild
- LastSibling
- Lead
- Level
- NextMember
- NextSibling
- OpeningPeriod
- ParallelPeriod
- Parent
- PrevMember
- PrevSibling
Aggregation funtions
- Aggregate
- Avg
- Count
- Max
- Min
- Median
- Sum
Text functions
- CStr
- Format
- Instr
- LCase
- Left
- Len
- LTrim
- Mid
- Right
- RTrim
- Trim
- UCase
Numeric functions
- Standard operators
- +, -, *, /
- Abs
- CDate
- CDbl
- CInt
- Divide
- Log
- Log10
- Mod
- Power
- Round
- Sqrt
Logical functions
- IIf
- IsAncestor
- IsEmpty
- IsLeaf
- IsSibling
Hierarchy functions
- Levels
- Members
Level functions
- Members
- Ordinal
Financial functions
Information on these functions can be found in the VBA function reference
- DDB
- FV
- MIRR
- IPmt
- IRR
- NPer
- NPV
- Pmt
- PPmt
- PV
- Rate
- SLN
- SYD
XLCubed custom functions
- XL3TagToMember
XL3TagToMember(hierarchy, tag)- e.g. XL3TagToMember([Account], "#NetIncome")
- XL3TagToSet
XL3TagToSet(hierarchy, tag)- e.g. XL3TagToSet([Account], "#MyFlag")
Sample Calculations
These templates may be useful starting poitns for your own calculations. You should replace the Dimension, Hierarchy, Level and Measure names with your own.
todo
Item
Many functions return a set and you might want to choose a particular member from that set. A set is always a set of tuples (not members) so you might need to use the Item() function twice to get a member from the set.
For example in this calculation gets the fourth child of the current Date member. As Children returns a set we pick out the tuple, and then use Item(0) to get the only member in that tuple.
( [Measure].[Amount], [Date].CurrentMember.Children.Item(3).Item(0) )
IRR, MIRR, NPV
These functions accept a list of values as an argument, not a set of tuples and a value expression like Sum(), Avg() etc.
For these to work you should specify the member of measures to use so that the calculation does not reference itself.
This will evaulate the values for "My Set" in the context of the IRR measure which can not work.
IRR( MySet ) - wrong!
Instead reference the measure you want to calculate for and create a set with that member in.
IRR( { [Measures].[My Measure] } * MySet )
