FAQs for Formulas

最後の更新
PDFとして保存

How-to: Annualize data in a formula

You can create an assumption account to represent month (or another time period defined in your calendar) for ratios that require annualizing the numerator. Then, you can create a formula to calculate the projected amount.

Example: To get Projected Annual Income (i.e., ACCT.Proj_Annual_Inc) for the month of May, you need to take Net Income (i.e, ACCT.Net_Inc) for January through May, divide by 5, and then multiply by 12.

To create the assumption account and projection formula:

Create an assumption to represent the month (i.e., ASSUM.Month)
For the ASSUM.Month account, populate each month with a value from 1-12 based on your calendar or fiscal year
- For example, please see the values for each CY-2020 month below.
  - Jan = 1, Feb = 2, Mar = 3, Apr = 4, May = 5, Jun = 6, Jul = 7, Aug = 8, Sep = 9, Oct = 10, Nov = 11, and Dec = 12
Create a formula to calculate Projected Annual Income
- IFF(month(this)=1, DIV(ACCT.Net_Inc, ASSUM.Month)*12, DIV(ACCT.Net_Inc+ACCT.Proj_Annual_Inc[time=this-1], ASSUM.Month)*12)

Topic: Difference between IF and IFF in a formula

IF evaluates both paths of the formula
IFF only evaluates the path of the formula that meets the stated condition

We recommend initially creating a formula with an IF statement to make sure that both parts of the formula are valid by testing both paths. If the formula is taking a long time to calculate and you already validated both paths for the IF statement(s), you can try changing IF to IFF to see if performance improves.

Possible benefits when using IFF:

Improved performance
IFF statements can help avoid circular references in formulas that reference time

Topic: Difference between the division functions

The Div and Divf functions will have the same results; however, formulas that use Divf can calculate faster because the numerator is not evaluated when the denominator is zero. Unlike the Div function, if the denominator is zero, the result of this function is always valid even if the numerator contains an error. Div would report a "Refers to Invalid" error if its numerator is invalid, even if the denominator is zero.

Div(N,D)
Divf(N,D)f the denominator is zero, the numerator is not evaluated
N/D

How-to: Modify a master formula for new versions without changing data in historical versions

The description field for versions via Modeling > Versions can be used to make version specific changes for master formulas. In cases where past versions should remain unaffected and new versions should reflect the update, you can enter a description via Modeling > Versions for all old versions that you do not want to change. Then, you can use formula logic that references the version description. This will prevent formula-driven data from changing in locked versions while allowing you to make update the formula formula for future budgeting needs.

To use the description field to prevent data from changing in old versions:

Go to Modeling > Versions
Enter a description for all old versions that should remain unchanged such as "Historical"
Go to Modeling > Accounts to modify the master formula for the account
Add logic to the formula that references the version description
- For example: IFF(this.version.description = "Historical", [Formula for historical versions], [Formula for new versions])

This can also be used for formulas referencing dimensions and attributes because the elements have a description field.

How-to: Version specific formula changes for calculated accounts

https://knowledge.adaptiveplanning.com/Model_Administration/Modeling_FAQs/Formulas/Version_specific_formula_changes_for_calculated_accounts

How-to: Create a formula that calculates year over year (YOY) growth percent

Example: Calculate YOY growth percent for Expenses

To calculate year over year growth percent for an account:

Create a Custom Account with a formula that references the past 12 months for the GL Account (or other account type)
- Account code: ACCT.ExpensesYOY_Custom
- Account formula: ACCT.Expenses[time=this-12]
Create a Metric Account that calculates YOY growth percent
- Account code: ACCT.ExpensesYOY_Metric
- Account formula: DIV((ACCT.Expenses-ACCT.ExpensesYOY_Custom),ACCT.ExpensesYOY_Custom)

How-to: Create a formula reference to filter an account for more than one dimension

You can create a formula reference to filter an account for multiple dimensions by separating the dimensions by commas in the same bracket.

Example: Filter Revenue by the New and Type dimensions

To filter an account by multiple dimensions:

Use the formula assistant to add the following formula: ACCT.Revenue[New=Yes, Revenue Type=Support]