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

How to Reference the Value of Account in Other Time Periods

This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release. 

Question

Is there a way to reference the value of an account in a different time period? If so, how can this be done?

Answer

Time modifiers can be used in formulas to reference the value of accounts in a different time periods. These modifiers can be manually entered in formulas or selected in the formula assistant from the Time drop-down selector in Term Modifiers area. For example, ACCT.Expenses[Time=this-1] would reference the value of the Expenses Account in the previous period. Below are some of the possible choices in the Time modifier drop-down list.

  • This – References the value of an account in the same month.
  • This+1 – References the value of an account in the next month. The 1 in this modifier can be changed reference any period in the future.
  • This-1 - References the value of an account in the previous month. The 1 in this modifier can be changed reference any period in the past.
  • This.year – References the fiscal year value of an account. A “-1” or “+1” can be added to the syntax to reference the fiscal year total of the previous year or the next year. For example, [time=this.year-1] would reference the previous fiscal year total of an account. The 1 can also be changed to any number.
  • This.qtr – References the value of an account in this quarter. A “-1” or “+1” can be added to the syntax to reference the total of the previous quarter or the next quarter. For example, [time=this.qtr+1] would reference the account’s total in the next quarter. The 1 can also be changed to any number.
  • 01/2017 – References the value of an account in a specific month and year. The month and year of the modifier can be changed.
  • 2017 – References the value of an account in a specific year. The year can be manually adjusted.
  • Q1-2017 – References the value of the first quarter of the year. The quarter number and year can be manually
  • This-12:This-1 – References the total value of an account for a relative range of periods.  The “12” and “1” can be modified to any number.

Any time modifier can include the beginning time period, followed by a colon (:), followed by the ending time period to reference a range of periods. For example, ACCT.Salaries [time=01/2017:06/2017] would reference the total value of the Salaries Account in January through June of 2017. For periodic accounts (e.g. revenues and expenses,) this formula will sum the 6 time periods. For cumulative accounts (e.g. headcount or balance sheet accounts,) it will return the value from the last time period in the range.

formula assistant.jpg

  • Was this article helpful?