While building formulas in Cube, you will select between two types of rollup logic. The rollup logic selection determines the order in which the values are calculated as part of your formula.
- Sum leaf values, then calculate: First, total the components of each dimension in the formula, and then perform the calculation. Recommended for ratios and percentages, and for rates or fixed values.
- Calculate leaf values, then sum: First, calculate the values across the dimensions in the formula, and then sum the parts. Recommended for formulas that add or subtract, apply a rate row by row, use IF or SKIP, or use table or time functions.
Use the recommended formula rollup logic for our supported operations or use the guide below to determine which method best suits your calculations.
You can modify a formula's rollup logic even after creating your formula by navigating to the Dimension or Formula page in the Workspace and editing the dimension.
Note: Calculate leaf values, then sum will result in the fastest fetch times. Be sure to use this rollup logic when possible.
Quick guide
| If your formula | Use | Example |
|---|---|---|
| Divides to produce a ratio or percentage | Sum leaf values, then calculate | "Gross Margin" / "Revenue" |
| Returns a rate or fixed value | Sum leaf values, then calculate | IF("Entity" = "GCo UK", 0.138, 0.0765) |
| Reads a fixed member or tag on its own, such as a company total | Sum leaf values, then calculate | "Revenue"("Entity": "Global Co") |
| Applies a rate that varies by department, product, or market | Calculate leaf values, then sum | "602001 - Salary & Wages" * "Bonus %" |
| Uses IF on a dimension, or ISCHILDOF | Calculate leaf values, then sum | IF("Entity" = "GCo US", "602001 - Salary & Wages" * 0.062, 0) |
| Uses SKIP | Calculate leaf values, then sum | IF("Scenario" = "Actuals", "Revenue" / "Customer Count", SKIP) |
| Uses a table function | Calculate leaf values, then sum (required) | "Units Sold" * TABLE_LOOKUP("Product Breakdown", "Price", "Product", "Market") |
| Uses MONTHS_AGO, RUNNING_TOTAL, or YEAR_TO_DATE | Calculate leaf values, then sum | <MONTHS_AGO("Current Assets", 1)> - "Current Assets" |
| Only adds or subtracts | Calculate leaf values, then sum | "Revenue" - "Cost of Goods Sold" |
If a formula needs both, such as a ratio inside an IF, split it into two formulas: one with the condition, using Calculate leaf values, then sum, and one that divides its totals, using Sum leaf values, then calculate.
How do the rollup logic options work?
We'll use the following example to highlight the difference between the formula rollup options.
Account A and Account B contain the values shown in the first two rows above.
We will use the formula "Account A"*"Account B" to create a new account dimension in the third row. We'll call this row Account C.
The value for Account C will differ depending on the rollup logic chosen when we create the formula. The image below shows a value of 195 if we sum the leaf values first, and a value of 53 if we calculate the leaf values first.
vs.
These numbers are drastically different because the order of operations can change the calculation significantly.
Neither number is wrong on its own. It depends on what Account B is. If Account B is a rate that differs by region, such as a commission rate, each region should use its own rate, so 53 is right. If Account C is meant to combine two totals, such as a ratio, 195 is the right reading. The quick guide above covers the common cases.
Sum leaf values, then calculate
Using sum leaf values, then calculate for the rollup logic means all values for each dimension will be totaled first, and then the calculation will be performed.
Sum the values of Account A, or, in this case, the Jan-20 Budget for Regions A, B, and C 8+1+4=13
Sum the values of Account B, or, in this case, the Jan-20 Budget for Regions A, B, and C 2+5+8=15
Then calculate according to the formula or 13*15=195
Calculate leaf values, then sum
The rollup logic, calculate leaf values, then sum, performs all calculations on each dimension value first, and then totals up the results.
In our example, this means Account C will equal the sum of Account A multiplied by Account B for all regions.
Calculate Region A 8*2=16
Calculate Region B 1*5=5
Calculate Region C 4*8=32
Sum the calculations for Account C 16+5+32=53
Formula rollup logic for calculations over time
When working with a formula that must consider a range of dates such as year-to-date or quarter-to-date, sum leaf values, then calculate will produce the expected results for most customers.
For example, you have a formula
"Revenue" / "Revenue"("Scenario": "Budget")
(see
Add Criteria to a Formula Reference)
calculating a variance between budget and actuals
| Dimension | Jan-23 |
Feb-23 |
Mar-23 |
| Budget vs. Actuals Variance | 0.89 |
0.93 |
0.98 |
Sum leaf values, then calculate
If you use sum leaf values, then calculate as the rollup logic, the formula will use the underlying values for each month's Budget and Actuals to calculate the variance for the quarter.
Budget vs. Actuals Variance 280,000 / 300,000 = 0.933
| Dimension | Jan-23 |
Feb-23 |
Mar-23 |
Q1-23 |
| Budget | 100,000 |
100,000 |
100,000 |
300,000 |
| Actuals | 89,000 |
93,000 |
98,000 |
280,000 |
| Budget vs. Actuals Variance | 0.89 |
0.93 |
0.98 |
0.933 |
Calculate leaf values, then sum
Using calculate leaf values, then sum would produce 2.8 for Q1-23 because it will total the calculated monthly variance value.
Budget vs. Actuals Variance 0.89 + 0.93 + 0.98 = 2.8
| Dimension | Jan-23 |
Feb-23 |
Mar-23 |
Q1-23 |
| Budget | 100,000 |
100,000 |
100,000 |
300,000 |
| Actuals | 89,000 |
93,000 |
98,000 |
280,000 |
| Budget vs. Actuals Variance | 0.89 |
0.93 |
0.98 |
2.8 |
Rollup logic for IF, SKIP, and criteria
These formulas fail quietly when the rollup logic is wrong, so check the setting before you rely on the totals.
-
IF on a dimension. With Sum leaf values, then calculate, Cube checks the condition at the total. For
IF("Department" = "Sales", ...)All Departments isn't Sales, so the total comes back as the false value. Use Calculate leaf values, then sum. See Write Conditional Formulas With IF.
- SKIP. With Sum leaf values, then calculate, a value you enter into a skipped cell shows in its own cell but is missing from every total above it. Use Calculate leaf values, then sum. See Mix Calculated and Manual Values With SKIP.
- Criteria that read a fixed member or tag on their own. With Calculate leaf values, then sum, the fixed value is added once for every member underneath, so totals come out too high. Use Sum leaf values, then calculate. See Add Criteria to a Formula Reference.
Rollup logic for formulas that read a Table
Formulas that use TABLE_LOOKUP, TABLE_SUM, TABLE_AVG, TABLE_COUNT, TABLE_MAX, or TABLE_MIN always use Calculate leaf values, then sum. Cube works the lookup out at the lowest level of detail first, then adds the results up.
This is worth knowing because a total will not match a lookup done at the total level. If each department has its own bonus rate, total bonus expense is every department's bonus added together. The overall percentage that results will not appear anywhere in the Table, and that is expected.
For example, with total salaries of $1,070,000 spread across six departments whose bonus percentages range from 15% to 35%, total bonus expense comes to $306,200. That is 28.6% of the total, a rate that appears in none of the Table rows because it is a weighted result rather than an input.
See Reference Table data in a formula.
Related articles
- Write Conditional Formulas With IF
- Add Criteria to a Formula Reference
- Mix Calculated and Manual Values With SKIP
- Reference Table data in a formula
- Common formulas and supported operations