AND statement within field

[#1]

Hello,

I have a listbox with the following calculated dimensions and I want the list to return only those Partners that match ALL the Specializations that are selected in the C_Specialization field. Is that possible? Any suggestion? Maybe I should use indirect set analysis but I'm not sure how.

Thanks

=if(Specialization=C_Specialization

and

aggr(count(distinct Client)>=1

, Partner),

Partner)

[swuehl]

Not sure if I have fully understood, maybe like

=aggr(

only({<Specialization=C_Specialization, Partner= {"=count(distinct Client)>=1"}> } Partner)

, Partner)

[#3]

Thanks swuehl but I'm afraid with your expression Partner show only when something is selected in C_Specialization, which is not what I'm trying to achieve.

Basically, if I select 2 C_Specializations, I want to see only the Partners where their Specializations (a separate field) matches both selected C_Specializations and not any one of them.

Here is a sample file so that you can see what I mean - check the Nr of Specializations chart: Partners 71634 and 131351 should not show because they only match one of the two selected C_Specializations.

Thanks

[swuehl]

Ok, attached is one possible solution.

(Another idea, instead of the data island C_Specialization, you could also consider using a AND_mode list box)

I am using this as expression in the listbox:

=aggr(

only({<Partner= {"=count(distinct Client)>=1 and

sum(pow(2,keepchar(C_Specialization,'0123456789'))) =sum(distinct pow(2,keepchar(Specialization,'0123456789')))"}> } Partner)

, Partner)

I assigned each specialization a binary digit (power of two), so I could compare the two selections just by summing the values (I would rather do the assignment in the script, but I can't reload your sample file and I am too lazy to do a binary load). I've done the assignment just by looking at the numerical in the specialization name, but that's just as workaround for your dummy data, you can do the assignment as you like.

If you need to check not on complete equality in selections, but partial (C_Specialization as a subset of Specialization), you could work with binary and operator.

Hope this helps,

Stefan

[#5]

Thank you!

We are almost there. The remaining issues are as follows:

• If nothing is selected in C_Specialization, I still need to be able to see the list of possible Partners - that is to say the list of all Partners that have Specializations matching any one of the possible C_Specialization plus the second condition (they have at least one client)
• For various reasons I cannot make selections directly in Specializations (the Clients that have never been served will disappear and I don't want them to) so I need to be able to get the result just making selections from C_Specialization

Please don't leave me now

[swuehl · Accepted solution]

Maybe I am running into something too complicated, need to take a break. But maybe like this?

=aggr(

only({<

     Partner= {"=count(distinct Client)>=1 and

    if(not GetSelectedCount(C_Specialization), 1,

    sum(distinct pow(2,keepchar(Specialization,'0123456789'))) bitand sum(pow(2,keepchar(C_Specialization,'0123456789')))=sum(pow(2,keepchar(C_Specialization,'0123456789')))) "}

,Specialization = p(C_Specialization) > } Partner)

, Partner)

Regards,

Stefan

[#7]

WOW! It seems to work fine! Thank you!

[#8]

Hi Stefan,

Would you be able to help me with this please: http://community.qlik.com/message/244712#244712 ?

I really don't know what else I could try to make the calculated dimension work...

Pretty please

Linda