Zum Hauptinhalt wechseln
Dokumentation
Dokumentation
Adaptive Planning
Reference: Formula Syntax for Account References and Modifiers

Reference: Formula Syntax for Account References and Modifiers

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.
You can modify general ledger, custom, cube, metric, and assumption accounts with time, level, dimension or attribute modifiers. You can use many modifiers per account reference.
Modifiers are not available for ROW references because the formula automatically uses the data populated in the row as the modifiers.

Refer to an Account

  • References to general ledger, custom, and metric Accounts follow this format:
    ACCT.
    Account_Code
  • References to cube and modeled accounts follow this format:
    ACCT.
    Sheet_Prefix.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 modeled sheet rows follow this format:
  • ROW.
    Input_Column
  • ROW.
    Lookup_Table
  • ROW.
    Attribute_Name
When you use an attribute modifier with a ROW term, it resolves to the string of the attribute value. The split corresponds to the current modeled row. You can only modify with an attribute name that doesn't contain spaces or other terminator characters.

Time Modifiers

  • 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-
    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)
  • Ranges:
    • 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.year
       sums 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.
For simplicity, the examples in the table show formulas using comma separators. Some browser settings require you to use semicolon separators.
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.

Dimensions and Attributes

You can pull data from an account that's tagged with specific dimensions and attributes.
Example: Headcount is an account in the Personnel modeled sheet. Employee Type is a custom dimension. Full Time is 1 of the dimension values for Employee Type. To reference only data tagged with the Full Time dimension value in the Headcount account, enter:  ACCT.Personnel.Headcount[Employee_Type=Full Time]
Guidelines:
  • Append the modifier after ACCT with square brackets [......]
  • Use
    this
     as a dimension value to reference the current dimension value. Example: ACCT.6110[Product=this]) .
  • You can add multiple dimensions or attributes, separated by commas. Example: The formula refers to the Product and Customer dimension:  ACCT.6110[Product=Shirts, Customer=WinterWonderland] .
  • Don't list more than 1 dimension value for the same dimension modifier. When you list more than 1 value, the formula returns only the last 1 listed. Example: This formula only returns data in Account 6110 that's tagged with Sweaters, not Shirts, and not Shirts and Sweaters: ACCT.6110[Product=Shirts, Product=Sweaters]