Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ?
Ok great 😀
Could you indicate your problem as solved ?
Have a nice day
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!
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.
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
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))
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
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 :
And measure :
Thats a good idea. I will try that to see the duplicates with adding sum as well
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
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 ?
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