Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Reference: Formula Syntax for Account References and Modifiers

Provides detailed syntax for account references and modifiers that you can use in formulas. This includes Account references,  Assumption references, and Column references as well as Time modifiers, Level modifiers, and Dimensions.

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:

ACCT.Account_Code

  • References to Cube and Modeled Accounts follow this format:

ACCT.Sheet_Group.Account_Code

  • Use ACCT.this in 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 this to reference the current level or dimension value.)

Refer to an Assumption

  • References to Global Assumption Accounts follow this format:

ASSUM.Account_Code

Refer to a Modeled Sheet Input Column

  • References to Input Columns & Lookup Tables on a Modeled sheet follow this format:

ROW.Input_Column
ROW.Lookup_Table

Accounts can be modified on time, level, dimension or attribute. Multiple modifiers can also be applied. The following sections describe the available options.

Time Modifiers

Examples:

  • ACCT.Personnel.Headcount[time=this-1]
  • ACCT.Personnel.Salary[time=this.year-1]

Options include:  

  • this (the current period)
  • this+n (where n is the number of future periods)
  • this-(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)
  • Ranges:
    • Two time references separated by a colon
    • Both ends of the range must be of the same granularity
    • For example: this.year-2:this.year would sum three years.
Full Formula Examples Description
IFF(this.month.IsUnder(ToDate(ROW.Year,12,31).Quarter)),
   
ASSUM.LargerQuota,
ASSUM.NormalQuota)
The .ROW expression indicates this formula is in a modeled sheet. It assumes the row of the modeled sheet has a Year column.  If the month is in the quarter of that year which contains Dec 31, it returns a larger quota assumption value, otherwise it returns a normal quota assumption.
IFF(this.Year.NumberOfDays > 365,
   
ASSUM.DailySpreadAmt[time=this-48],
   
ASSUM.DailySpreadAmt[time=this-12])

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 Modifiers

Examples:

  • ACCT.Personnel.Headcount[Level=IT]
  • ACCT.Personnel.Headcount[Level=Corporate(+)]
  • ACCT.Personnel.Headcount[Level=Corporate(-)]

Options include:  

  • [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
IFF(this.Level.Location != this.Level.Parent.Location,
   
ASSUM.RegionalOfficeCharge, 0

This formula returns a RegionalOfficeCharge assumption value if the level has a different Location value (dimension or attribute) than its parent level in the org hierarchy.  This works for cases where the top level at this Location should incur some regional office change, but all descendant levels will share the same Location and don't have to incur that change.

 

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

Examples:

  • ACCT.Personnel.Headcount[Employee_Type=Full Time]
  • ACCT.Personnel.Salary[Employee_Type=Full Time, Level4]

Guidelines/Options:

  • 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])
  • Was this article helpful?