topic Re: Incorrect Subtotals in Pivot Table in QlikView

Incorrect Subtotals in Pivot Table

Wed, 28 Aug 2013 09:45:31 GMT

I have two tables

Table A

Country

Model

Table B

Model ( Few Models )

Cost

When I Left Join Table A with B for some Model which are not in table B Cost is
null. And when I use Pivot table to calculate count(Model)*Cost
and try to get subtotals I get 0.

Table A Tableb

Country Model Model Cost

A a 10

b e 14

ABC e f 12

XYZ a

Can you please help me to get proper subtotals.

Re: Incorrect Subtotals in Pivot Table

CELAMBARASAN — Wed, 28 Aug 2013 09:50:09 GMT

Hi,

Use RangeSum(count(Model) * Cost)

Hope it helps

Celambarasan

Re: Incorrect Subtotals in Pivot Table

nilesh_gangurde — Wed, 28 Aug 2013 09:53:31 GMT

Hii Nisha,

use the following expression.

count(Model)* if(isnull(Cost),0,Cost)

This will consider the null values as 0.

-Nilesh

Re: Re: Incorrect Subtotals in Pivot Table

Thu, 29 Aug 2013 03:52:23 GMT

Thanks Nilesh, I have already tried this but not getting the desired result.

Please find the result which I get in the Pivot table.

For some country I get the subtotal which is random and for tother even the subtotal is missing.

@Thanks,

Nisha

Re: Incorrect Subtotals in Pivot Table

Thu, 29 Aug 2013 05:46:49 GMT

try this

count(Model) * if(Cost=' ',0,Cost)

Re: Incorrect Subtotals in Pivot Table

CELAMBARASAN — Thu, 29 Aug 2013 06:07:59 GMT

Can you attach the sample app?

Re: Incorrect Subtotals in Pivot Table

Thu, 29 Aug 2013 06:11:23 GMT

Hello Vishwaranjan,

Thanks. Its giving me correct total but the partial subtotals are still 0.00 and for some its random number.

Re: Re: Incorrect Subtotals in Pivot Table

rlp — Thu, 29 Aug 2013 09:30:25 GMT

Try this:

sum(

aggr(

sum( alt( cost, 0 ) )

Model

)

Re: Incorrect Subtotals in Pivot Table

Fri, 30 Aug 2013 02:38:31 GMT

Doesnt help much.

Re: Incorrect Subtotals in Pivot Table

Fri, 30 Aug 2013 05:32:40 GMT

use aggr function by declaring the field u want the aggregation for.... tht will solve the problem