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.

[Mark_Little]

I can't say for sure,  but I would be looking for duplications in the data. 

Create a data table which should show unique data lines and add count() on a id field and confirm it is returning one. If not review the data model.

[pashok75]

Thanks for the response. 

I can see one record in the straight table and what I observe it is duplicating the value in the sum. But how this happens is confusing. In the data model there is no link to other tables as well. That is what confusing me?

Ashok

[Sabrina_V]

Hello,

If I'm right  the sum for Product = 82 and Sub Product Name = 8163 must be equal to : 

3,335.64 + (3,638.08) + 129,232.60 = 136,206.32

It's not equal to your value in the ligne Grand Total neither to your last lines.

In order to check if, you have some duplications, maybe try this formula :

Sum(Aggr(sum(distinct collection),Sub Product, Product))

[pashok75]

I tried this formula now the result is empty. This is my actual column names 

=Sum(Aggr(sum(distinct GNT_QS_PREM),GNT_Class_Code,GNT_SC_Code))

Formula is showing OK 

But no result set

Check the screenshot attached.

1. Expression Formula

2. Straight Table Data

[Sabrina_V]

can you try this : 

=Sum(Aggr(sum(distinct GNT_QS_PREM),GNT_SC_Code,GNT_Class_Code))

Another idea to find some duplicates values : 

Straight table with in dimensions : 

• GNT_SC_Code
• GNT_Class_Code
• GNT_QS_PREM

And measure : 

• count(GNT_QS_PREM)

[pashok75]

Thats a good idea. I will try that to see the duplicates with adding sum as well 

[pashok75]

I did that and found 2 count. That means qlik is duplicating. How come? there is only one record and what is causing this??

Ashok

[Sabrina_V · Accepted solution]

Qlik does not duplicate. You should load on the script only the data for the values you are analyzed and you will see if you load 1 or more lines.

Do you make some joining on the script ?

[pashok75]

There is no duplicate in the data. Data is fine. It only has one record. Qlik is duplicating. If I put a pivot in excel it works fine. When I say these are my columns to group why Qlik is considering other columns. I dont understand this?

Its a straight table and record is one only and there is no join to other table. 

Ashok