topic Re: Set Analysis Issue - Comparing two Fields in QlikView

Set Analysis Issue - Comparing two Fields

shumailh — Wed, 06 Jul 2011 12:18:34 GMT

I am trying to compare two fields and unable to get the required value, below is my data:

AccNo	MonthName_ID	MonthNameMTD_ID	MonthNameyTD_ID	MonthNameYOY_ID	Bal
12456	Jan-11	Dec-10	Jan-11	Feb-10	154,100.00
15144	May-11	Apr-11	Jan-11	Jun-10	10,054.00
65112	Jun-11	May-11	Jan-11	Jul-10	320,496.00
41120	Nov-10	Sep-10	Jan-10	Dec-09	12,054.00
45661	Apr-11	Mar-11	Jan-11	May-10	21,560.00
57482	Jun-11	May-11	Jan-11	Jul-10	1,541.00
78441	May-11	Apr-11	Jan-11	May-10	540,000.00

Below is the expression i am using to get the bal

=(sum({$<MonthName_ID = ${MonthNameMTD_ID}>} bal)

Shumail

Set Analysis Issue - Comparing two Fields

erichshiino — Wed, 06 Jul 2011 12:40:04 GMT

Try this:

=(sum({$<MonthName_ID ={"=$(MonthNameMTD_ID)"}>} bal)

Hope this helps,

Erich

Set Analysis Issue - Comparing two Fields

shumailh — Wed, 06 Jul 2011 13:00:26 GMT

not working i tried the same after correction but still no solution

=sum({$<MonthName_ID ={"=$(MonthNameMTD_ID)"}>} bal)

I review the below link which mentions that i could nt compare two fields in set analysis... [:S]

http://community.qlik.com/message/101556#101556

Re: Set Analysis Issue - Comparing two Fields

erichshiino — Wed, 06 Jul 2011 14:06:47 GMT

Hi,

I changed the syntax a little bit

Please, check attachment

Regards,

Erich

Re: Set Analysis Issue - Comparing two Fields

vikasgupta — Sat, 05 Jan 2013 11:47:13 GMT

Hi Erich

I wants to compare two fields using set expression. I used your expression but i am not getting required result its return 1000 instead of 700 so i wants to ask you that we can not use set expression for comparing two fields ?

but if we are using IF condition then its working correct.

plz finds the attached for same and if if any alternative is there then plz let me know .

Thanks

Vikas gupta

Re: Set Analysis Issue - Comparing two Fields

Tue, 30 Sep 2014 19:20:22 GMT

Thanks!!! I was searching for this solution!

Re: Set Analysis Issue - Comparing two Fields

maxgro — Tue, 30 Sep 2014 19:38:42 GMT

=sum( {$ <AccNo={"=MonthName_ID=MonthNameyTD_ID"}>} Bal )

Re: Set Analysis Issue - Comparing two Fields

Tue, 06 Oct 2015 01:03:14 GMT

I don't understand why "1000" is returned. We're comparing fields (Table1.Name and Table2.Name) from separate tables which have no join (i.e. joinless tables -- no line between them in the editor) Since there is no join, maybe the comparison is moot. (Qlikview just does the cartesian product when you mix joinless tables) And since you're summing Table2.Amt, it just checks whether Table2 is filtered at all and accepts that filter...

Re: Set Analysis Issue - Comparing two Fields

rcurlewis — Sun, 29 Oct 2017 11:13:49 GMT

Hi Shumail,

My suggestion is to look at the P/E options in SET ANALYSIS. It works when you want to say P = Possible Values a E = Exclude Values.

I use it as follows as an example: Sum({<[Type]= {'Value'}, [Field1]= P(Field2)>} [Amount]). Note the Round Brackets with P/E as these are functions.

Your expression: =(sum({$<MonthName_ID = P(MonthNameMTD_ID)>} bal)

Hope this helps

*Sorry just a note. This is taking every Possible Value and not just the value - so test it as this resolved quite a number of my issues in the past*

According to Rob Wunderlich you can also do this: sum({<RecId={"=F1=F2"}>}Amount)