Pivot Chart-Total calc Issue

[#1]

Hi,

Raw data looks like this

NBR	Time	status
100	10/15/2011 11.30PM	done
100	10/15/2011 11.35PM	done
101	10/15/2011 11.36PM	done
102	10/15/2011 11.37PM	done
103	10/15/2011 11.38PM	done

Now in the pivot table

NBR	Ct	Fct
100	2	0
101	1	1
102	1	1
103	1	1
TOTAL	5	0

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

[swuehl · Accepted solution]

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

[jagan]

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.

[#4]

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