Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum Error in Charts

hi,

I have three columns..

ABAmount
10002550
32100082
15007890
13008954
25130060

I need to sum up the Amount when Column A = Column B. eg: First 2 rows, 1000 is in both columns but has different adjacent values, so i have to sum up 50 + 82 and similarly Row 4 and 5, since 1300 is in both columns need to sum up, 60 + 54.  whereas row 3, is a single unique line item and that remains the same. Plz help me find the right expression.

20 Replies
Not applicable
Author

hi.. with the expression you have given, i am getting correct values for those which are in both columns, but for the unique values.. I am not getting values.

And second thing, when i try with 5 line items it works.. but with Larger number of items and different names it doesnt work..

Not applicable
Author

use the following syntax for amount and it should work:   =if(FieldIndex('B',sum(A))=0,Bal, Bal + FieldValue('Bal',FieldIndex('B',sum(A))))  Regards  Rahul

Not applicable
Author

Now it is giving me all the values.. even for the single items.. which should not be the case na... In my QVW, i should have only 3 values..

Not applicable
Author

=if(FieldIndex('B',sum(A))=0,if(FieldIndex('A',sum(B))=0,Bal), Bal + FieldValue('Bal',FieldIndex('B',sum(A))))

Not applicable
Author

It is working correctly.. thanks all..

Not applicable
Author

NameABTotal
ABC1000442550
ABC32100082
GHY15007890

LKJ

1300448954
LKJ25130060
ABC16005699

If i add a new field "name" as a dimension for the expression above, values are not matching. Please help.

Not applicable
Author

Dear Shilpa,   How are you? Could you please post an example?   Regards  R

Not applicable
Author

Please find the example attached.. the expression should ideally work for all dimensions.. bt my example is not giving values

Not applicable
Author

Dear Shilpa, 

Try the following: 

aggr(if(FieldIndex('B',sum(A))=0,if(FieldIndex('A',sum(B))=0,Bal), Bal + FieldValue('Bal',FieldIndex('B',sum(A)))),[Doc. No.])

It should do the trick. 

The reason why it was not working is because your Customer Name is not unique, therefore we are aggr this syntax over a unique field now. (Asuming your Doc No is a unique field)

Regards Rahul Lakhina

Not applicable
Author

thanks it is working.. but one doubt.. if i do not want Doc no and i want any other dimension.. or no other dimension,.. then?