| 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) |
(→Sample Calculations) |
||
| Line 130: | Line 130: | ||
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> | ||
| + | |||
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> | ||
Revision as of 10:55, 10 August 2022
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
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
- Head
- Intersect
- Item
- LastPeriods
- Mtd
- PeriodsToDate
- Qtd
- Siblings
- Subset
- Tail
- Union
- Ytd
Member functions
- Ancestor
- Ascendants
- ClosingPeriod
- Cousin
- CurrentMember
- DefaultMember
- Descendants
- FirstLast
- FirstSibling
- Item
- Lag
- LastChild
- LastSibling
- Lead
- Level
- NextMember
- NextSibling
- OpeningPeriod
- ParallelPeriod
- Parent
- PrevMember
- PrevSibling
Aggregation funtions
- Aggregate
- Avg
- Count
- Max
- Min
- Sum
Text functions
- CStr
- Format
- Instr
- LCase
- Left
- Len
- LTrim
- Mid
- Right
- RTrim
- Trim
- UCase
Numeric functions
- Standard operators
- +, -, *, /
- Abs
- CDate
- CDbl
- CInt
- CLng
- Divide
- Mod
- Power
- Round
Logical functions
- IIf
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
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
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 )
Instead reference the measure you want to calculate for and create a set with that member in.
IRR( { [Measures].[My Measure] } * MySet )
