topic Re: Comparing two data sets in QlikView

Comparing two data sets

Tue, 16 Jun 2015 01:15:49 GMT

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

Re: Comparing two data sets

settu_periasamy — Tue, 16 Jun 2015 02:34:35 GMT

Hi,

Do you want like the below screen shot

if so, find the attachment.

Re: Comparing two data sets

Tue, 16 Jun 2015 03:45:24 GMT

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

settu_periasamy — Tue, 16 Jun 2015 03:56:10 GMT

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.

Re: Comparing two data sets

Tue, 16 Jun 2015 04:58:26 GMT

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

settu_periasamy — Tue, 16 Jun 2015 05:09:11 GMT

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

Tue, 16 Jun 2015 06:11:52 GMT

Thanks a lot Settu. It worked like magic.