The formula editor helps you write Cube formulas without memorizing names or syntax: it suggests dimensions, members, tags, Tables, and functions as you type, color codes each part of the formula, and inserts references and functions for you.
Required permission: The editor uses the same permissions as creating and editing any formula in Cube. Finance users and Admins can create and edit formulas. Business users can view them.
Open the editor
The editor appears in two places, and works the same way in both.
- Formulas page: go to Formulas in your Workspace and select + Formula, or select the edit icon on an existing formula.
- Dimensions page: go to Dimensions, open a formula member, and use its formula section.
What's in the drawer
| Field | What it's for |
|---|---|
| Formula Name | The name of the formula member. Required. |
| Parent | Where the formula sits in your hierarchy. |
| Description | Optional. A plain-language note on what the formula calculates and why, so the next person doesn't have to work it out. Available in the Formulas drawer. |
| Formula Logic | The formula itself, up to 5,000 characters. A counter shows how many you've used. |
| Apply To | Which scenarios the formula applies to. Defaults to All Scenarios. See Create scenario-based formulas. |
| Rollup logic | Whether Cube calculates at the lowest level and then adds up, or adds up first and then calculates. Defaults to Sum leaf values, then calculate. See the Guide to formulas rollup logic. |
| Rollup Type | How the formula rolls up over time: sum, average, ending balance, or beginning balance. |
Tip: Write the business rule in Description before you write the formula, for example "5% of Sales department revenue at the Other market rate." It makes the formula easier to check and to hand off.
Get suggestions as you type
As you type in Formula Logic, the editor suggests what can come next. Press Tab to accept a suggestion or Esc to dismiss it.
| When you type | The editor suggests |
|---|---|
"and a few letters |
Dimension members and tags that match |
(right after a reference |
Dimensions you can add as criteria |
:inside criteria |
Members of that dimension |
|
Inside TABLE_LOOKUP(or TABLE_SUM(
|
Your Tables first, then the columns of the Table you picked |
[after a Table name |
Scenarios the Table has a version in |
| Letters in plain text | Matching members, tags, functions, and keywords such as IF and SKIP |
Suggestions only include members, tags, and Tables you have access to.
Read a formula at a glance
The editor color codes each part of a formula, so functions, references, time functions, and text each look different. References also show whether they're valid: a reference turns green when it matches a dimension member in your Cube and red when it doesn't, usually because of a typo.
Insert a dimension member
- Place your cursor where the reference should go.
- Select the Insert Dimension icon above Formula Logic.
- Select a Dimension and a Member.
- To read the value from a specific department, scenario, or other member, select a dimension from the list below Member and pick its member. Repeat for each criterion.
- Check the reference in Preview (will insert).
- Select Insert into Formula.
Note: Any dimension can be the main selection, not only Account. Insert into Formula stays disabled until every criterion has a member. To drop a criterion, select the remove icon next to it.
Insert a function
- Place your cursor where the function should go.
- Select the f(x) icon above Formula Logic.
- Browse by category or search by name. Selecting a function shows what it does and how to write it.
- Select the function to insert it.
| Category | Functions |
|---|---|
| Math | ROUNDUP, CEIL |
| Logical | IF, AND, OR, ISCHILDOF, SKIP |
| Time | MONTHS_AGO, RUNNING_TOTAL, YEAR_TO_DATE |
| Tables | TABLE_LOOKUP, TABLE_SUM, TABLE_AVG, TABLE_COUNT, TABLE_MAX, TABLE_MIN |
The editor inserts the function with a slot for each part, for example TABLE_SUM("", "", ""), and places your cursor in the first one. Suggestions then help you fill in each part.
Note: Most functions are written without angle brackets, for example
IF(...)andTABLE_LOOKUP(...). Only the three time functions keep them:<MONTHS_AGO(...)>,<RUNNING_TOTAL(...)>, and<YEAR_TO_DATE(...)>. The editor inserts each one the right way.
Clean up and expand long formulas
- Clean up formula: select the clean up icon above Formula Logic to tidy spacing, indent nested IFs and functions, and balance parentheses where it can tell what you meant. It only changes formatting, never what the formula calculates.
- Expand Editor: select Expand Editor below the field to widen the drawer when you're working on a long formula.
Save your formula
- Check Apply To, Rollup logic, and Rollup Type.
- Select Save Formula.
Cube recalculates the formula in the background, which can take a few minutes for formulas that depend on other formulas. If you close the drawer without saving, Cube asks before discarding your changes.
Good to know
- Existing formulas work as they are. They open in the editor with color coding, and there's nothing to convert.
- Changes to tags or your hierarchy made while the drawer is open show up in suggestions the next time you open it.
- If two people edit the same formula at once, the last save wins.
- The editor lives in your Workspace. You can still fetch, report on, and publish around formula values from your spreadsheets.