0 Replies Latest reply: Sep 6, 2013 10:58 AM by Andrea Gigliotti

# Pivot Table how to get partial sums into expressions definition

Hello experts,

I have the attached pivot table with 2 calculated dimensions and 1 expression.

see below the 2 dimensions:

first dimension:

=if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.10), 10,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.20), 9,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.30), 8,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.40), 7,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.50), 6,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.60), 5,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.70), 4,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.80), 3,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 0.90), 2,

if( aggr(\$(vMargAP), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAP), [Codice Cliente]), 1), 1 ))))))))))

second dimension:

=if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.10), 10,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.20), 9,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.30), 8,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.40), 7,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.50), 6,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.60), 5,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.70), 4,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.80), 3,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 0.90), 2,

if( aggr(\$(vMargAC), [Codice Cliente]) <= Fractile(total aggr(\$(vMargAC), [Codice Cliente]), 1), 1 ))))))))))

where vMargAP and vMargAC variables content is SUM with set analysis.

I need to get the partial sums of first dimension into expression definition because I have to display the percentage of the cell value to the total row value.

ex. Decile 2012 = 1 and Decile 2013 = 10  I have to display the calc 64/1301*100 = 4,91%

for Decile 2012 = 2 and Decile 2013 = 2     I have to display the calc 376/1300*100 = 28,92%

....

and so on for each cells values.

Can anyone help me to achieve it ?