This topic describes syntax rules and options that you can use for account references and modifiers in formulas. Account references are prepended with ACCT, ASSUM, or ROW.
Refer to an Account
- References to GL, Custom, and Metric Accounts follow this format:
- References to Cube and Modeled Accounts follow this format:
ACCT.thisin a formula to refer to the current account. This allows you to write the same formula in multiple accounts if the formula needs to refer to the current account. This makes it much easier to copy formulas between large blocks of accounts. (You can also use
thisto reference the current level or dimension value.)
Refer to an Assumption
- References to Global Assumption Accounts follow this format:
Refer to a Modeled Sheet Input Column
- References to Input Columns & Lookup Tables on a Modeled sheet follow this format:
Accounts can be modified on time, level, dimension or attribute. Multiple modifiers can also be applied. The following sections describe the available options.
this(the current period)
this+n(where n is the number of future periods)
this-n(where n is the number of prior periods)
this.year(the year referenced from the current period)
this.qtr(the quarter referenced from the current period)
this.year-1(the prior year from the current period)
- Two time references separated by a colon
- Both ends of the range must be of the same granularity
- For example:
this.year-2:this.yearwould sum three years.
|Full Formula Examples||Description|
This formula detects if this is a leap year, and if so, it uses some assumption from four years ago (the last leap year), otherwise it uses the value from the prior year.
[Level=this](includes the active level)
[Level=this(+)](includes all children from the active level, rollup is selected)
[Level=<level-name>(+)](includes all children from the specified level, implied in rollup levels)
[Level=<level-name>(-)](only includes the specified level, no children)
|Full Formula Example||Description|
This formula returns a
For formulas that work across organization levels, the data privacy for an account must be public. Otherwise, the account data will not be included.
Dimensions and Attributes
ACCT.Personnel.Salary[Employee_Type=Full Time, Level4]
- Append the formula after ACCT with square brackets [......]
- You can add multiple dimensions (separated by commas). For example:
ACCT.6110_Salary_Wages [Product=Value 1, Customer=Value1]
- Use this as a dimension value to reference the current dimension value. For example:
if (isblank (ACCT.ProdSalesCube.PriceAssumLink),0,ACCT.SalesAssum.Price_per_Unit[Product=this])