Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

Use RangeSum(count(Model) * Cost)

Hope it helps

Celambarasan

nilesh_gangurde
Partner - Specialist
Partner - Specialist

Hii Nisha,

use the following expression.

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


This will consider the null values as 0.


-Nilesh

Not applicable
Author

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
Author

try this

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

CELAMBARASAN
Partner - Champion
Partner - Champion

Can you attach the sample app?

Not applicable
Author

Hello Vishwaranjan,

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

rlp
Creator
Creator

Try this:

sum(

     aggr(

          sum( alt( cost,  0 ) )

          ,

          Model    

     )

)

Not applicable
Author

Doesnt help much.

Not applicable
Author

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