2 Replies Latest reply: Jan 20, 2014 12:30 PM by Manish Chauhan RSS

    Multiple Totals on Dataset

    Manish Chauhan

      I have a PivotTable Chart with dimension Year, Month, Fund, Sector and expression of AvgCapLong and AvgCapShort. I want to show partial sums at the Year, Month, Fund and Sector level.


      Currently the Expression for AvgCapLong is simply SUM(AvgCapLong). I calculate the AvgCap in the database, hence it's SUM rather than Avg.


      I want to see the following partial sums:


      At the Sector level (dimensionality=4) : SUM(AvgCapLong) - This works fine.


      At the Fund level (dimensionality=3): SUM(AvgCapLong) - This works fine also. This is the Sum of average across sectors.


      At the Month Level (dimensionality=2): SUM(AvgCapLong) - Perfect. This is the Sum across Funds.


      At the Year level (dimensionality=0): SUM(AvgCapLong). Problem. I want the AVERAGE of the Monthly Sum of AvgCapLong. The Average should be the average of monthly sum(avgCapLong). How do I put this in the expression?


      I tried - but it didn't like the expression.


      IF(Dimensionality()=0, avg( {$<Dimensionality()=2}> } SUM(ACR_AvgCap_DALong))/1000000, Sum(ACR_AvgCap_DALong)/1000000)