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.