Set Analysis with multiple conditions

[#1]

Hello,

I'm starting to use set analysis for the expressions in charts. I'm having trouble when I need to have multiple conditions to aggregate values.

The original formula (it worked flawlessly but if I selected some data the chart was rebuilt) was:

=Sum(If(Tipo >= 2, If(right(Quarter,1)='1',If(left(Quarter,2)='15',ENR))))/Sum(If(right(Quarter,1)='1',If(left(Quarter,2)='15',ENR)))

The formula with set analysis that I think would be similar would be:

=Sum({1<Tipo={">2"},Quarter={"15*"},Quarter={"*1"}>}ENR)/Sum({1<Quarter={"15*"},Quarter={"*1"}>}ENR)

Do you know what I'm doing wrong with the second one?

Thanks!

[Anil_Babu_Samineni]

Expression seems okay to me, What was the wrong with that?

May be change it as Single Quotes? May be Qlikview Frustrating

Sum({1<Tipo={">2"},Quarter={"15*"},Quarter={"*1"}>}ENR)/Sum({1<Quarter={"15*"},Quarter={"*1"}>}ENR)

[#3]

The difference between >2 and >=2 was noted and even though it changes the chart it's still not the same as the original formula

[Anonymous]

Why do you use {1<.... instead of {$<...

[Anil_Babu_Samineni]

Hence, Here >2 will fetch only till 1 from initiative. But, >=2 will effect with 2 Club value due to using of  Equal before value. That is the power of Algorithms will do

[#6]

Neither the single quotes nor the >= solve that the expressions show different values between the formulas. Tried both

[#7]

According to a tutorial by Michael Tarallo the 1 instead of the $ is to show always the condition even if you select some other values.

[Anonymous]

I don't know your data, but it could be a "problem" like that

https://community.qlik.com/message/1021669#1021669

When combining fields within if conditions / expressions which don't have a 1 to 1 relationship, you "multiply" your data, because QlikView builds temporary join tables.

[#9]

The thing is I'm comparing the resulting chart with one "handmade" in excel. The original formula is exactly the same as the one in excel and I know that's the correct one.

[#10 · Accepted solution]

Found the solution playing a little bit with the expressions.

Apparently with this expression it's solved:

=Sum({1<Tipo={">=2"},Quarter={'15Q1'}>}ENR)/Sum({1<Quarter={'15Q1'}>}ENR)

The difference is I have only 1 Quarter condition, it's a little bit weird to me since both expressions should be the same. Anyone has any clues?