Additional Cube Functions

Nested Percent Variants

Cube makes available four special functions, which are Nested versions of the Percent, PercentCount, Percent100 and PercentCount100. The functions behave similarly to the basic percent functions but the result is derived from all values received divided by all values in that group. Therefore, unlike Percent, which will give a percentage out of all records, Nested Percent will give the percentage out of all siblings in the same level of the cube.

Here is an example, just looking at one Cube axis:

                       Percent    Nested Percent
USA                    100        100
      AZ                30         30
           Apple        15         50
           Orange       10         33
           Pear          5         17
      WA                70         70
           Apple        40         57
           Orange       20         29
           Pear         10         14

Using Percent for USA-AZ-Apple would sum the value of Apples sold in Arizona and sum the value of all fruit in USA and work out the percent from those values. In other words, the value would be the percent of Arizona Apples compared to all fruit (15% in this example).

Using Nested Percent on the other hand works out the sum relative to the siblings. In this case USA-AZ-Apple would give the percentage of Apple sales compared to all fruit sales in Arizona (AZ-Apple+AZ-Orange+AZ-Pear). In this nested case it is 50%.

In the Percent column, all the items at the same level in the tree add up to 100%. In the Nested Percent column, each element's children add up to 100%.