Cube Functions

Function Listing for Cube Formula and Logic.

Worksheet Function Listing

Workview Functions are used when developing workviews specifically when using the formula editor. These formulas are applied to the cube underneath the workview which means that business logic only needs to be developed once and the results of this logic can then be reported using any dimensionality.

Common Use Functions

The following is a list of the most commonly used functions.

  • ELEMENT
  • LINK
  • LINKBY
  • SEQUENCE
  • POSITION

String Functions

These functions are used to manipulate text and commentary.

  • TRIM
  • LEFT
  • RIGHT
  • MID
  • LEN
  • REPLACE
  • SUBSTITUTE (alias)
  • UPPER
  • LOWER

Numeric Functions

These functions are used to manipulate numbers and perform calculations.

  • FLOOR
  • SQRT
  • ABS
  • ROUND
  • INT
  • MIN
  • MAX
  • AVERAGE
  • AVERAGENOZEROS
  • MODE
  • MEDIAN

Financial Functions

These functions are used to evaluate financial logic.

  • FV
  • IPMT
  • NPER
  • PMT
  • PPMT
  • PV
  • RATE

Conditional Functions

These functions are used to evaluate conditions

  • IF
  • AND
  • OR

Dimension Functions

The following is a list of the Functions used to reference dimensions in a formula.

  • ELEMENT
  • POSITION
  • ISLEVELZERO
  • CHILD

ABS

Returns a positive number regardless of the sign of the original number.

ABS(numericValue)

Parameters

  • numericValue - The number input.

Example

ABS(-21);

Result: 21

ALIAS

Returns the element for the specified dimension for the cell bring calculated.

ALIAS(dimension, alias, element)

Parameters

  • Dimension - The name of the dimension
  • Alias - The name of the alias
  • Element - The name of the element

Example

ALIAS("Time", "Name", "2018 - 01");

Assuming a calendar year, the time element "2018 - 01" would likely have an alias of "2018 - January" this will be the return value.

AND

Returns 1 if both the conditions are evaluated to true.

AND(condition1, condition2)

Parameters

  • condition1 - A condition which has to be evaluated
  • condition2 - A condition which has to be evaluated

Example

AND(1>0, 2 < 3)

Result: 1

AVERAGE

Returns the average value from a range of inputs

AVERAGE(Input1, Input2, ...InputN)

Parameters

  • Input1 ... InputN - Inputs to be evaluated

Example

AVERAGE(["Measure A"], ["Measure B"], ["Measure C"], ["Measure D"])

If the value held at "Measure A" is equal to 1 and the value held at "Measure B" and "Measure C" are equal to 3 and "Measure D" is equal to 8 then the function will return 3.75.

AVERAGENOZEROS

Returns the average value from a range of inputs ignoring zeroes.

AVERAGENOZEROS(Input1, Input2, ...InputN)

Parameters

  • Input1 ... InputN - Inputs to be evaluated

Example

AVERAGENOZEROS(["Measure A"], ["Measure B"], ["Measure C"], ["Measure D"])

If the value held at "Measure A" is equal to 1 and the value held at "Measure B" and "Measure C" are equal to 3 and "Measure D" is equal to 0 then the function will return 2.33333333333333.

CHILD

Returns the name of the child element based on the specified dimension, hierarchy, element and position of the child.

CHILD(dimensionName,hierarchyName, elementName, value)

Parameters

  • dimensionName - The dimension.
  • hierarchyName - The hierarchy.
  • elementName - The element.
  • value - Index of the child. Must be numeric.

Example

CHILD("Time","MONTH", "2015", 2);

ELEMENT

Returns the element from the dimension specified in the cube cell intersection which is presently being evaluated by the rules engine.

ELEMENT(dimensionName)

Parameters

  • dimensionName - The dimension.

Example

ELEMENT("Time");

If the user is refreshing a workview which is only looking at "2015 - Jan" the result will be "2015 - Jan".

ELEMENTBYPOSITION

Returns a boolean.

ELEMENTBYPOSITION(dimension,hierarchy,position)

Parameters

  • dimension - The name of the dimension
  • hierarchy - The name of the hierarchy
  • position - The position to return

Example

ELEMENTBYPOSITION("Time","Default",2);

If the second element in the specified hierarchy is “2018” this will be the return value

