6 Replies Latest reply: Jun 16, 2015 2:11 AM by Madhu B

# Comparing two data sets

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.

• ###### Re: Comparing two data sets

Hi,

Do you want like the below screen shot

if so, find the attachment.

• ###### Re: Comparing two data sets

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 ?

• ###### Re: Comparing two data sets

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.

• ###### Re: Comparing two data sets

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.

• ###### Re: Comparing two data sets

Hi,

put this in expression

=if(GetSelectedCount(Field3) and

Concat(DISTINCT Field3,'')=Concat({1}DISTINCT Field3,''),rangesum(avg(Amt1),avg(Amt2)),0)

• ###### Re: Comparing two data sets

Thanks a lot Settu. It worked like magic.