20 Replies Latest reply: Jul 19, 2012 6:20 AM by Rahul Lakhina

# Sum Error in Charts

hi,

I have three columns..

 A B Amount 1000 25 50 32 1000 82 1500 78 90 1300 89 54 25 1300 60

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.

• ###### Re: Sum Error in Charts
ABAmount=
10002550132
32100082132
1500789090
13008954114
25130060114

right?

in this case?

ABAmount=
100014003196?
1400783296?
8910003396?
• ###### Re: Sum Error in Charts

the second case can never happen cause for each unique number in column A, there can be only on corresponding line item in B.

• ###### Re: Sum Error in Charts

Hi,   Try   (Amount)+FieldValue('Amount',FieldIndex('B',sum(A)))  Please see file attached.   Regards  Rahul

• ###### Re: Sum Error in Charts

Thanks.. It surely works.. but the first row has a value 110.. Why is that?

Thanks & Regards,

Shilpa

• ###### Re: Sum Error in Charts

A 25 =60 B 25 =50  Therefore value equals 60+50 = 110

• ###### Re: Sum Error in Charts

"the second case can never happen cause for each unique number in column A, there can be only on corresponding line item in B." is not correct...?

• ###### Re: Sum Error in Charts

Hi Rahul,

Your expression is giving me th required output but it is not working in my application.

• ###### Re: Sum Error in Charts

Hi Shilpa,   If possible, could you post an example?  Regards  R

• ###### Re: Sum Error in Charts

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

• ###### Re: Sum Error in Charts

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

• ###### Re: Sum Error in Charts

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

• ###### Re: Sum Error in Charts

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

• ###### Re: Sum Error in Charts

It is working correctly.. thanks all..

• ###### Re: Sum Error in Charts
NameABTotal
ABC1000442550
ABC32100082
GHY15007890

LKJ

1300448954
LKJ25130060
ABC16005699

• ###### Re: Sum Error in Charts

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

• ###### Re: Sum Error in Charts

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

• ###### Re: Sum Error in Charts

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

• ###### Re: Sum Error in Charts

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?

• ###### Re: Sum Error in Charts

You can change the dimension till the dimension is Unique if not you will have to aggr the expression over a unique field, if you do not have a unique field you can aggr it over A as that is a unique field.

Regards

R

• ###### Re: Sum Error in Charts

Hi,

Can you do an applymap function in the script level between A & B to get the amount correspondingly ,

and call this calculated amount  in the expression directly

Regards,

Kiruthiga