topic Pivot Table - Incorrect Partial Sum in QlikView

Pivot Table - Incorrect Partial Sum

Fri, 14 Mar 2014 20:15:38 GMT

Hi,

I was using Pivot table to do some calculations. But it does return correct partical sum for me. Please refer to below table. In the "Total" row, it gives me a number which is not equal to the real sum of "Total Cost" column.

In the "Total Cost" column, my code is like: sum(Item Cost) * count(Item) and I tried sum( total <Item> Item Cost) but didn't work. Can anyone help me on it? Thanks!

AREA	Item	Item Cost	*Total Cost = Item Cost Unit**	Unit
West	A	$13,581	$13,581	1
West	B	$27,303	$27,303	1
West	C	$113,845	$113,845	1
West	D	$13,480	$13,480	1
West	E	$10,000	$20,000	2
West	F	$35,625	$249,375	7
West	G	$5,864	$5,864	1
West	H	$207,494	$207,494	1
West	I	$5,707	$11,414	2
West	J	$80,737	$80,737	1
West	K	$6,119	$6,119	1
West	L	$4,874	$24,370	5
West	M	$27,035	$27,035	1
West	N	$8,000	$32,000	4
West	O	$42,708	$42,708	1
West	P	$10,000,000	$1,120,000,000	112
Total			$1,508,668,766	142

Re: Pivot Table - Incorrect Partial Sum

jerem1234 — Fri, 14 Mar 2014 20:27:19 GMT

This is because it is not summing the rows, but calculating total Item Cost, then multiplying by the total Count. You want the sum of rows, in this you need different formula for total. You'll need aggr and dimensionality like:

if(dimensionality() =1, sum([Item Cost]) * sum(Unit), sum(aggr(sum([Item Cost]) * sum(Unit), Item)))

This is one that works in my example. For yours, maybe:

if(dimensionality() =1, sum(Item Cost) * count(Item), sum(aggr(sum(Item Cost) * count(Item), Item)))

Please find attached for example.

Hope this helps!

EDIT: You should be able to get away with just:

sum(aggr(sum(Item Cost) * count(Item), Item))

Re: Pivot Table - Incorrect Partial Sum

maxgro — Fri, 14 Mar 2014 20:34:24 GMT

sum([Item Cost]*Unit)