topic Re: Set Analysis with multiple conditions in QlikView

Set Analysis with multiple conditions

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Set Analysis with multiple conditions

Anil_Babu_Samineni — Thu, 09 Feb 2017 17:18:49 GMT

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)

Re: Set Analysis with multiple conditions

Thu, 09 Feb 2017 17:19:11 GMT

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

Re: Set Analysis with multiple conditions

Anonymous — Thu, 09 Feb 2017 17:21:17 GMT

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

Re: Set Analysis with multiple conditions

Anil_Babu_Samineni — Thu, 09 Feb 2017 17:21:38 GMT

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

Re: Set Analysis with multiple conditions

Thu, 09 Feb 2017 17:23:33 GMT

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

Re: Set Analysis with multiple conditions

Thu, 09 Feb 2017 17:24:23 GMT

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.

Re: Set Analysis with multiple conditions

Anonymous — Thu, 09 Feb 2017 17:29:22 GMT

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.

Re: Set Analysis with multiple conditions

Thu, 09 Feb 2017 17:40:52 GMT

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.

Re: Set Analysis with multiple conditions

Thu, 09 Feb 2017 17:45:09 GMT

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?

Re: Set Analysis with multiple conditions

Anonymous — Thu, 09 Feb 2017 17:46:23 GMT

that means, you're sure, that there's always a one to one relationship in the chain between:

- ENR

- Quarter

- Tipo

???

Re: Set Analysis with multiple conditions

Thu, 09 Feb 2017 17:50:30 GMT

Found the solution a few answers below.

For a specific Quarter and a specific Tipo you have multiple ENR, however the key for the tables is a specific Code

Re: Set Analysis with multiple conditions

Anonymous — Thu, 09 Feb 2017 18:02:17 GMT

as I said: I don't know your data