topic Re: The sum function in aggregation is giving wrong results in App Development

The sum function in aggregation is giving wrong results

pashok75 — Thu, 06 Apr 2023 06:36:24 GMT

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.

Re: The sum function in aggregation is giving wrong results

Mark_Little — Thu, 06 Apr 2023 06:52:29 GMT

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.

Re: The sum function in aggregation is giving wrong results

pashok75 — Thu, 06 Apr 2023 06:55:50 GMT

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

Re: The sum function in aggregation is giving wrong results

Sabrina_V — Thu, 06 Apr 2023 07:00:41 GMT

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))

Re: The sum function in aggregation is giving wrong results

pashok75 — Thu, 06 Apr 2023 07:19:16 GMT

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

Re: The sum function in aggregation is giving wrong results

Sabrina_V — Thu, 06 Apr 2023 07:23:41 GMT

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)

Re: The sum function in aggregation is giving wrong results

pashok75 — Thu, 06 Apr 2023 07:26:45 GMT

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

Re: The sum function in aggregation is giving wrong results

pashok75 — Thu, 06 Apr 2023 07:28:55 GMT

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

Re: The sum function in aggregation is giving wrong results

Sabrina_V — Thu, 06 Apr 2023 07:53:15 GMT

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 ?

Re: The sum function in aggregation is giving wrong results

pashok75 — Thu, 06 Apr 2023 07:57:45 GMT

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

Re: The sum function in aggregation is giving wrong results

Sabrina_V — Thu, 06 Apr 2023 08:08:12 GMT

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

Re: The sum function in aggregation is giving wrong results

pashok75 — Thu, 06 Apr 2023 08:18:17 GMT

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

Re: The sum function in aggregation is giving wrong results

Sabrina_V — Thu, 06 Apr 2023 08:21:55 GMT

Ok great 😀

Could you indicate your problem as solved ?

Have a nice day

Re: The sum function in aggregation is giving wrong results

Hania — Thu, 06 Apr 2023 09:09:05 GMT

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!