## Cube Functions

Function Listing for Cube Formula and Logic.

#### New Functions

The following is a list of functions added in the March 2021 update.

Supported from engine version 2.5.188

#### New Functions

The following is a list of functions added in the July 2021 update.

Supported from engine version 2.5.466

#### 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.

### String Functions

These functions are used to manipulate text and commentary.

### Numeric Functions

These functions are used to manipulate numbers and perform calculations.

### Financial Functions

These functions are used to evaluate financial logic.

### Conditional Functions

These functions are used to evaluate conditions

### Dimension Functions

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

#### 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.

#### 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

``CEIL(2.4);``

Result: 3.

#### 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);``

#### CHILDCOUNT

Returns the number of child elements in a hierarchy.

``CHILDCOUNT(dimension, hierarchy, element)``

#### Parameters

• Dimension - The dimension in which the selected hierarchy exists
• Hierarchy - The identifier of the hierarchy to search
• Element - The identifier of the parent element

#### Example

``CHILDCOUNT("Account","Management Profit and Loss","EBIT");``

#### COLORLERP

Returns a Linear Interpolation between two colors by a fraction.

``COLORLERP(Fraction, Color1, Color2)``

Parameters

• Fraction - Fraction used for the interpolation (between 1 and 0).
• Color1 - Hex value for the first color.
• Color2 - Hex value for the second color.

Example

``COLORLERP(0.5, "#FF0000", "#00FF00")``

