topic Sum function returning average (pivot table) in QlikView

Sum function returning average (pivot table)

Mon, 22 Aug 2011 17:00:59 GMT

Hello,

I have the below function in a pivot table to calculate the ARS (average rate of sale) at higher levels.

However, when the ARS is subtotaled, the value is not the sum of all value. Instead, it recalculates in the subtotal line.

I know i can switch to a straight table and select the sum of rows option. Is there anyway to achieve the same result in a pivot table?

sum (ars * net sales) / sum (net sales)

Thanks,

Sara

swuehl — Mon, 22 Aug 2011 17:16:20 GMT

Hi Sara,

I think it should be possible using advanced aggregation. There is an explanation and examples in the help file:

"Sum of rows in pivot tables"

Your expression could look like:

= sum(aggr(Sum(ars * [net sales]),YOURDIMENSION)) / sum(aggr(sum([net sales]), YOURDIMENSION))

Regards,

Stefan

edit: You probably don't need the sum(aggr(...)) for the second part, if the evaluation as total expression is fine here.

edited by swuehl

Mon, 22 Aug 2011 17:32:06 GMT

That returned the same results. the total for vendors b,c,d,e should be 6.5648.

I've also tried this expression, which i got from another post...the only difference is that it returned 0 subtotals.

rangesum(above(sum(ars * net sales) / Sum(net sales),0, rowno(Total)))

swuehl — Mon, 22 Aug 2011 17:36:16 GMT

How do you group your Vendors? Do you use another dimension not shown here?

Could you post your qvw file here or a sample that reproduces your problem?

Mon, 22 Aug 2011 17:38:04 GMT

Vendors are grouped by department, departments by group, and groups by divisions.

swuehl — Mon, 22 Aug 2011 17:53:09 GMT

Try this:

=sum(aggr(sum([Net Sales]*ARS) /sum( [Net Sales]),Vendor,Department,Group,Division))

edit:

=sum(aggr(sum([Net Sales]*ARS) /sum( [Net Sales]),Vendor))

should be enough, I think

edited by swuehl

Mon, 22 Aug 2011 18:13:00 GMT

Perfect, that worked. Thanks sooo much!!!