The sum function in aggregation is giving wrong results

[pashok75]

I am facing an issue wherein the sum function is returning wrong values. 

The first part is the raw data

Product	Product Name	Sub Product	Sub Product Name	Collection
82	xxx	8163	a	3,335.64
82	xxx	8163	a	(3,638.08)
82	xxx	8163	a	129,232.60
			Grand Total	128,930.16
This is the output for the pivot while using expression
Product	Sub Product Name	Collection
82	8163	258,162.76
		258,162.76

Dimensions

Product

Sub Product Name

Expression 

sum(collection)

What I am doing wrong.

[Sabrina_V]

Could you just try loading the data with a filter in the script?

Product	Sub Product Name
82	8163

An tell me how many lines do you load 

[pashok75]

HI

Got the problem. In the Table I see duplicate records creating this confusion. Its the load issue. 

Thank you so much for assistance. Sum should work as intended. Due to data duplicate in load values it is happening. Thank you everyone for your kind support.

Ashok

[Sabrina_V · Accepted solution]

Ok great 😀

Could you indicate your problem as solved ?

Have a nice day

[Hania · Accepted solution]

Hi

when you use sum(collection )  then you will get 136206.32 value.

as you have shown your grand total 128,930.16  , here the value of collection (3,638.08) gets double, 

may be this because of the frequency is double for  this particular data .

if this is not the case then convert this data into number format  by applying  below expression:  

load PurgeChar(Collection,'()') as Collection  in script .

Hope this helps,

help user find answers ! don't forget to mark  a solution that work for you and click the like button!