topic Rangesum with aggr function in QlikView

Rangesum with aggr function

Fri, 04 May 2012 18:04:33 GMT

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 — Fri, 04 May 2012 19:22:26 GMT

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.