ELEMENTHASANCESTOR

Returns a boolean.

ELEMENTHASANCESTOR(dimensionName,ancestorName)

Parameters

  • dimensionName - The dimension.
  • ancestorName - The ancestor element name (note. you can prefex with the hierarchy).

Example

ELEMENTHASANCESTOR("Time","Default (Calendar)»2015");

If the user is refreshing a workview which is only looking at "2015 - Jan" the result will be 1.

FLOOR

Removes decimal places without rounding.

FLOOR(numericValue)

Parameters

  • numericValue - The number to be converted into an Integer.

Example

FLOOR(2.6);

Result: 2.

FV

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

FV(rate,nper,pmt,pv,type)

Parameters

  • rate - The interest rate per period.
  • nper - The total number of payment periods in an annuity.
  • pmt - The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
  • pv - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
  • type - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

IF

Returns 1 if both the conditions are evaluated to true or if the cell being calculated has no elements which have children.

IF(condition1, resultA, resultB)

Parameters

  • condition1 - A condition which has to be evaluated
  • resultA - Result if the condition evaluates to  1
  • resultB - Result if the condition evaluates to anything other than 1

Example

IF(["Measure A"] = 1, ["Measure B"], ["Measure C"])
//If the value held at "Measure A" is equal to 1 then the function will return the value held at "Measure B"

//Assuming ["Measure A"] is 1, ["Measure B"] is 3, ["Measure C"] is 8, then
IF(["Measure A"] = 1, ["Measure B"], ["Measure C"]) // evaluates to
IF(1 = 1, 3, 8)// and returns 3

Note: Other conditional functions can be used inside of IF function

Result: 1

INT

Returns an integer value.

INT(numericValue)

Parameters

  • numericValue - The number input.

Example

INT(21.1);

Result: 21

IPMT

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

IPMT(rate, per, nper, pv, fv, type)

Parameters

  • rate - The interest rate per period.
  • per - Specifies the period
  • nper - Total number of payment periods in an annuity.
  • pv - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
  • fv - The future value of an investment
  • type - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

ISLEAF

Returns 1 if the cell being calculated has no elements which have children

ISLEVELZERO

Returns 1 if the element of the specified dimension at the calculated cell intersection doesn't have children.

ISLEVELZERO(dimensionName)

Parameters

  • dimensionName - The dimension to find the element in.

Example

ISLEVELZERO("Time")

LEFT

Returns a specific portion of a string from a given string, starting at the start of the string and containing the specified number of letters.

LEFT(SourceString, Length)

Parameters

  • SourceString - Specifies the string to be used as the source.
  • Length - Specifies the number of characters you want to return from the start position.

Example

LEFT("Hello world!",5)

Extract the first 5 characters from the text "Hello world!".
Result: "Hello"

LEN

Returns the length of a given string.

LEN(SourceString) 

Parameters

  • SourceString - Specifies the string to count the length of.

Example

LEN("Hello world!")

Count the length of the string: "Hello world!".
Result: 12

LINK

Create a flow of information into the affected cells from the specified cubes.

This function is used to link cubes. For example this function would be used in a Profit and Loss model to bring the Operating Expenditure from other cubes such as Travel, Salary and Wages, Capital Expenditure and Sales or Revenue Planning.

LINK(CubeName, RelativeLocation)

Parameters

  • CubeName - the cube to source information from.
  • RelativeLocation - the relative location in the target cube. The relative location needs to specify each of the elements of dimensions which exist in the source cube which do not exist in the destination cube.

Example

LINK("Travel", ["All Travel","Total Expenditure"])
This will return the value in the Travel cube which is in the corresponding cell.
Assuming the cube with the formula is being applied to has only a Scenario dimension in common the target cube and the evaluated cell has a Scenario of Actual. It returns the value at the intersection of ["All Travel","Total Expenditure","Actual"] in the Travel cube.
 

LINKBY

Create a flow of information into the affected cells from the specified cubes using on a specified relative cell value as an element (on a dimension) in the target cube.

LINKBY(CubeName, RelativeLocation, ValueLocation)

Parameters

  • CubeName - the cube to source informaiton from
  • RelativeLocation - the relative location in the target cube. The relative location needs to specify each of the elements of dimensions which exist in the source cube which do not exist in the destination cube.
  • ValueLocation - a linkdim or mapping value.