This will return a color 50% between red (#FF0000) and green (#00FF00)

#### COUNTSTATICVALUES

Returns the COUNT of user input values. Ignores calculated values from formulas.

``COUNTSTATICVALUE(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.

Examples

``````COUNTSTATICVALUES("Profit and Loss",["Amount","All Months"])
``````

Will return the Count of user entered values across all months.

#### 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

#### ELEMENTEXISTS

Returns a boolean.

``ELEMENTEXISTS(dimensionName,elementName)``

#### Parameters

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

#### Example

``ELEMENTEXISTS("Time","2015")``

#### 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.

#### 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.

#### 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.

#### HASSTATICVALUE

Returns true if the cell being calculated has a non-formula driven, non-zero value. Ignores calculated values from formulas.

#### Parameters

• This function takes no arguments.

#### Example

``HASSTATICVALUE()``

Assuming the cell being calculated has a user or process entered value this will return true.

#### 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

Accepts any numeric value and returns it as an integer. This is an alias for the function FLOOR.

``INT(numericValue)``

#### Parameters

• numericValue - The number input.

#### Example

``INT(21.7);``

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")``

#### 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.

#### 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

#### LEVELSBETWEEN

Returns the number of steps from the Ancestor specified to the Descendant specified

``LEVELSBETWEEN(DimensionString, AncestorString, DescendantString)``

Parameters

• DimensionString  the dimension to source the sequence from.
• AncestorString - the ancestor to count the amount of steps from.
• DescendantString - the descendant to count the amount of steps to.

Example

``LEVELSBETWEEN("Account", "EBITDA", ELEMENT("Account"))``

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. 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!"

#### MAPPEDELEMENT

Returns the element for the specified dimension within a mapping entry.

``MAPPEDELEMENT(mappingName, dimensionName, mappingDirection)``

#### Parameters

• mappingName - The name of the mapping.
• dimensionName - The name of the dimension for which an element will be returned.
• mappingDirection - The direction "LR" or "RL".

#### Example

``MAPPEDELEMENT("Map Date to Week","Week","LR")``

Assuming the cell being calculated is against the Date element "2020-01-02" this will be the return the associated week from the mapping "2020 - Week 1"

#### 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.

#### 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.

#### 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.

#### 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.

#### NUMBERFORMAT

Returns the provided number formatted based on the given format.

``NUMBERFORMAT(Number, FormatString)``

Parameters

• Number - The number to be processed.
• FormatString - The format to use.

Example

``NUMBERFORMAT(0.912, "0.0%")``

This will return 91.20%

#### 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

#### PARENT

Returns the name of the direct parent element based on the specified dimension, hierarchy, element and index of the parent.

``PARENT(dimensionName,hierarchyName, elementName, value)``

#### Parameters

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

#### Examples

``PARENT("Time","Default", "2019 - Jan", 1)``

Result: "2019 - Q1"

``PARENT("Time","Month YTD", "2019 - Jan", 3)``

Result: "2019 - Mar YTD"

#### PARENTCOUNT

Returns the number of parent elements in a hierarchy.

``PARENTCOUNT(dimension, hierarchy, element)``

#### Parameters

• Dimension - The dimension in which the selected hierarchy exists
• Hierarchy - The identifier of the hierarchy to search
• Element - The identifier of the child element

#### Example

``PARENTCOUNT("Account","Management Profit and Loss","Revenue")``

#### 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.

This function only returns the position of elements at the root of a hierarchy. When used on an n-level element, the function will return -1.

``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.

#### SPLIT

Parses a string using a delimeter and returns a component based on an index (zero based).

``SPLIT(InputString, DelimeterString, IndexNumber)``

Parameters

• InputString - The string to be split.
• DelimeterString - String which the input string will be split based on.
• IndexNumber - Index of the delimeted string to be returned (index is 0 based).

Example

``SPLIT("1|2|3|4|5", "|", 2)``

Will return a string “3” as the index is zero based.

### Workview Function - SQRT

Returns the square root of a number.

``SQRT(numericValue)``

#### Parameters

• numericValue - The number input

#### Example

``SQRT(16)``

Result: 4

See REPLACE.

#### SUMSTATICVALUES

Returns the SUM of user input values. Ignores calculated values from formulas.

``SUMSTATICVALUE(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.

Examples

``````SUMSTATICVALUES("Profit and Loss",["Amount","All Months"])
``````

Will return the SUM of user entered values across all months.

#### 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!"

#### CONSOLIDATEAVERAGE

Returns the average of all child values for a given consolidation intersection. This function only works at a consolidated level.

``CONSOLIDATEAVERAGE(Dimension)``

#### Parameters

• Dimension - The dimension used to average children.

#### Example

``IF( ISLEAF() , CONTINUE , CONSOLIDATEAVERAGE("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 return the average of direct children values.

#### CONSOLIDATEAVERAGENOZEROS

Returns the average of all child values excluding empty or 0 values for a given consolidation intersection. This function only works at a consolidated level.

``CONSOLIDATEAVERAGENOZEROS(Dimension)``

#### Parameters

• Dimension - The dimension used to average children.

#### Example

``IF( ISLEAF() , CONTINUE , CONSOLIDATEAVERAGENOZEROS("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 return the average of direct children values.

#### ELEMENTTYPE

Returns S or N based on the element for the specified dimension for the cell bring calculated

``ELEMENTTYPE(Dimension)``

#### Parameters

• Dimension - The name of the dimension

#### Example

``ELEMENTTYPE("Time")``

Assuming the cell being calculated is against the time element "2018 - January" the return value will be "N"

#### NTHCOUNT

Returns the number of bottom most descendant elements within a specific parent element within a specified dimension hierarchy.

``NTHCOUNT(Dimension, Hierarchy, Element)``

#### Parameters

• Dimension - The name of the dimension
• Hierarchy - The name of the hierarchy
• Element - The name of the parent element

#### Example

``NTHCOUNT("Time","Default","2018")``

This will return 365 assuming the Time dimension has a day level granularity.

#### NUMBER

Returns a numeric value from a provided string. Returns 0 if the string can not be converted into a number.

``NUMBER(NumberString)``

Parameters

• NumberString - A string representation of a number.

Example

``NUMBER("2.5")``

This will return 2.5 as a number.

#### OFFSET

Returns the name of the element at a specified index (numeric position) relative to the specified element in a specified dimension hierarchy

``OFFSET(Dimension, Hierarchy, Element, Offset)``

#### Parameters

• Dimension - The name of the dimension
• Hierarchy - The name of the hierarchy
• Element - The name of the element
• Offset - The relative position to return

#### Example

``OFFSET("Time","Default","2020",1)``

"2021" will be the return value

#### 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"