 Account calculation functions

For formula accounts, the following functions are available to perform calculations.

ABS()

• ABS(numeric expression)
• Calculates the absolute value of an expression.
• For example, ABS(-100) returns 100.

IF()

• IF(logical expression, numeric expression 1, numeric expression 2)
• Tests the condition and returns the specified numeric expression.
• See the performance note below.

IsAsset()

• IsAsset()
• Tests whether the account being processed is an asset account.
• This function should only be used as the logical expression of an IF() test.

IsEmpty()

• IsEmpty(numeric expression)
• Returns TRUE if the account or numeric expression (formula) evaluates to NULL.

IsExpense()

• IsExpense()
• Tests whether the account being processed is an expense account.
• This function should only be used as the logical expression of an IF() test.

IsLiability()

• IsLiability()
• Tests whether the account being processed is a liability account.
• This function should only be used as the logical expression of an IF() test.

IsRevenue()

• IsRevenue()
• Tests whether the account being processed is a revenue account.
• This function should only be used as the logical expression of an IF() test.

IsStatistical()

• IsStatistical()
• Tests whether the account being processed is a statistical account.
• This function should only be used as the logical expression of an IF() test.

Lag()

• Lag(account, 1)
• Returns the account value for a specified time period (n-1) before the current time period (n).
• For example, if the current value for Account for the month of January is 10, for the month of February the value of Lag(Account,1) is 10.
• For January, this expression returns the value of Account for the month of December of the previous year.

• The Lag() function works along the Time dimension.

  	Dec	Jan	Feb	Mar
  Account	5	10	20	30
  Lag(Account, 1)	-	5	10	20

• See the performance note below.

LagReset()

• LagReset(account, 1)
• Returns 0 for the first period in each fiscal year and returns the account value for a specified time period (n-1) which is before the current time period (n).
• See the performance note below.

Lead()

• Lead(account, 1)
• Returns the account value for a specified time period (n+1) that is after the current time period (n).
• For example, if the current value for Account for the month of February is 20, the value of Lead(Account, 1) for the month of January is 20.

• For February, this expression would return the value of Account for the month of March of the current year.

  	Dec	Jan	Feb	Mar
  Account	5	10	20	30
  Lead(Account, 1)	10	20	30	-

• See the performance note below.

Max()

• Max(account1, account2, ...)

• Calculates the maximum value between 2 (and up to 30) expressions independently for each month.

  	Jan	Feb	Mar
  A	100	-100	0
  B	200	-200	100
  C	300	-300	-100
  Max(A, B, C)	300	-100	100

Min()

• Min(account1, account2, ...)

• Calculates the minimum value between 2 (and up to 30) expressions independently for each month.

  	Jan	Feb	Mar
  A	100	-100	0
  B	200	-200	100
  C	300	-300	-100
  Min(A, B, C)	100	-300	-100

Round()

• Round(numeric expression, number_of_decimal_places)
• Calculates the expression and rounds to the given number of decimal places.
• Using a negative value for the second parameter rounds to powers of 10.

Functions and performance

Certain functions are resource intensive and may cause performance issues when the calculation runs. The following functions may cause slowness:

• Lag(), LagReset(), Lead()
  • These functions are considered recursive because they straddle members of the Time dimension repeatedly.
  • Multiple accounts with the Standard Formula calculation type and using any of these functions may result in extremely slow queries.
• IF()
  • Conditional formulas are expensive because they require logical tests to be performed across all evaluated data points.
  • Depending on the size of the cube, this could take an extremely long time to complete.