Write Conditional Formulas With IF – Cube

Use IF to calculate a formula one way when a condition is true and another way when it isn't, for example a bonus that only applies to Sales or a commission rate that changes above a revenue threshold.

Required permission: Conditional formulas use the same permissions as creating and editing any formula in Cube. No additional permission is needed.

Write an IF formula

IF works the same way it does in a spreadsheet. It takes three parts, separated by commas:

IF(condition, value if true, value if false)

Part	What it does	Example
Condition	The question Cube asks for each cell.	`"Entity" = "GCo US"`
Value if true	What the cell returns when the condition is true.	`"602001 - Salary & Wages" * 0.062`
Value if false	What the cell returns when it isn't.	`0`

Put together:

IF("Entity" = "GCo US", "602001 - Salary & Wages" * 0.062, 0)

Either value can be a number, a reference, a calculation, another IF, or SKIP.

Go to Formulas in your Workspace.
Select New, or edit an existing formula.
Type
IF(
in the Formula field, or select Insert Functions and choose IF from the Logical category.
Enter the condition, the value if true, and the value if false.
Select the rollup logic. See Choose rollup logic for IF formulas.
Select Save Formula.

Note: IF always needs all three parts. If one is missing, the formula won't save.

Write a condition

A condition can ask two kinds of question.

Question	Example	What it checks
Where is this cell?	`"Department" = "Sales"`	Whether the cell being calculated belongs to a dimension member.
How big is this number?	`"Revenue" > 100000`	The value of a reference at the cell being calculated, compared with a number or another reference.

Use any of these operators:

Operator	Meaning
`=`	Equal to
`!=`	Not equal to
`>`	Greater than
`>=`	Greater than or equal to
`<`	Less than
`<=`	Less than or equal to

Rules for "where is this cell?" conditions

Name a member with no children.
"Department" = "Sales"
works.
"Department" = "Sales + Marketing"
doesn't, because Sales + Marketing is a parent. To check everything under a parent, use ISCHILDOF.
Check a different dimension from the formula's own. An Account formula can check Department, Entity, Scenario, or any other dimension, but not Account.
Use = and !=.
For example,
"Scenario" != "Actuals"
is true in every scenario except Actuals.

Combine conditions with AND and OR

Use AND when every condition must be true, and OR when any one of them can be.

Goal	Formula
IT bonus pool, US entity only	`IF("Department" = "IT" AND "Entity" = "GCo US", "602001 - Salary & Wages" * 0.10, 0)`
Holiday bonus for the UK or Australia	`IF("Entity" = "GCo UK" OR "Entity" = "GCo Australia", 1000, 0)`
Sales in the UK or Australia	`IF(("Entity" = "GCo UK" OR "Entity" = "GCo Australia") AND "Department" = "Sales", "Revenue" * 0.02, 0)`

Note: Cube checks AND before OR, the same as most spreadsheets. A OR B AND C means A, or both B and C. When you mix AND and OR, add parentheses so the formula reads the way you intend.

Apply a rule to everything under a parent with ISCHILDOF

ISCHILDOF("Parent") is true when the cell being calculated sits anywhere under that parent, at any level. Use it in place of a list of members.

IF(ISCHILDOF("Sales + Marketing"), "602001 - Salary & Wages" * 0.10, 0)

This gives every department under Sales + Marketing a bonus. When a department moves under Sales + Marketing, the formula includes it on the next recalculation, with no edit needed.

ISCHILDOF replaces the whole comparison. It already knows which dimension the parent belongs to, so don't put
"Department" =
in front of it.
The parent itself isn't included.
ISCHILDOF("Sales + Marketing")
is true for Sales and Marketing, and false for Sales + Marketing.
It works with AND and OR. For example,
IF(ISCHILDOF("Delivery") AND "Entity" = "GCo US", "602001 - Salary & Wages" * 0.05, 0)
.
Use a different dimension from the formula's own, the same as other conditions.

Instead of Write

IF("Department" = "General & Administrative", ...) IF(ISCHILDOF("General & Administrative"), ...)

IF("Department" = ISCHILDOF("General & Administrative"), ...) IF(ISCHILDOF("General & Administrative"), ...)

A long chain of

"Department" = ... OR "Department" = ...

for every team under one parent

IF(ISCHILDOF("General & Administrative"), ...)

Use more than two outcomes with nested IF

Put another IF in the value-if-false position to add a tier.

IF("Bookings" > 500000, "Bookings" * 0.12, IF("Bookings" > 100000, "Bookings" * 0.10, "Bookings" * 0.05))

Territory	Bookings	Rate	Commission
East	80,000	5%	4,000
Central	250,000	10%	25,000
West	600,000	12%	72,000
Total	930,000		101,000

Tip: Select Cleanup Formula to indent each level so nested IFs are easier to read. Beyond two or three levels, consider splitting the logic into separate formulas.

Set a fixed value

A formula can be just a number. 0.05 sets a 5% assumption everywhere the formula appears, without publishing it to every cell. Combine it with IF for a different value by entity, department, or scenario:

IF("Entity" = "GCo UK", 0.138, 0.0765)

Use fixed values for rates and assumptions that other formulas read, such as "602001 - Salary & Wages" * "Payroll Tax Rate".

Leave some cells for manual input

Use SKIP as either value to leave a cell uncalculated, so it can be filled by publishing or importing instead. For example, IF("Scenario" = "Actuals", "Revenue" / "Customer Count", SKIP) calculates ARPU in Actuals and leaves it open for your team to enter in Budget and Forecast. See Mix Calculated and Manual Values With SKIP.

Choose rollup logic for IF formulas

Rollup logic decides whether Cube calculates at the lowest level and adds up the results, or adds up the inputs first and then calculates. It matters more for IF formulas than for any other kind.

If your formula Use Why

Checks where the cell is (

"Department" = "Sales"

, ISCHILDOF) and returns an amount

Calculate leaf values, then sum

With the other setting, Cube checks the condition at the parent. All Departments isn't Sales, so the total comes back as the false value.

Checks a number (

"Bookings" > 100000

) and returns an amount

Calculate leaf values, then sum

Each territory gets its own tier. With the other setting, the total applies one tier to the combined revenue.

Returns a rate, percentage, or fixed value Sum leaf values, then calculate Rates can't be added up. With the other setting, parent totals add the rate once for every member underneath.

In the commission example above, Calculate leaf values, then sum returns the correct 101,000 total. Sum leaf values, then calculate applies 12% to the combined 930,000 and returns 111,600.

See the Guide to formulas rollup logic for more.

More examples

Goal	Formula
Avoid dividing by zero	`IF("Headcount" <= 0, 0, "Personnel Expenses" / "Headcount")`
Calculate only outside Actuals	`IF("Scenario" != "Actuals", "Bookings" * 0.85, 0)`
Different benefits load for G&A	`IF(ISCHILDOF("General & Administrative"), "602001 - Salary & Wages" * 0.25, "602001 - Salary & Wages" * 0.20)`
Flag revenue more than 10% over Budget	`IF("Revenue"("Scenario": "Budget") != 0, IF(("Revenue" - "Revenue"("Scenario": "Budget")) / "Revenue"("Scenario": "Budget") > 0.1, 1, 0), 0)`

If something is not right

What happened	What to check
Conditions in IF statements can't use parent dimensions. Try selecting a child instead of a parent.	The condition names a parent member. Pick a member with no children, or use `ISCHILDOF("Parent")` .
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.
The formula won't save	Check that IF has all three parts, every name is in straight double quotes, and parentheses are balanced. Cleanup Formula can help.
Leaves look right but totals are 0 or wrong	Check the rollup logic. See Choose rollup logic for IF formulas.
AND and OR give an unexpected result	Add parentheses. Cube checks AND before OR.

Related to