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 formulas that use multiplication and division.
- Calculate leaf values, then sum: First, calculate the values across the dimensions in the formula, and then sum the parts. Recommended for formulas that use addition and subtraction.
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.
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.
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 "Actuals"/"Budget" calculating a variance between budget and actuals for the first quarter of FY23 that gives you the following values and you would like to calculate the variance for Q1-23.
| 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 |