This function can be used in two scenarios:

Scenario A: When a part of the information needed to reference the target cell is held in the data of the current cube against a string-type measure:

LINKBY("SourceCubeName", ["Element 1", "Element N"], LINKDIM("Dimension 3", ["Element 3"]))

Scenario B: When linking to another cube via a mapping object. Mapping objects are used when the relationship between two cells is not easily supported by other referencing options. (See also MAPPING):

LINKBY("SourceCubeName", ["Element 1", "Element N"], MAPPING("Mapping Name", "LR"))

LOWER

Returns the lowercase value of the provided string.

LOWER(string)

Parameters

  • String - Specifies the string that should be converted.

Example

LOWER("HELLO WORLD!")

Result: "hello world!"

MAPPING

This function is used in conjunction with the LINKBY function to assist in traversing between cubes with uncommon dimensionality or within a single cube where required by business logic.

MAPPING(MappingName, MappingDirection)

Parameters

  • MappingName - the Mapping object name.
  • MappingDirection - Either "LR" or "RL" which dictates the direction we are mapping from and to respectivly.

Example

Cube - Detailed Cube - Summary
Year Year
Month Month
Scenario Scenario
Account Account Summary
Measures ("Amount") Measures ("Amount")

Where the Account Summary dimension is a subset of the Account dimension which excludes the N level accounts from the Account dimension. This has been loaded into a Mapping object which maps these two dimensions. 

  • Mapping: "Account to Account Summary".
  • Left Dimension: Account
  • Right Dimension: Account Summary

The following rule can exist in the Summary cube to pull data from the Detailed cube for the "Amount" measure.

LINKBY("Detailed",["Amount"],MAPPING("Account to Account Summary","RL"))

This function will return the value stored at the equivalent account in the detailed cube against the amount measure.

MAX

Returns the maximum value from a range of inputs

MAX(Input1, Input2, ...InputN)

Parameters

  • Input1 ... InputN - Inputs to be evaluated

Example

MAX(["Measure A"], ["Measure B"])

If the value held at "Measure A" is equal to 1 and the value held at "Measure B" is equal to 2 then the function will return 2.

MID

Returns a specific portion of a string from a given string, starting at the position you specify, based on the number of characters you specify.

MID(SourceString, StartPosition, Length)

Parameters

  • SourceString - Specifies the string to be used as the source.
  • StartPosition - The position of the first character you want to extract in text. The first character in text has start_num 0, and so on.
  • Length - Specifies the number of characters you want to return from the start position.

Example

MID("Hello world!",1,5)

Extract the first 5 characters from the text "Hello world!".
Result: "Hello"

MIN

Returns the minimum value from a range of inputs

MIN(Input1, Input2, ...InputN)

Parameters

  • Input1 ... InputN - Inputs to be evaluated

Example

MIN(["Measure A"], ["Measure B"])

If the value held at "Measure A" is equal to 1 and the value held at "Measure B" is equal to 2 then the function will return 1.

MEDIAN

Returns the median value from a range of inputs. The median is if the values were listed in order, it will be the middle value if the number of values is odd, or the average of the 2 middle numbers if the number of values is even. 

MEDIAN(Input1, Input2, ...InputN)

Parameters

  • Input1 ... InputN - Inputs to be evaluated

Example

MEDIAN(["Measure A"], ["Measure B"], ["Measure C"], ["Measure D"])

If the values held at "Measure A", "Measure B","Measure C", "Measure D", are 1, 2, 3, and 8 respectively, then the function will return 2.5.

MODE

Returns the most frequent occuring or repetitive value from a range of inputs.

MODE(Input1, Input2, ...InputN)

Parameters

  • Input1 ... InputN - Inputs to be evaluated

Example

MODE(["Measure A"], ["Measure B"], ["Measure C"], ["Measure D"])

If the value held at "Measure A" is equal to 1 and the value held at "Measure B" and "Measure C" are equal to 3 and "Measure D" is equal to 8 then the function will return 3.

NPER

Returns the total number of payment periods in an annuity based on periodic, constant payments and future value.

NPER(rate, pmt, pv, fv, type)

Parameters

  • rate - The interest rate per period.
  • pmt - The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
  • pv - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
  • fv - The future value of an investment
  • type - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

