This article includes suggestions and workarounds. Content may not be accurate for all use cases or represent best practices for the latest release.
Is it possible to make Version specific changes to formulas in my Modeled Sheet Calculated Accounts?
Please also see the Dot Notation documentation that can be used to make formulas version specific.
There would be two options in this case. The first is using dot notation to reference a specific version name or description. This is detailed above. This would be recommended over a sheet assumption. You could build a formula such as if(search(this.version.description,"New")>0,New_Formula,Old_Formula), which looks at the description of a version to see if it contains the word "New", in which case the new formula will be used, else in historical versions without "New" in the description, the old formula will still be used.
Option two is using an assumption. Sheet assumptions are version specific and will allow you to make the formula change version specific if you use the process outlined below. The first step will be to add the sheet assumption to act as a trigger and the second step will be to modify the calculated account formula to reference this formula. In this example, I will use the formula below as the current formula:
And the formula below as the new formula:
To add a sheet assumption, please take the following steps:
- Go to Modeling > Level Assigned Sheets. Select the appropriate sheet. In this example, I selected the Personnel sheet.
- In the bottom left, click the sheet assumptions tab.
- Click the "+" sign to add a new sheet assumption. For the purpose of this example, lets call the sheet assumption Trigger.
- Populate the new sheet assumption with 1's for the time periods of the version you would like the new formula to calculate.
- If the Left Scroll Limit of the version is not the same as the start of plan, and the formula you are modifying is for an initial balance element (pay rate, for example), it is important to enter the trigger in all time periods available to the version. To do so, click the show and hide option and select all months as the view able time range. Set the trigger to one for all time periods.
Once the sheet assumption has been successfully added, save the sheet. You will then go back into the sheet builder and modify the actual formula.
The current formula is:
To modify the formula, you will want to add the syntax:
If(Assum.Personnel.Trigger=1, New Formula Syntax, Old Formula Syntax)
This can be translated as if the Trigger you created has a 1 populated for a time period, use the new formula. If it does not have a 1, use the old formula. Since the sheet assumption trigger will be version specific, the new formula will only occur in the versions that you populate a 1 for.
The final formula might look something like:
If(Assum.Personnel.Trigger=1, Row.Salary+(Row.Salary*Row.Commissions*Row.Adjustment), ROW.Salary+(ROW.Salary*ROW.Commissions))
- This new formula will be version specific, and will only be calculated in the versions that have a 1 populated for the trigger sheet assumption.
For additional information, please see this article: Version Specific vs. Structural Changes