Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two sets of data like this
Ref Data:
Field1, Field2, Field3, Amt1, Amt2
A, M, P1, 2, 1
A, M, P2, 2, 1
A, F, P3, 2, 1
A, F, P4, 2, 1
A, I, P5, 2, 1
A, I, P6, 2, 1
B, M, P2, 2, 2
B, M, P7, 2, 2
B, I, P5, 2, 2
B, F, P3, 2, 2
C, M, P2, 2, 3
C, M, P7, 2, 3
C, I, P5, 2, 3
C, F, P3, 2, 3
Now there is a listbox or multibox where the user picks the Field3.
The requirement is if all the Field3 related to Field1 is picked then we need to take the avg of the amt1 and amt2 and sum it. For example if from the list box if I pick P1,P2,P3,P4,P5,P6 then it should take average sum of amt1 and amt2 for "A" in Field1. Else it should consider 0.
Also if I pick P2,P7,P5,P3 then it should take avg sum of amt1 and amt2 for "B" and "C" and sum it together to give one value.
How do I achieve this considering this ref data can be 1000s of lines and the listbox choice can range anywhere from 1 to all of them.
I thought of "If Statement" but that wont work as the no of choices made in listbox is totally random.
Can you kindly help me in this please. I am not sure how to progress.
Thanks in advance
Hi,
Do you want like the below screen shot
if so, find the attachment.
First of all thanks a lot. This pretty much looks like what I want.
Can you please explain the statement used in that chart. Also can I use that statement in a variable which sums all the results of that expression ?
Hi,
yes, i think you can set the variable for that like
var1: Concat(DISTINCT Field3,'')=Concat({1}DISTINCT Field3,'')
var2: rangesum(avg(Amt1),avg(Amt2))
in the expression,
if($(var1),$(var2),0)
just i had concat the selection based 'Field3' values, and concat the overall ({1}) 'Field3' values. if it is match, then we can show the result.
Adapt the fields, based on your requirement.
Hi
Is there way to default it to zero? Meaning if I dont select any field3 value it should be zero not sum of everything.
Hi,
put this in expression
=if(GetSelectedCount(Field3) and
Concat(DISTINCT Field3,'')=Concat({1}DISTINCT Field3,''),rangesum(avg(Amt1),avg(Amt2)),0)
Thanks a lot Settu. It worked like magic.