Partial sums incorrect

[#1]

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

[sureshbaabu · Accepted solution]

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

[#3]

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

[sureshbaabu]

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