Software Updates
2021 - March Software Update
New Functions
The following is a list of functions added in the March 2021 update.
Supported from engine version 2.5.188
2021 - July Software Update
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"))
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"])
["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!"
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.
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.
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"