1 Reply Latest reply: May 4, 2012 3:22 PM by swuehl RSS

    Rangesum with aggr function

    Mario Diaz

      Hellow Masters.

       

      Can I use Aggr function in a expression with Rangesum like that.

       

      =RangeSum(Above(total sum({$<[Comision - Año]={$(=max(Año))}, [Comision - Division] = {'DIVISION I','DIVISION II','DIVISION III','DIVISION VII'}}>} [Comision - Ventas]),0,Rowno(total))) /

      RangeSum(Above(total sum({$<[Comision - Año]={$(=max(Año))}>} [Comision - Ventas]),0,rowno(total)))

       

      This expression give me a % of sales, but I need totalize this %'s in the totalize line of pivot table.

       

      Thanks Alot.

        • Rangesum with aggr function
          swuehl

          Should be possible, you want a sum of row? So try

           

          = sum( aggr( YourLengthyExpressionIncludingRangesums, YourPivottableDimensions))

           

          A possible problem is that aggr() will not take notice of your sort order used in the pivot table, it will always use load order for your aggr() dimension values. When using aggr() in combination with a chart inter record function like above(), you need to consider this, i.e. you might need to align the load order of your dimensions field with the sort order you want.