List Box "And Mode" Problem

[phaneendra_kunc]

Hi,

I have a problem with "And mode" from list box, even though it is enabled i cant pick more than 1 from the list box.

i have followed the rules from QV help(F1) and serched for "And mode".

Also refered below link.

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

Please see attached for the file i was trying to acheive.

Thanks in Advance.

[swuehl]

Hi phani221984,

I haven't used AND mode too much before, so I am just guessing also:

I noticed in John's example in above referenced thread, that he is doing the distinct load (and following AND mode on the field) on the other table (Account table in your case).

If you do this, you can use And mode on field Account succesfully (even though the requirement stated in the HELP: the table must not contain any duplicate records, is not fulfilled! See Account 4).

Not sure if this helps, I am pretty confused also.

Regards,

Stefan

P.S: The requirement "the table must not contain any duplicate records" is probably not a hard requirement, you just lose information on your distinct load.

edit

[swuehl]

Well,

I thought about it a little more.

What the and mode is probably doing, is creating the intersection of your implicitely selected ID Values within the tables scope, i.e. if you select two brands, you are not getting an intersection.

Example:

Selected Brand A -> Brand ID 1

Selected Brand B -> Brand ID 2

On the other table:

Account 1 --> Brand ID 1,2,3,4,5,6

Account 3 --> Brand ID 2,4

Here you get an intersection of Brand IDs 2,4

(Though your first table fulfills all requirements to enable AND-mode, it is just not "useful" (well, you could use the NOT-feature, maybe))

If my assumption is correct, I think you could solve your problem if you create like an Account-ID in your Brands-table instead the Brands-ID in the Account-Table.

Hope I got my ideas clear,

Stefan

[phaneendra_kunc]

Thanks Stefan,

yes you are right, but i do have a Market which restricts the Brands too.

All im trying to do is to find out the Accounts who bought Brand 1 and Brand 3 but not Brand 5

see attached you will understand better.

Thanks,

Phani

[swuehl]

Well, besides reworking your data model (where I had not a looked at), you could also use set analysis, like

=count({<Account= (P({<Brand= {'Brand 1'}>})* P({<Brand= {'Brand 3'}>}))/P({<Brand= {'Brand 5'}>}) >} DISTINCT Brand)

as expression in your table.

Regards,

Stefan

[phaneendra_kunc]

Thanks Again Stefen,

Your expression works if im going to choose 2 Purchased Brands and 1 not purchansed Brand.( by pushing the selected brands to Variables and use them in Expression)

but for my senario user is going to choose some times 1 Purchasing and 1 Not Purchasing , or 3 Purchasing and 1 not purchasing. This will be dynamic.

I dont know how to Acheive this.

I have posed this here

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

Thanks for helping me out so far.

Phani

[swuehl]

Hi,

please look at attached.

I just added a copy of your Brand Table as BrandSelect table. Used this for AND mode list box, seems to work.

Regards,

Stefan

P.S: I've seen your other post, I think this is also possible, but if above works, I would prefer this.

[phaneendra_kunc]

yours perfectly works for the count. but not for the sales, as there is no relation between Brand ID and Brand columns if i use SUM instead of Count it is giving the Aggregated values for whole Account in that market.

Sorry i dint told abt that before

see attached for what im trying to acheive.

[swuehl · Accepted solution]

Hi phani221984,

maybe like attached?

I used this as expression in a pivot table with account and Brand ID as dimensions:

=sum( aggr(if(wildmatch(GetFieldSelections(BrandSelect),'*'&[BRAND ID]&'*'),sum(Sales),0),[BRAND ID],Account))

I am not sure why I need to use the aggr(), but if I don't, I get multiple records back.

You also won't get a column for the NOT values (like Brand 5), but I think IMHO that's ok, because you selected accounts with no Brand match for those anyway.

Then I noticed that you are keeping like duplicate information in Account table (Account -Brand ID) and Brand Table ( Account - Brand). I don't know if this is necessary or maybe you could link your market to the Account table via Brand ID also. But that's an annotation.

Regards,

Stefan

[phaneendra_kunc]

Hi Stefan,

This solved my problem. Thanks a lot for the help. you are genious.

if you are free please look at my other posting

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

Thanks,

Phani