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

pivot table not showing correct Total(partial sum)

Hii,

          is there is any issue with pivot table in QV??

When i change it to straight table sum is correct

but when i use pivot table sum gets wrong

what may be the problem and how to solve it??

thanks

Lavi

1 Solution

Accepted Solutions
Not applicable
Author

It is correct now . because i have changed the expression like this..

according to the guidance of Reference manual for pivot table.

sum(aggr(sum(if([Entry Type]=1,[Total Price]))/[Exchange Rate],Dimension1,Dimension2,Dimension3

thanks

View solution in original post

9 Replies
Gysbert_Wassenaar

It's possibly that you have duplicate records. The pivot table will not show these records more than once, but they will all be included in the sum. It's also possible you need to use the aggr function to create correct partial sums.


talk is cheap, supply exceeds demand
Not applicable
Author

Gysbert,

                 you may be right but when i select sum of rows in straight table then sum is correct and when i select expression total the sum is wrong(Same as pivot table)

this is the expression i am using

sum(if([Entry Type]=1,[Total Price]))/avg([Exchange Rate])*-1

there are 6 7 dimensions i am using.

thanks

Lavi

its_anandrjs

Hi Harleen,

Write expression some thing like and try with Distinct

sum( Distinct if([Entry Type]=1,[Total Price]))/avg([Exchange Rate])*-1

HTH

Regards,

Anand

Not applicable
Author

No Anand it did not worked...

its_anandrjs

Hi Harleen,

Can you provide any sample for this.

Regards,

Anand

kamalqlik
Partner - Specialist
Partner - Specialist

Hi Harleenji,

This is due to that there may be some null data with your records.

When you are using pivot it is showing the null data some to.

Go to the dimension tab and tick suppress when data is null.

Hope it helps.

Regards

Kamal

its_anandrjs

Hi Harleen,

Yes please check the dimensions that which column has a null values like by len( ) function and then select Suppress When Value Is Null option for perticular dimensio in the chart properties of the dimension and the check the sum total.

HTH

Regards,

Anand

Not applicable
Author

It is correct now . because i have changed the expression like this..

according to the guidance of Reference manual for pivot table.

sum(aggr(sum(if([Entry Type]=1,[Total Price]))/[Exchange Rate],Dimension1,Dimension2,Dimension3

thanks

Not applicable
Author

Hi All

I am also facing the similar problem. sub total does not work for aggr function. have you guys find any solution.?