Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Incorrect Subtotals in Pivot Table

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

                     A

               

                    b                            e                          14

ABC           e                             f                           12

                     e

                   F

   XYZ           a

                b            

Can you please help me to get proper subtotals.

9 Replies

Re: Incorrect Subtotals in Pivot Table

Hi,

Use RangeSum(count(Model) * Cost)

Hope it helps

Celambarasan

nilesh_gangurde
Valued Contributor

Re: Incorrect Subtotals in Pivot Table

Hii Nisha,

use the following expression.

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


This will consider the null values as 0.


-Nilesh

Not applicable

Re: Re: Incorrect Subtotals in Pivot Table

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

Not applicable

Re: Incorrect Subtotals in Pivot Table

try this

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

Re: Incorrect Subtotals in Pivot Table

Can you attach the sample app?

Not applicable

Re: Incorrect Subtotals in Pivot Table

Hello Vishwaranjan,

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

richard_pressan
Contributor

Re: Re: Incorrect Subtotals in Pivot Table

Try this:

sum(

     aggr(

          sum( alt( cost,  0 ) )

          ,

          Model    

     )

)

Not applicable

Re: Incorrect Subtotals in Pivot Table

Doesnt help much.

Not applicable

Re: Incorrect Subtotals in Pivot Table

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

Community Browser