Difference between revisions of "XLCubed MDX Calculation Support"

(Financial functions)
Line 74: Line 74:
 
* Power
 
* Power
  
====Financial functions====
+
===Logical functions===
 +
* IIf
 +
 
 +
===Hierarchy functions===
 +
* Levels
 +
* Members
 +
 
 +
===Level functions===
 +
* Members
 +
* Ordinal
 +
 
 +
===Financial functions===
  
 
Information on these functions can be found in the [https://docs.microsoft.com/en-us/office/vba/language/reference/functions-visual-basic-for-applications VBA function reference]
 
Information on these functions can be found in the [https://docs.microsoft.com/en-us/office/vba/language/reference/functions-visual-basic-for-applications VBA function reference]
Line 88: Line 99:
 
* SLN
 
* SLN
 
* SYD
 
* SYD
 
===Logical functions===
 
* IIf
 
 
===Hierarchy functions===
 
* Levels
 
* Members
 
 
===Level functions===
 
* Members
 
* Ordinal
 
  
 
==Sample Calculations==
 
==Sample Calculations==

Revision as of 09:07, 26 July 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.

Supported Functions

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

Set functions

  • { } - braces
    • Multiple members/tuples can be combined
    • Range operator  : is supported [1]
  • Ancestors
  • Children
  • Head
  • Item
  • LastPeriods
  • PeriodsToDate
  • Siblings
  • Subset
  • Tail

Member functions

  • Ancestor
  • ClosingPeriod
  • Cousin
  • CurrentMember
  • DefaultMember
  • Descendants
  • FirstMember
  • FirstSibling
  • Item
  • Lag
  • LastMember
  • LastSibling
  • Lead
  • Level
  • NextMember
  • OpeningPeriod
  • ParallelPeriod
  • Parent
  • PrevMember

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
  • CDbl
  • CInt
  • Divide
  • Mod
  • Power

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
  • IPmt
  • NPer
  • 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