4 Replies Latest reply: Dec 9, 2011 2:54 PM by Pooja Nanda RSS

    aggr function in pivot table (not working for Null values)

      Hi,

       

      I have a pivot table like this

       

      CTypeStatusVal%
      Xaccept514.3%
      reject1028.6%
      pending2057.1%
      Status Sub total35100%
      Yaccept4521.5%
      reject4019.0%
      pending12559.5%
      Status Sub Total210100 %
      -accept360%
      pending120%
      Status Sub Total480%
      TOTAL250

       

      CType and Status are the dimensions. Val and % are the expressions.

      For the sub total calculatation I am using Partial Sum.

       

      For Val, I am using

       

      count(DISTINCT {$<EType={'SAS'}>}ENbr)

       

      For the % calculation I am using following

       

      Val/

      aggr(nodistinct count({$<EType={'SAS''}>}ENbr),EType,CType)

       

      When Ctype is NULL,the calculation becomes incorrect for % calculation.Though in the above example partial sum for CType where Null is 4 but in the % calculation the aggr function returns as 5,so the % calculation is wrong.Can anyone help on this?I am not sure why aggr function not working properly when Ctype is Null?

       

      Second question,how to get the % value for TOTAL?

       

       

       

      Thanks,Pooja