Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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..
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
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..
=if(FieldIndex('B',sum(A))=0,if(FieldIndex('A',sum(B))=0,Bal), Bal + FieldValue('Bal',FieldIndex('B',sum(A))))
It is working correctly.. thanks all..
Name | A | B | Total |
---|---|---|---|
ABC | 1000 | 4425 | 50 |
ABC | 32 | 1000 | 82 |
GHY | 1500 | 78 | 90 |
LKJ | 1300 | 4489 | 54 |
LKJ | 25 | 1300 | 60 |
ABC | 1600 | 56 | 99 |
If i add a new field "name" as a dimension for the expression above, values are not matching. Please help.
Dear Shilpa, How are you? Could you please post an example? Regards R
Please find the example attached.. the expression should ideally work for all dimensions.. bt my example is not giving values
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
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?