Guide to Formulas Rollup Logic

You can select your formula's rollup logic in Cube depending on your goal.

Formula rollup logic determines the calculation order and only applies within the formulas section in the Account Dimension.

• Sum leaf values, then calculate (default)
• Calculate leaf values, then sum

You can modify the formula rollup logic by navigating to the Dimension or Formula page and editing the dimension. 

How does it work?

Let's take a look at the following example:

"Account A" and "Account B" contain the values shown above, and we create a new account dimension, "Account C", which value will be equal to the following formula: "Account A" * "Account B".

We now need to select a Rollup Logic for the formula in the Account C dimension. Depending on the rollup logic chosen, the value for Account C will differ.

Let's look at an example below:

"Sum leaf values, then calculate"       vs. "Calculate leaf values, then sum"

vs. 

Sum leaf values, then calculate

If we select "Sum leaf values, then calculate" for the Rollup Logic, when fetching "Budget" for all "Regions" meaning Region A, B, and C, the amount fetched for Account C will be equal to the sum of Account A across regions8+1+4=13multiplied by the sum of Account B across all regions 2+5+8=15 resulting in 13*15=195

In other words, the "Sum leaf values, then calculate" first sums up the values for Account A across the different Top Level Dimensions (e.g. location/division/department - in this case, Region A, B, and C), which gives 8+1+4=13, and Account B separately 2+5+8=15 and then the two sums are calculated together (in this case, multiplied) leading to13*15=195

Calculate leaf values, then sum

If we select "Calculate leaf values, then sum" for the Rollup Logic, when fetching "Budget" for all "Regions" meaning Region A, B, and C, the amount fetched for Account C will be equal to the sum of Account A multiplied by Account B for all regions which are 8*2=16 for Region A plus 1*5=5  for B, and4*8=32 C, resulting in 16+5+32=53

“Calculate leaf values, then sum” first calculates the values in Account A with the values in Account B within the different Top Level Dimensions, 8*2=16, for TLD Region A, 1*5=5 for Region B, and 4*8=32for Region C. Then, all the calculations will be summed. In this case, all the multiplications are summed, resulting in16+5+32=53

Learn how to add formulas or create dimensions with formulas.