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

Calculate Formula Values

Question

What does the Calculate Formula Values option in Version Access Control do?

Answer

Use Case

The feature is designed to improve performance in locked versions. Sheets and reports load faster in these versions as most formula evaluations are pre-calculated and saved. The below scenarios describe what would happen to data in versions that have been locked, with Calculate formula values checked (a “frozen” version).

 

Levels

Creating Levels:

  • When a new level is created, while it will exist in the frozen version, it will have no data as shared formulas (and data) do not copy in locked versions. This means data will not change in frozen versions.
  • Level can be made unavailable if so desired from the Manage Your Organization Structure page under Version Availability.

Deleting Levels:

  • When deleting the level, you will receive a warning that data will be deleted in locked versions as well.
    • This WILL delete the data in ALL versions on that level, including frozen versions.
  • The values in formulas that refer to data from said level will not change, but if the version was ever re-calculated or unlocked, it would now return formula errors since the reference is no longer valid.
  • Rollups (parent levels) will change to reflect the deletion of this level.
  • For example:
    • We have a parent level (Corporate) that has two child levels: US and UK.
    • The value in an account called Custom on level UK = 500.
    • We have a formula on level US = ACCT.Custom[level=UK].
      • This formula returns a value of 500.
    • With a value of 500 on level US and 500 on level UK, the parent level (Corporate) is equal to 1000.
    • We delete level UK.
      • The formula on level US will still return a value of 500, as the version was frozen, although level UK no longer exists. If you look at the cell, the formula will show as ACCT.Custom[level=?].
      • The Corporate level, rolling up data from both US and UK, will now return a value of 500 as rollups re-evaluate even in locked versions.
      • If there was also a formula = ACCT.Custom[level=Corporate], it would still return a value of 1000.
      • If the version was ever unlocked, or recalculated, the formula on level US would now return a formula error as level UK no longer exists.

Please see the video recording attached below this article for an example of deleting a level.

 

Accounts

Creating Accounts:

  • New accounts will exist in frozen version, but will have no data
  • If an account is turned into a parent, data will transfer from new parent to child in frozen/locked versions as well. This will not change data.

Deleting Accounts:

  • Accounts will be removed from ALL versions.
  • The values in formulas referring to said accounts will not change, but if the version was ever re-calculated or unlocked, it would now return formula errors since the reference is no longer valid.
  • The rollup (parent account) value will change to reflect the deletion of this account.
  • Formulas that refer to the parent account will not change.
  • For example:
    • We have a parent account (Revenue) that has two child accounts: Hardware and Software.
      • Hardware = 100
      • Software = 200
      • Revenue (parent) = 100+200 = 300
    • We have a fourth account (Custom_Revenue) equal to the Revenue parent account.
      • This returns the value of 300.
    • We delete the Hardware account.
      • Revenue (parent) will now show a value of 200.
      • Custom_Revenue will still be equal to 300 because the calculation is not run again in the frozen version. It has been pre-calculated.
    • If the version was unlocked, formulas would update and Custom_Revenue would re-evaluate to 200.

Metric Accounts:

  • Freezing a version will have no effect on metric accounts.
    • If a metric account’s formula is changed, or an account/level referenced in a metric is deleted, the value calculating in that metric account in frozen versions will change as well.
  • Standard (Custom or GL) accounts that refer to a metric account, once that metric account is changed, will retain the pre-calculated value of the metric.

Calculated (Modeled) Accounts:

  • Freezing a version will have no effect on modeled accounts.

Cube Calculation Accounts:

  • Updating the formulafor a cube calculation account will not change values in a frozen version.
  • Updating the data referenced in a cube calculation account’s formula (such as deleting an account or dimension value that contributes to the value) will not change values in a frozen version.
    • If the resulting change results in a formula error for the Calculation account, this formula error will show in the frozen version. This is because a syntax check will be run for the account globally. If the syntax error is removed, the old value will return.

Account Examples:

Scenario 1: Deleting a Referenced Account (Custom):
  • custom account = ACCT.Example = 100
  • We delete ACCT.Example
  • Result: Custom account still equals 100
Scenario 2: Deleting a Referenced Account (Metric)
  • metric (or cube metric) account = ACCT.Example = 100
  • We delete ACCT.Example
  • Result: Metric account now shows a formula error
Scenario 3: Deleting a Referenced Account (Cube Calculation)
  • cube calculation account = ACCT.Example (let’s pretend we don’t need isblank() logic here) = 100
  • We delete ACCT.Example, turning the formula into ACCT.?
  • Result: The cube calculation account returns a formula error and its syntax must be corrected for the value to return to 100
Scenario 4: Updating a Formula (Cube Calculation)
  • cube calculation account = ACCT.Example = 100
  • We update the formula to ACCT.Example+1000
  • Result: The cube calculation account still equals 100, even after the formula update
Scenario 5: Deleting a Contributing Value to a Referenced Rollup (Cube Calculation)
  • cube calculation account = ACCT.Parent = 1000
    • The parent has two children: Child1 (=600) and Child2 (=400)
    • We delete one of the children of ACCT.Parent (Child1), changing ACCT.Parent’s value to 400
    • Result: The cube calculation account still equals 1000

 

Dimensions

Creating Dimension Values:

  • If the version was frozen/locked when a List dimension was first created, its values will automatically not be available in the frozen version.
  • While non-list dimension values will appear in frozen versions, this should not impact the data.

Deleting Dimension Values:

  • As it would not be possible to delete an in-use dimension value, this should not be a concern.
  • Removing the value from a sheet, rather than deleting the value entirely, will impact rolled up data, but will not change formulas that referenced or utilized said value.

 

Version/Actuals Changes

Updating Actuals Values Used in a Plan Version:

  • The actuals value that displays in the frozen version (overlayed actuals) will change.
  • Any formulas that refer to said actuals values (references to prior periods) will not change.

Please see the second video recording attached below this article for an example.

Updating the Start of Plan:

  • Updating the Start of Plan for a frozen version forward (Jan 2015 to March 2015) will have no impact on the version or data in any way. The overlayed actuals will not change and will not shift forward.
    • This is the same behavior of what occurs in a locked version.
  • Updating the Start of Plan for a frozen version backward (March 2015 to Jan 2015) will shift the overlayed actuals back in the version, but will not change the values in formulas that reference the now changed actuals.
  • Was this article helpful?