OR

Returns 1 if either of the conditions are evaluated to true.

OR(condition1, condition2)

Parameters

  • condition1 - A condition which has to be evaluated
  • condition2 - A condition which has to be evaluated

Example

OR(1>0, 5 < 3)

Result: 1

PMT

Returns the payment made each period; it cannot change over the life of the annuity.

PMT(rate, nper, pv, fv, type)

Parameters

  • rate - The interest rate per period.
  • nper - Total number of payment periods in an annuity.
  • pv - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
  • fv - The future value of an investment
  • type - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

POSITION

Returns a number which represents the specified elements position in a specified hierarchy.

POSITION(dimensionName, hierarchyName, elementName)

Parameters

  • dimensionName - The dimension to find the element in.
  • hierarchyName - The hierarchy to find the element in.
  • elementName - The element to locate and return the position of.

Example

POSITION("Time","FY2015","2015 - Jun");

If the hierarchy is comprised only of the months in calendar order, this function will return 6.

POW

Returns the input number raised to the power of the exponent provided

POW(input, exponent)

Parameters

  • input - Specifies the base number
  • exponent - The exponent for which the base is to be raised to.
POW(2,4)

Result: 16

PPMT

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

PPMT(rate, per, nper, pv, fv, type)

Parameters

  • rate - The interest rate per period.
  • per- Specifies the period
  • nper - Total number of payment periods in an annuity.
  • pv - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
  • fv - The future value of an investment
  • type - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

PV

Returns the present value of an investment based on periodic, constant payments and a constant interest rate.

PV(rate, nper, pmt)

Parameters

  • rate - The interest rate per period.
  • nper - The total number of payment periods in an annuity.
  • pmt - The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

RATE

Returns the interest rate per period based on periodic, constant payments and future value.

RATE(nper, pmt, pv, fv, type)

Parameters

  • nper - The total number of payment periods in an annuity.
  • pmt - The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
  • pv - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
  • fv - The future value of an investment
  • type - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

REPLACE

Replace all occurrences of the search string with the replacement string.

REPLACE(sourceString, searchString, replacementString)

Parameters

  • sourceString - Specifies the string to be searched.
  • searchString - Specifies the value to find.
  • replacementString - Specifies the value to use as a replacement.

Example

REPLACE("Hello world!","world","Peter")

Replace the characters "world" in the string "Hello world!" with "Peter".
Result: "Hello Peter!"

RIGHT

Returns the last portion of a string from a given string and a specified start character position.

RIGHT(SourceString, StartingPlace)

Parameters

  • SourceString - Specifies the string to be used as the source.
  • StartingPlace - Specifies the starting place for the substring.

Example

RIGHT("Hello world!",6)

Extract the last 6 characters from the text "Hello world!".
Result: "world!"

ROUND

Rounds a number to a specified number of decimal places.

ROUND(numericValue, precision)

Parameters

  • numericValue - The number to be rounded.
  • precision - The number of decimal places to round to.

Example

ROUND(2.6645,1);

Result: 2.7

SEARCH

Returns a the position of the needle argument in the haystack string argument. Returns -1 if the needle is not found in the haystack. The result is a zero based position.

SEARCH(haystack, needle, startPosition)

Parameters

  • haystack - String to search
  • needle - String to find in the haystack
  • startPosition - The position to begin the search (zero based)
SEARCH("Hello Business Users","Users",0)"

Result: 15

SEQUENCE

This function is typically used in rolling balance calculations. For example, you might have a measures dimension tracking the number of subscribed customers which, in any given period, has an Opening Balance, New Subscriptions, and Churning Subscriptions, all of which added together result in a Closing Balance. You can use the SEQUENCE function on the Opening Balance element to make it equal to the prior periods Closing Balance.

SEQUENCE(Dimension, Hierarchy, SequenceShift, RelativeLocation)

Parameters

  • Dimension - the dimension to source the sequence from.
  • Hierarchy - the hierarchy in the specified dimension to source the sequence from.
  • Sequence Shift - a numeric value denoting how many items to move (shift) in the sequency. (for rolling monthly balances this may be -1)
  • RelativeLocation - the relative location in the resulting location.

Example (for Opening Balance)

