Formulas overview

A formula is a child dimension whose value is calculated from other dimensions rather than imported from your source system or published from your spreadsheet. Formulas take the hard work out of updating multiple spreadsheets by storing and updating frequently used calculations alongside the rest of your Cube data.

Add, remove, or update your formulas in the Formulas or Dimensions sections of the Web portal. 

Learn more about how Cube structures your data. 

Write Formulas

Create a formula by selecting a parent dimension and then writing the formula. Use quotation marks around Cube dimensions to reference them. Get started with common formulas, or write your own by referencing our list of supported operations. 

The background color of a dimension in the formula field will turn green if the text you input matches a Cube dimension. If the dimension name is mistyped, it will be red.

Choose which scenario(s) the formula should apply to if you need different calculations for different scenarios. See more on this below. 

Then select the formula rollup logic. This determines the calculation order:

Sum leaf values, then calculate

Calculate leaf values, then sum

See the guide to formulas rollup logic to learn more.

Example formula

We can add a formula to calculate Gross Margin in Cube by adding a new dimension, naming it Gross Margin, and writing out the formula under Sources. 

We'll include this formula dimension in our range in our spreadsheet. When Cube fetches data, it will check all Gross Profit and Income values and calculate the formula in your spreadsheet.

Learn more about adding formulas.

Scenario-specific formulas

Formulas can be general calculations that apply to all scenarios, such as Net Income, or they can be applied to specific scenarios, like a Forecast.

When working with accounts that typically have static values applied, use a formula for a specific scenario. For example, your Forecasted Revenue might be calculated by several drivers, while your Actual Revenue includes hard numbers from your accounting system.

In this example, you would create a Scenario-based formula for your Revenue Account that only applies to your Forecast Scenario.

Tip: Scenario-based formulas can only be used for Account dimensions and cannot be applied to Scenarios with Actuals write protection enabled. 

Explore an example of a Scenario-based formula to learn more. 

Make changes to dimensions used in formulas

Suppose you have a child dimension called Revenue that you would like to expand into the specific Revenue streams, e.g., Product Revenue.

When you add the new Product Revenue child dimension, Cube automatically creates a "Revenue Only" dimension to store all the data originally associated with Revenue. This is because values are stored at the lowest level.

So the hierarchy started like this:

• Revenue 

Ultimately becomes:

• Revenue

  • Product Revenue 

  • Revenue Only

By default, Cube moves all the values in Revenue into Revenue Only. We also keep the formula consistent for Gross Margin, now using Revenue Only.

However, assume you add data and want to use Product Revenue for Gross Margin now.

• Old formula: Gross Margin = "Revenue Only" - "COGS"

• New formula: Gross Margin = "Product Revenue" - "COGS"

When you save the formula, Cube automatically notes and recalculates the change. 

Note: It may take some time for the recalculation to complete.

In some places, you might have had data for Revenue Only, but you still need to get data for Product Revenue or vice versa. When Cube calculates Gross Margin, it overwrites the old Gross Margin values with the new ones.

Let's take a look at some examples:

1. Before, your Cube had Revenue Only and COGS at this intersection. Cube also has Product Revenue at this intersection, so Cube recalculates and updates the value of the Gross Margin with the new formula.

2. Before, your Cube had COGS but no Revenue Only at this intersection, so Gross Margin was negative since Cube calculated "0 - COGS". Now that your Cube also has Product Revenue at this intersection, Cube overwrites the negative number with the newly calculated value.

3. Before, your Cube had Revenue Only and COGS at the intersection. Cube doesn't have new Product Revenue data to replace it, and Gross Margin will become negative here.

Cube automatically detects when a formula changes, whether it is the values themselves or a new dimension, and will recalculate based on those changes. 

Recalculate a formula

Manual recalculation

Formulas can be manually recalculated at any time from the Cube Portal on your Formulas page:

After the recalculation has been completed, you'll be notified via email.

Automatic recalculation

Formulas are automatically recalculated whenever a data change occurs for the dimensions that the formulas reference. The following events can trigger automatic recalculations in your Cube:

• Data imports
• Data publish
• Re-mapping of dimensions
• Duplication of dimensions
• Disabling of dimensions
• Editing a Formula
• Changing a hierarchy
• Merging a scenario

Note: If your formula includes a formula-based dimension, Cube will first recalculate the formula-based dimensions before the formula can recalculate. Given the amount of data calculated, this can take time. 

For example, Gross Profit is calculated based on value-based dimensions. As these values are updated, so are the relative dimensions (Revenue and COGS).

Then, a formula like Operating Income is calculated based on a formula-based dimension (Gross Profit) and a value-based dimension (Operating Expenses).

Operating Income = Gross Profit - Operating Expenses

When it comes time to recalculate, the formula's components (Revenue and COGS) must first be recalculated, then the calculation for Gross Profit less Operating Expenses, and finally, the output, Operating Income.

Cube formulas vs. spreadsheet formulas

Cube formulas are best used when you have a recurring formula in your spreadsheet based on varying calculations and/or rollups.

For example, suppose your standard reporting package includes Gross Profit and Operating Income. You can save time by having Cube calculate these on your behalf, and the formula automatically updates every time the corresponding values change in the relevant dimensions. 

While Cube formulas can also be used for one-off calculations, it's not recommended. These are best kept directly in your spreadsheet. However, you can still use them if it works best for your process.

The best practice is to remove formulas for one-off calculations from your Cube when they are no longer needed. If you create these formulas repeatedly, add or leave them in your Cube to give yourself some time back.

Learn more about when to use formulas, tags, or dimension rollups.