| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XLCubed MDX Calculation Support"
(→Sample Calculations) |
(→Logical functions) |
||
| (12 intermediate revisions by the same user not shown) | |||
| Line 17: | Line 17: | ||
* Crossjoin | * Crossjoin | ||
* Except | * Except | ||
| + | * Filter | ||
* Head | * Head | ||
* Intersect | * Intersect | ||
| Line 40: | Line 41: | ||
* FirstLast | * FirstLast | ||
* FirstSibling | * FirstSibling | ||
| + | * Is (keyword) | ||
* Item | * Item | ||
* Lag | * Lag | ||
| Line 60: | Line 62: | ||
* Max | * Max | ||
* Min | * Min | ||
| + | * Median | ||
* Sum | * Sum | ||
| Line 83: | Line 86: | ||
* 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 117: | 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 123: | 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=== | ===IRR, MIRR, NPV=== | ||
| Line 131: | Line 158: | ||
This will evaulate the values for "My Set" in the context of the IRR measure which can not work. | This will evaulate the values for "My Set" in the context of the IRR measure which can not work. | ||
| − | <code>IRR( MySet )</code> | + | <code>IRR( MySet )</code> - wrong! |
Instead reference the measure you want to calculate for and create a set with that member in. | Instead reference the measure you want to calculate for and create a set with that member in. | ||
<code>IRR( { [Measures].[My Measure] } * MySet )</code> | <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 )
