Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Thanks in advance

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

Hi,

Do you want like the below screen shot

1.JPG

2.JPG

if so, find the attachment.

View solution in original post

6 Replies
settu_periasamy
Master III
Master III

Hi,

Do you want like the below screen shot

1.JPG

2.JPG

if so, find the attachment.

Not applicable
Author

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 ?

settu_periasamy
Master III
Master III

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.

Not applicable
Author

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.

settu_periasamy
Master III
Master III

Hi,

put this in expression

=if(GetSelectedCount(Field3) and

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

Not applicable
Author

Thanks a lot Settu. It worked like magic.