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

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

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.

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

Returns an integer value.

INT(numericValue)

Parameters

• numericValue - The number input.

Example

INT(21.1);

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

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.

This function is used to link cubes. For example this function would be used in a Travel Planning model to bring a specific Travel Rate into the Travel cube from a Rates cube based on a selected location.

LINKBY(CubeName, RelativeLocation, RelativeValueLocation)

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.

Example

LINKBY("Travel Rates", ["Flight Costs"], ["Destination"])

This will return the value in the Travel Rates cube which is in the corresponding cell based on the specified destination in the formula cube (Travel)

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 Forecast. Evaluate ["Destination"] into a value - for example 'Auckland'. It returns the value at the intersection of["Flight Cost","Auckland","Forecast"] in the Travel Rate cube

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

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

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.

Workview Function - SQRT

Returns the square root of a number.

SQRT(numericValue)

Parameters

• numericValue - The number input

Example

SQRT(16)

Result: 4

See REPLACE.

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

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.

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