topic Re: Partial sums incorrect in QlikView

Partial sums incorrect

Tue, 16 Oct 2012 11:00:23 GMT

Monthname	toy	Balls	Bats	Dolls	Bikes	Total	Excel totals
January		143	57	21	27	246	248
February		102	24	3	38	167	167
March		106	33	2	30	170	171
April		96	16	4	23	139	139
May		99	22	27	26	174	174
June		88	13	1	24	126	126
July		120	25	8	31	184	184
August		45	14	2	16	77	77
September		71	17	42	28	158	158
October		51	11	24	17	103	103
Total		921	232	134	260	1544

excel totals		921	232	134	260

Dear All,

Ive created a very simple pivot which shows the amount of toys bought over the course of a year or year to date.

using 1 dimension - monthname

and 1 expression - count(distinct toy)

All of the internal figures in the table are correct and the sub totals at the bottom in the row 'Total' are also correct but as the sub totals read across the table they calculate incorrectly, seen by coumn 'Total'

the additional column and row excel totals, ive added to show the differences.

this table has no 'If' statement and there are no synthetic keys in any table but im confused as to why certain partial sums are showing/ adding incorrectly, can anyone shed any light.

sorry but i cant add the qvw as its part of a larger company sheet

many thanks

Re: Partial sums incorrect

sureshbaabu — Tue, 20 Nov 2012 09:53:41 GMT

Hello,

I faced the same issue today and I found the solution for the same.

I'm not sure if you have fixed the issue already. Thought it can help you

The incorrect total is beacuse of the duplicate records in the data.

The pivot table will not show these records more than once, but they will all be included in the sum.

Solution: we have to use the aggregator function (‘aggr’) to create correct partial sums

Syntax: Sum(Aggr(original expression,Dimension1,Dimension2))

For your example: Sum(Aggr( count(distinct toy),monthname))

Let me know if that works for you!!!

Thanks

Re: Partial sums incorrect

Thu, 22 Nov 2012 13:59:31 GMT

Hi Suresh

Yes that worked thank you. I thought it may be a duplicate issue , hence the distinct count. Im curious why the figures went up when the correct equation was put in.

If the distinct figures are not in the pivot but are in the totals , wouldnt the totals be higher than what the actual figures are.

Despite not understanding why the figures went up I am thankful its adding correctly now

Re: Partial sums incorrect

sureshbaabu — Thu, 22 Nov 2012 14:53:49 GMT

Hello,

I had gone thorough some discussion and the following could be the reason.

Pivot tables don't do a sum of rows, but rather re-evaluate your expression for the total row. When doing the full total- the expression will be applied for the total column and it provided the results.

Hope this clarifies your question.

Thanks