Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
I have field called Item. This Item is an Island
Again Each Item is a Separate field.
Ex:
If I select Item Ball, I need Sum(Ball) =30
If I select Item Pen, I need Sum(Pen) = 70
If I select both Ball and Pen, I need Sum(Ball)+ Sum(Pen) = 100 or Sum(Ball+Pen) =100
like wise if I select all the items, I need sum of all the items to be added.
How can I achieve this?
Thanks in advance.
Regards
Manideep
Try it with this concept may be:
Expression:
=Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 1))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 1)))) +
Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 2))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 2))))
Output when nothing is selected:
When Pen is selected:
When both are selected:
Attaching the qvw for reference.
HTH
Best,
Sunny
I performed the above for just two items, but it can be expanded for all the Items in your list:
Expression:
=Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 1))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 1)))) +
Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 2))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 2)))) +
Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 3))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 3)))) +
and so on....
Like in attached sample?
=Sum($(=GetFieldSelections(Item,'+')))
Hi sunindia
Thanks for the quick response.
Instead of writing one expression for 1 item, I would like to make it dynamic, because I don't know how many Items I will have, they will be added dynamically.
So each and every time if the item is added I need to change the expression which is not possible.
Is there any alternative way in getting this?
Regards,
Manideep
I would look at what tresesco has proposed. The only issue I saw was when you select both Ball and Pen it gave me a sum of 200 instead of 100, but I cannot open his attached qvw in which he might have fixed the issue.
For not knowing how many items there could be, you can always create 200 of the above expressions, because it will default to 0 when there are no item
But I guess we can definitely look for a better solution
Best,
Sunny
Hi tresesco
This is nearer to my requirement
I tried this, But this not working when my Item contains 2 or 3 spaces. and I should not remove those spaces.
Ex: Compass Box
I am getting an error message.
PFA!!!
=Sum($(='['&GetFieldSelections(Item,']+[')&']'))
Thanks tresesco
It worked for me.
Hi tresesco
If there is 0 any individual item field, the total sum getting 0. Can you let me know how can I overcome this?
Thanks in advance.
Regards
Manideep