Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Pivot Table - Incorrect Partial Sum


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!

AREAItemItem Cost Total Cost = Item Cost *UnitUnit
WestA$13,581$13,5811
WestB$27,303$27,3031
WestC$113,845$113,8451
WestD$13,480$13,4801
WestE$10,000$20,0002
WestF$35,625$249,3757
WestG$5,864$5,8641
WestH$207,494$207,4941
WestI$5,707$11,4142
WestJ$80,737$80,7371
WestK$6,119$6,1191
WestL$4,874$24,3705
WestM$27,035$27,0351
WestN$8,000$32,0004
WestO$42,708$42,7081
WestP$10,000,000$1,120,000,000112
Total $1,508,668,766142
1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

Re: Pivot Table - Incorrect Partial Sum

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))

2 Replies
jerem1234
Valued Contributor II

Re: Pivot Table - Incorrect Partial Sum

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))

MVP
MVP

Re: Pivot Table - Incorrect Partial Sum

sum([Item Cost]*Unit)

Community Browser