3 Replies Latest reply: Dec 13, 2011 6:09 PM by Pooja Nanda

# Pivot Chart-Total calc Issue

Hi,

Raw data looks like this

NBRTimestatus
10010/15/2011 11.30PMdone
10010/15/2011 11.35PMdone
10110/15/2011 11.36PMdone
10210/15/2011 11.37PMdone
10310/15/2011 11.38PMdone

Now in the pivot table

NBRCtFct
10020
10111
10211
10311
TOTAL50

Here Ct and Fct are the Expressions.

For Calculation of Ct,I have used

Count(DISTINCT TOTAL<nbr>timeEnter)

and for the Fct,I have used if([Ct]=1,1,0)

When I do a partial sum on Nbr,I am getting Total for Fct as 0 (where as expected val is 3).

Can someone help me on this?

Thanks,Pooja

• ###### Pivot Chart-Total calc Issue

Pooja,

since you don't use Time or timeEnter as dimension, you don't need to use the TOTAL<nbr> here, just

=count(DISTINCT timeEnter)

should be enough.

For the Fct column, the TOTAL is evaluated as expression total in a pivot table, so the TOTAL expression is equivalent to if(5=1,1,0), resulting in 0, it is not a sum of rows expression.

You could either use a straight table chart with sum of rows as Total mode in expression tab, or stick with the pivot and use advanced aggregation to get your sum of rows in the TOTAL line (check out the Help, searching for sum of rows in pivot table using advanced aggregation):

=sum(aggr(if(count(DISTINCT Time)=1,1,0),NBR))

Hope this helps,

Stefan

• ###### Re: Pivot Chart-Total calc Issue

Hi Pooja,

What Swuehl said is absolutely correct, since there is a single dimension using straight table is better option, by using the "Sum of Rows" option the Total would be corrected or else we need to use Sum and AGGR function in Pivot table.

Please check the attached file, it is similar to what Swuehl has suggested.

Regards,

Jagan.

• ###### Pivot Chart-Total calc Issue

Thanks to both of you for your help on this and for the explanation.