Use this article when a formula won't save or saves but returns numbers you don't expect. Find the message or symptom you're seeing, then follow the fix.
The formula won't save
When a formula can't be saved, Cube shows a message in the formula drawer. Messages about syntax usually include the line and column where Cube stopped reading.
| Message or symptom | Cause | Fix |
|---|---|---|
| Syntax | ||
| An error occurred parsing this formula at line 1, column 1 |
The formula starts with
=.
Cube formulas don't begin with
=
the way spreadsheet formulas do.
|
Remove the
=.
|
| An error occurred parsing this formula at a later column | Something at that column doesn't match the formula syntax. Usually a missing or extra parenthesis, an IF with only two parts, or a name without quotes. | Check the formula around that column. Select Clean up formula to balance parentheses and indent nested functions so the problem is easier to see. |
| A name stays red, or never turns green | The text in quotes doesn't exactly match a dimension member, tag, Table, or column. A typo, an extra space, or different capitalization. | Pick the name from the suggestions as you type, or copy it exactly from the Dimensions page. |
| The formula won't save after you paste it in | The quotes are curly quotes from Word, Google Docs, or email. |
Retype every quote as a straight double quote (").
|
| Conditions and criteria | ||
| Conditions in IF statements can't use parent dimensions. Try selecting a child instead of a parent. |
The condition names a parent member, such as
"Department" = "Sales + Marketing".
|
Name a member with no children, or use
ISCHILDOF("Sales + Marketing")
on its own, without
"Department" =
in front.
|
| A formula can't compare a dimension to itself. Use a dimension different from the parent of this formula. | The condition checks the formula's own dimension, such as Account in an Account formula. | Check a different dimension, such as Department, Entity, or Scenario. |
| The top level dimension for a dimension in a formula cannot differ from the top level for the formula dimension itself |
The formula references a member of another dimension directly,
such as
"Actuals" / "Budget"
in an Account formula.
|
Read the other dimension with criteria instead:
"Revenue" / "Revenue"("Scenario": "Budget").
See
Add Criteria to a Formula Reference.
|
| References between formulas | ||
| Scenario based formulas cannot be referenced in other formulas | The formula references an account that has a scenario-based formula. | Rebuild that account with IF on Scenario. See Create scenario-based formulas. |
| Dimension formula cannot reference itself or its parent | The formula references itself, or two formulas reference each other. Cube checks this across all scenarios at once, even if the two never calculate in the same scenario. | Trace which formulas reference each other, and split the logic into separate accounts. See Mix Calculated and Manual Values With SKIP. |
| cannot be a formula since it is mapped to a General Ledger dimension | The account receives imported data, and the formula would calculate over it. | Have the formula skip every scenario that receives imported data. See Mix Calculated and Manual Values With SKIP. |
| Tables | ||
| Table 'X' does not exist or Column 'X' does not exist in table 'Y' | The Table or column name is misspelled, or you don't have access to the Table. | Check the spelling against the Table, and check that it's shared with you. |
| Column 'X' is not linked to a cube dimension | A match column isn't a Dropdown: Dimension column, and no value was given for it. |
Give it a value, like
("Status", "Existing").
|
| Table 'X' does not exist in scenario 'Y' | The Table name includes a scenario in brackets, and the Table has no version in that scenario. | Save a version of the Table in that scenario, or remove the brackets. |
For example, this formula won't save because of the leading =:
| Won't save | Saves |
|---|---|
=<MONTHS_AGO("Current Assets", 1)> - "Current Assets"
|
<MONTHS_AGO("Current Assets", 1)> - "Current Assets"
|
The formula saves, but the numbers look wrong
| What you see | Why | Fix |
|---|---|---|
| Totals are 0, blank, or too high, but the cells underneath look right | The rollup logic doesn't match the formula. | See the quick guide to rollup logic. |
| An IF formula returns the false value at every total | The IF checks a dimension, and Cube is checking the condition at the total, where it's never true. | Switch the formula to Calculate leaf values, then sum. |
| A value you entered into a SKIP cell shows in its cell but not in totals | The formula uses Sum leaf values, then calculate. | Switch it to Calculate leaf values, then sum. See Mix Calculated and Manual Values With SKIP. |
| A formula with criteria shows the same value under every member, and totals are too high | Criteria change where a value is read from, not which cells the formula calculates in. | Use IF with AND / OR or ISCHILDOF to choose which cells calculate. See Write Conditional Formulas With IF. |
| A formula with criteria is blank | Nothing is stored at the member the criteria point to, in the scenario and month you're viewing. | Check that data exists for that member. |
| A TABLE_LOOKUP formula is 0 or empty for some figures | Those figures matched more than one row (you'll see a warning after recalculation), or matched no row. | Add match columns until each figure points to one row, switch to TABLE_SUM if the rows should be added together, or add the missing row to the Table. See Reference Table data in a formula. |
| A formula using MONTHS_AGO, RUNNING_TOTAL, or YEAR_TO_DATE gives odd totals | Time functions need to calculate month by month. | Set the rollup logic to Calculate leaf values, then sum. |
| Values haven't updated after a change | Cube recalculates in the background, and formulas that depend on other formulas recalculate in order. | Wait a few minutes, or recalculate the formula from the Formulas page. |
Quick checklist
Before you save a formula, check that:
- The formula doesn't start with
= - Every dimension member, tag, Table, and column name is in straight double quotes
- Each quoted name turns green in the Formula field
-
Only
<MONTHS_AGO(...)>,
<RUNNING_TOTAL(...)>, and
<YEAR_TO_DATE(...)>use angle brackets. Every other function, such as
IF,
ROUND, and
TABLE_LOOKUP, is written without them
- Every IF has three parts: condition, value if true, value if false
-
Conditions name members with no children, or use
ISCHILDOF("Parent") - The rollup logic matches the formula. See the quick guide to rollup logic
Tip: Spaces around operators are optional, but they make formulas easier to read. Clean up formula adds them for you.
Still stuck?
Contact Cube Support with the exact message (including any line and column), a copy of the formula text, the rollup logic setting, and a screenshot of where the number looks wrong.