topic Re: pivot table not showing correct Total(partial sum) in QlikView

pivot table not showing correct Total(partial sum)

Thu, 15 Nov 2012 09:07:10 GMT

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

Re: pivot table not showing correct Total(partial sum)

Gysbert_Wassenaar — Thu, 15 Nov 2012 09:19:33 GMT

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.

Re: pivot table not showing correct Total(partial sum)

Thu, 15 Nov 2012 09:48:24 GMT

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

Re: pivot table not showing correct Total(partial sum)

its_anandrjs — Thu, 15 Nov 2012 10:16:04 GMT

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

Re: pivot table not showing correct Total(partial sum)

Thu, 15 Nov 2012 10:19:12 GMT

No Anand it did not worked...

Re: pivot table not showing correct Total(partial sum)

its_anandrjs — Thu, 15 Nov 2012 11:05:13 GMT

Hi Harleen,

Can you provide any sample for this.

Regards,

Anand

Re: pivot table not showing correct Total(partial sum)

kamalqlik — Thu, 15 Nov 2012 11:22:00 GMT

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

Re: pivot table not showing correct Total(partial sum)

its_anandrjs — Thu, 15 Nov 2012 11:29:03 GMT

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

Re: pivot table not showing correct Total(partial sum)

Thu, 15 Nov 2012 13:57:14 GMT

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

Re: pivot table not showing correct Total(partial sum)

Sun, 17 Mar 2013 14:06:08 GMT

Hi All

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