New Functions

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

• MAPPEDELEMENT
• NTHCOUNT

Supported from engine version 2.5.188

New Functions

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

• COLORLERP
• COUNTSTATICVALUES
• HASSTATICVALUE
• LEVELSBETWEEN
• NUMBERFORMAT
• SPLIT
• SUMSTATICVALUES

Supported from engine version 2.5.466

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
• CEIL
• 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

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

ABS(numericValue)

Parameters

• numericValue - The number input.

Example

ABS(-21);

Result: 21

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.

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

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.

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.

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.

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

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

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)

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.

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

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

Returns a boolean.

ELEMENTEXISTS(dimensionName,elementName)

Parameters

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

Example

ELEMENTEXISTS("Time","2015")

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.

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"

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.

Removes decimal places without rounding.

FLOOR(numericValue)

Parameters

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

Example

FLOOR(2.6);

Result: 2

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.

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.

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

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

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.

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

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

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"

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.

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"

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

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

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

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

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"

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

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.

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.

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.

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"

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.

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.

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.

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%

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

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"

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

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.

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.

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

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.

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.

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

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

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

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

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.

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.

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.

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"

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

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.

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.

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"

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.

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.

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

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"