Common formulas and supported operations – Cube

Use the list of common formulas below to help you start adding formulas in Cube. Have additional formulas you use that aren't in our common formulas list? Use the supported operations table to write your own formulas.

Learn more about using formulas with Cube.

Common formulas

Modify dimension names in the formulas below to exactly match your dimensions in Cube.

Income Statement Formulas

Dimension Name	Cube Formula
Gross Profit	"Income"-"Cost of Goods Sold"
Gross Margin	"Gross Profit" / "Income"
Ebitda	"Gross Profit"-"Expense"
EBIT	"EBITDA"-"Depreciation & Amortization Expense"
EBT	"EBIT"+"Other Income"-"Other Expense"
Net Income	"EBIT"-"Income Tax Expense"

Balance Sheet Formulas

Dimension Name	Cube Formula
Retained Earnings (f)	<RUNNING_TOTAL("Net Income")> - <YEAR_TO_DATE("Net Income")>
Net Income (BS)	<YEAR_TO_DATE("Net Income")>

Cash Flow Formulas

Two important notes for common cash flow formulas:

The rollup account type for these formulas is set to sum rather than the beginning cash balance. Learn more about the rollup account type here.
For any formula using <MONTHS_AGO>, the rollup logic should be set to Calculate Leaf Values, Then Sum. Learn more about rollup logic for formulas here.

Dimension Name	Cube Formula
Retained Earnings (f)	<RUNNING_TOTAL("Net Income")> - <YEAR_TO_DATE("Net Income")>
Net Income (BS)	<YEAR_TO_DATE("Net Income")>
Chg in Current Assets	<MONTHS_AGO("Current Assets",1)>-"Current Assets"
Change in Net PP&E	<MONTHS_AGO("Fixed Asset",1)>-"Fixed Asset"
Change in Net Other Assets	<MONTHS_AGO("Other Asset",1)>-"Other Asset"
Change in Long-Term Debt	"Long Term Liability"-<MONTHS_AGO("Long Term Liability",1)>
Change in Capital Stock	"Capital Stock"-<MONTHS_AGO("Capital Stock",1)>
Beginning Cash Balance	<MONTHS_AGO("Bank",1)>
Change in Cash	"Operating Cash Flow"+"Investing Cash Flow"+"Financing Cash Flow"

Supported operations

Use these to write your own formulas. The last column shows the recommended rollup logic. See the quick guide to rollup logic for why.

Operation	How to write it	Example	Recommended rollup logic
Arithmetic
Add	`+`	`"Revenue" + "Discounts"`	Calculate leaf values, then sum
Subtract	`-`	`"Revenue" - "Cost of Goods Sold"`	Calculate leaf values, then sum
Multiply	`*`	`"Bookings" * 0.10`	Calculate leaf values, then sum
Divide	`/`	`"Gross Margin" / "Revenue"`	Sum leaf values, then calculate
Exponent	`^`	`"Churn" ^ 2`	Sum leaf values, then calculate
Fixed value	a number	`0.05`	Sum leaf values, then calculate
Math functions
Round	`ROUND(value)`	`ROUND("Product Revenue")`	Calculate leaf values, then sum
Round up	`ROUNDUP(value, decimals)`	`ROUNDUP("Product Revenue", 2)`	Calculate leaf values, then sum
Ceiling	`CEIL(value, decimals)`	`CEIL("Product Revenue", 2)`	Calculate leaf values, then sum
Floor	`FLOOR(value)`	`FLOOR("Product Revenue")`	Calculate leaf values, then sum
Absolute value	`ABS(value)`	`ABS("Variance")`	Calculate leaf values, then sum
Time functions
Months ago	`<MONTHS_AGO("Member", months)>`	`<MONTHS_AGO("Principal", 1)>`	Calculate leaf values, then sum
Running total	`<RUNNING_TOTAL("Member")>`	`<RUNNING_TOTAL("New ARR")>`	Calculate leaf values, then sum
Year to date	`<YEAR_TO_DATE("Member")>`	`<YEAR_TO_DATE("New ARR")>`	Calculate leaf values, then sum
Conditions
If, then, else	`IF(condition, value if true, value if false)`	`IF("Entity" = "GCo US", "602001 - Salary & Wages" * 0.062, 0)`	Calculate leaf values, then sum
Comparisons	`=!=>>=<<=`	`IF("Bookings" > 100000, "Bookings" * 0.10, "Bookings" * 0.05)`	Calculate leaf values, then sum
And, or	`AND`, `OR`	`IF("Entity" = "GCo UK" OR "Entity" = "GCo Australia", 1000, 0)`	Calculate leaf values, then sum
Is child of	`ISCHILDOF("Parent")`	`IF(ISCHILDOF("Sales + Marketing"), "602001 - Salary & Wages" * 0.10, 0)`	Calculate leaf values, then sum
Skip	`SKIP`, as the true or false value	`IF("Scenario" = "Actuals", "Revenue" / "Customer Count", SKIP)`	Calculate leaf values, then sum
References
Criteria	`"Member"("Dimension": "Member")`	`"Bookings" * "Commission Rate"("Market": "Market - Other")`	Depends on the formula. Use Sum leaf values, then calculate when the criteria reference stands on its own, such as a company total.
Tag	`"Tag name"`	`"Revenue" / "Marketing Expenses"`	Same as the formula around it
Tables
Table lookup	`TABLE_LOOKUP("Table", "Return column", "Match column", ...)`	`TABLE_LOOKUP("Product Breakdown", "Price", "Product", "Market")`	Calculate leaf values, then sum (required)
Table aggregations	`TABLE_SUM`, `TABLE_AVG`, `TABLE_COUNT`, `TABLE_MAX`, `TABLE_MIN`, with the same parts as TABLE_LOOKUP	`TABLE_SUM("Employee Roster", "Monthly Salary", "Department")`	Calculate leaf values, then sum (required)

Note: Only the three time functions use angle brackets. Write every other function without them, for example IF(...) and TABLE_LOOKUP(...). Wrap every dimension member, tag, Table, and column name in straight double quotes.

Table Lookup reads a value out of a Table, so formulas can use prices, rates and percentages from wherever your team maintains them. It takes more setup than the other operations here, because the columns you match on have to point to a single row. See Reference Table data in a formula.

Related to