Anaplan XL MDX Calculation support

For some data sources that don't natively support MDX, Anaplan XL can execute calculations on top of the source database.

This is available for Anaplan connection types.

Documentation for these functions can be found in the MDX function reference.

These are the supported 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

• Ancestor
• Ascendants
• ClosingPeriod
• Cousin
• CurrentMember
• DefaultMember
• Descendants
• FirstLast
• FirstSibling
• Is (keyword)
• Item
• Lag
• LastChild
• LastSibling
• Lead
• Level
• NextMember
• NextSibling
• OpeningPeriod
• ParallelPeriod
• Parent
• PrevMember
• PrevSibling

• Aggregate
• Avg
• Count
• Max
• Min
• Median
• Sum

• CoalesceEmpty
• CStr
• Format
• Instr
• LCase
• Left
• Len
• LTrim
• Mid
• Right
• RTrim
• Trim
• UCase

• Standard operators +, -, *, /
• Abs
• CDate
• CDbl
• CInt
• CoalesceEmpty
• Divide
• Log
• Log10
• Mod
• Power
• Round
• Sqrt

• CBool
• IIf
• IsAncestor
• IsEmpty
• IsLeaf
• IsSibling
• Hierarchy functions
• Levels
• Members

• Members
• Ordinal

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

XL3TagToMember(hierarchy, tag)

For example XL3TagToMember([Account], "#NetIncome")

XL3TagToSet(hierarchy, tag)

For example: XL3TagToSet([Account], "#MyFlag")

These templates may be useful starting points for your own calculations. You should replace the Dimension, Hierarchy, Level, and Measure names with your own.

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, this calculation gets the fourth child of the current Date member. As Children returns a set, 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) )

These functions accept a list of values as an argument, not a set of tuples, and a value expression like Sum(), Avg() and so on.

For these to work, specify the member of measures to use so that the calculation doesn't reference itself.

This evaluates the values for "My Set" in the context of the IRR measure, which can't work.

IRR( MySet ) is incorrect.

Instead, reference the measure you want to calculate for and create a set with that member.

IRR( { [Measures].[My Measure] } * MySet )