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:
- References to Attributes in Modeled Accounts follow this format:
This term resolves to the string of the value of the attribute with the split corresponding to the current modeled row. It is only allowed when the attribute name does not contain spaces or other terminator characters.
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
- Rolls up values based on the account type and the Time rollup setting for the account. For example:
this.year-2:this.yearsums three years for a periodic custom account with Time rollup set to sum the rolled-up values. It gives an average for the same account if Time rollup is set to average the rolled-up values. For accounts where the Time rollup setting is fixed, the formula generatea a value based on the fixed setting.
|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 setting for an account must not be Value of account is private . 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])