SEQUENCE("Time", "Month List", -1, ["Closing Balance"])

This will return the value from the same cube from the prior month element.

SQRT

Workview Function - SQRT

Returns the square root of a number.

SQRT(numericValue)

Parameters

  • numericValue - The number input

Example

SQRT(16)

Result: 4

SUBSTITUTE

See REPLACE.

TRIM

Removes proceeding and trailing spaces in a String

TRIM(SourceString)

Parameters

  • SourceString - Specifies the string to be used as the source.

Example

TRIM(" Hello ")

Result: "Hello"

UPPER

Returns the uppercase value of the provided string.

UPPER(string)

Parameters

  • String - Specifies the string that should be converted.

Example

UPPER("Hello world!")

Result: "HELLO WORLD!"

CEIL

Returns the smallest (closest to negative infinity) numeric value that is greater than or equal to the argument and has no decimal places

CEIL(numericValue)

Parameters

  • numericValue - The number to be converted into an Integer.

Example

FLOOR(2.6);

Result: 3.

ELEMENTEXISTS

Returns a boolean.

ELEMENTEXISTS(dimensionName,elementName)

Parameters

  • dimensionName - The dimension.
  • elementName - The element name.

Example

ELEMENTEXISTS("Time","2015")

FIRSTDESCENDANTVALUE

Returns the first child value for a given consolidation intersection. This function only works at a consolidated level.

FIRSTDESCENDANTVALUE(dimensionName)

Parameters

  • dimensionName - The dimension used to identify the first descendant value

Example

IF( ISLEAF() , CONTINUE , FIRSTDESCENDANTVALUE("Time") )

If this is calculating a N-Level value it will return said value. If this is calculating a consolidation on the Time dimension it will only return the first descendants value.
If you were looking at "2020", this would return the value for "2020 - Jan"

 

FIRSTPOPULATEDDESCENDANTVALUE

Returns the first child with a value for a given consolidation intersection. This function only works at a consolidated level.

FIRSTPOPULATEDDESCENDANTVALUE(dimensionName)

Parameters

  • dimensionName - The dimension used to identify the first descendant value

Example

IF( ISLEAF() , CONTINUE , FIRSTPOPULATEDDESCENDANTVALUE("Time") )

If this is calculating a N-Level value it will return said value. If this is calculating a consolidation on the Time dimension it will only return the first descendants value.
If you were looking at "2020", this would return the value for "2020 - Feb", if "2020 - Jan" was empty.

LASTDESCENDANTVALUE

Returns the last child value for a given consolidation intersection. This function only works at a consolidated level.

LASTDESCENDANTVALUE(dimensionName)

Parameters

  • dimensionName - The dimension used to identify the last descendant value

Example

IF( ISLEAF() , CONTINUE , LASTDESCENDANTVALUE("Time") )

If this is calculating a N-Level value it will return said value. If this is calculating a consolidation on the Time dimension it will only return the last descendants value.
If you were looking at "2020", this would return the value for "2020 - Dec"

LASTPOPULATEDDESCENDANTVALUE

Returns the last child with a value for a given consolidation intersection. This function only works at a consolidated level.

LASTPOPULATEDDESCENDANTVALUE(dimensionName)

Parameters

  • dimensionName - The dimension used to identify the last descendant value

Example

IF( ISLEAF() , CONTINUE , LASTPOPULATEDDESCENDANTVALUE("Time") )

If this is calculating a N-Level value it will return said value. If this is calculating a consolidation on the Time dimension it will only return the last descendants value.
If you were looking at "2020", this would return the value for "2020 - Nov", if "2020 - Dec" was empty.

PARENT

Returns the name of the parent element based on the specified dimension, hierarchy, element and position of the parent.(1 being direct parent, 2 being parent of parent, etc)

PARENT(dimensionName,hierarchyName, elementName, value)

Parameters

  • dimensionName - The dimension.
  • hierarchyName - The hierarchy.
  • elementName - The element.
  • value - Index of the parent. Must be numeric.

Example

PARENT("Time","MONTH", "2019 - Jan", 1)

VARIABLE

Returns the value of the specified variable.

VARIABLE(variable_name)

Parameters

  • variable_name - The variable for which the value has to be retrieved.

Example

VARIABLE("PRIMARY-COLOR");

Result of color variable: "Blue"