Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
Thanks to both of you for your help on this and for the explanation.