This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release.
Is there a way to reference the value of an account in a different time period? If so, how can this be done?
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.