Formulas - Rollup Logic: What does it mean?

What is Rollup Logic?

As shown below, Rollup Logic is a field within the formulas section in the Account Dimension. 

Please Note: the Rollup Logic option is only applicable to Dimensions with Formulas. 

Cube supports 2 types:

- Sum leaf values, then calculate (default)

- Calculate leaf values, then sum

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

How does it work? Which Rollup Logic should I select for my formula?

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 fetched for Account C will be different, as shown 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 fetch for Account C will be equal to the sum of Account A across regions which is 8+1+4= 13 multiplied by the sum of Account B across all regions which is 2+5+8= 15, resulting in 13*15= 195. 

In other words, the "Sum leaf values, then calculate" Rollup Logic, 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 make the calculation. The two sums are then multiplied together leading to 13*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 fetch for Account C will be equal to the sum of Account A multiplied by Account B for all regions which is 8*2 = 16 for Region A plus 1*5= 5 for B, and 4*8= 32 for C, resulting in 16+5+32= 53. 

The Rollup Logic “Calculate leaf values, then sum”, first calculates (e.g. multiply/divide) the values in Account A with the values Account B within the different Top Level Dimensions (e.g. location/division/department/region) which give (8*2 = 16) for TLD Region A, (1*5= 5) for Region B, and (4*8= 32) for Region C. Then all the calculations (multiplications or divisions) will be summed. In this case, all the multiplications are summed, resulting in (16+5+32= 53).

For more information on how to add formulas, or create dimension with formula.