topic Re: The show total function in Pivot table is not correct in QlikView

The show total function in Pivot table is not correct

Tue, 19 Feb 2013 02:31:46 GMT

Hi All,

I have one table:

load *

Inline

[Category,Revenue

'A',0

'B',100

'C',200

];

And I use one pivot table to show the revenue. However, as you can see, there is no revenue for A category, so I assigned the revenue from B to A with the expression by if(Category='A',sum({<Category={'B'}>}total Revenue),sum(Revenue))

It works well, but the total number is not right, the total number should be 400 not 300.

I have attached the QVW, could you help me out?

Thanks.

Re: The show total function in Pivot table is not correct

Anonymous — Tue, 19 Feb 2013 02:38:53 GMT

Heya Isaac,

The value on the Total row will be calculated just like for the other dimension values just that the Category value is null for this row. This means that the Category <> A and the total will only calculate the sum(Revenue) part which is 300.

You can use aggr() to create the "sum of rows" functionality for a pivot table that you're looking for.

Try this:

sum(aggr(if(Category='A',sum({<Category={'B'}>}total Revenue),sum(Revenue)), Category))

Re: The show total function in Pivot table is not correct

Tue, 19 Feb 2013 02:48:41 GMT

Thanks fore your correct answer, I am Isaac Li.

Re: The show total function in Pivot table is not correct

Anonymous — Tue, 19 Feb 2013 02:50:23 GMT

I know buddy. Say hi to Stanley and good luck with the development!

Cheers,

Johannes