Some files in a Filebox contain Datarails formulas or tables that pull from your data. When the source data changes, those files need to be recalculated to stay current. Calculating creates a new version of the file with refreshed values, so you don't have to open it, refresh it and upload it again.
There are two ways to do it:
- Calculate creates a new version on demand, whenever you choose.
- Auto Calculate creates new versions for you, when a source file changes or on a schedule.
When to use it
| Situation | How calculating helps |
|---|---|
| A financial model that depends on an assumptions file | Turn on Auto Calculate for the model and the assumptions Filebox. Each time the assumptions change, the model is recalculated, so you can produce a scenario for each set of assumptions. |
| A monthly report that pulls from several sources | Give the report a Month tag and turn on Auto Calculate. A new version is created each month with the right data, with no manual work. |
| A rolling forecast | Use Roll period to move the forecast forward one period, using the last version as the starting point. |
Before you start
- You can't calculate a version for a period that is locked, or while the whole Filebox is locked. Unlock it first. Review Managing Fileboxes for more information on unlocking a Filebox.
- Auto Calculate is available for Manual input, Model and Report Fileboxes. See Filebox Types.
- Two Fileboxes with Auto Calculate turned on can't reference each other. That would create a circular reference.
Calculate a new version
Choose how to roll the period
When you calculate, Roll period decides which period the new version belongs to.
| Roll period | What it does | Example |
|---|---|---|
| None | Recalculates the file and saves it under the Date tag you choose. The dates in the file don't move. | Take the latest version and create a refreshed version for June. |
| Forward | Uses the version as a template for the next period. The new version is saved under the next Date tag. | Roll a 1+11 forecast forward to produce the 2+10 forecast. |
| Backward | Uses the version as a template for the previous period. The new version is saved under the previous Date tag. Use it to restate an earlier period with new information. | Roll a 2+10 forecast back to produce a corrected 1+11 forecast. |
The hint under the option you select tells you exactly what will happen, for example "Copy data from October 2024 and roll it one Month forward".
Where Roll period appears: Fileboxes with a Date tag. It doesn't appear for Budget or Forecast scenario Fileboxes, or for a Collection Process.
Calculate from the Filebox
-
Open the Filebox and click + New version. Select Generate calculated version and click Continue. The new version is calculated from the latest version in the period.
To calculate from a specific version instead, click the ⋮ menu on that version's row and select Calculate.
-
In the Generate calculated version window, select the Roll period. If you select None, also choose the tag under Tag the new version as.
- Click Calculate.
The new version appears on the Versions page a few minutes later, under the Date tag you chose or rolled to.
Calculate every Filebox in a Collection Process
-
Open the Collection Process and click the calculator icon next to the Actions button. Its tooltip reads "Calculate".
- Under Calculate new versions based on this tag, choose the tag to calculate from. Then choose the tag for the new versions.
- Click Calculate.
A new version is added under the chosen Date tag for every Filebox in the Collection Process.
Roll forward from the Excel add-in
- Open the file in Excel.
- On the Datarails add-in, in the Report view section, click Roll Forward.
A new version is added under the next Date tag.
Set up Auto Calculate
With Auto Calculate turned on, Datarails creates new versions for you. For example, a consolidated budget report that pulls from each department's budget file updates itself whenever a department submits a new file.
- Open the Filebox's settings. In the Workspace, click the ⋮ menu on the Filebox's row and select Settings. You can also click the settings icon at the top of the Versions page.
-
On the General tab, turn on Auto-Calculate. This is saved as soon as you turn it on.
-
Click Edit auto-calc settings and choose the settings you need.
- Click Save changes. To leave without saving, click Discard and go back.
Auto Calculate settings
| Setting | Options |
|---|---|
| Trigger |
Source file change: calculates when a source file is updated. Choose All source files, or Specific source files to react only to the Fileboxes you select. Time interval: calculates on a schedule. |
| Versions to calculate |
Latest version of most recent time dimension only (recommended): recalculates only the newest period. Latest version of every time dimension: recalculates the latest version in each period. |
| Auto-assign new time dimensions | When a new period doesn't exist in the Filebox yet, creates a version for it automatically. |
After you save, the General tab shows a summary of what triggers Auto Calculate and which version is calculated.
Auto Calculate for a Collection Process
Turn on Auto-Calculate in the Collection Process's settings. It applies to every Filebox in the Collection Process. See Collection Process Overview.
© Datarails Ltd. All rights reserved.
Updated