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

    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
          Stefan Wühl

          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
            jagan mohan rao appala

            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.