topic Re: List Box "And Mode" Problem in QlikView

List Box "And Mode" Problem

phaneendra_kunc — Sat, 10 Sep 2011 04:52:14 GMT

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.

Re: List Box "And Mode" Problem

swuehl — Sat, 10 Sep 2011 11:59:43 GMT

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

List Box "And Mode" Problem

swuehl — Sat, 10 Sep 2011 13:27:16 GMT

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

Re: List Box "And Mode" Problem

phaneendra_kunc — Sat, 10 Sep 2011 17:28:10 GMT

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

List Box "And Mode" Problem

swuehl — Sat, 10 Sep 2011 17:50:32 GMT

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

List Box "And Mode" Problem

phaneendra_kunc — Sat, 10 Sep 2011 18:14:39 GMT

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

Re: List Box "And Mode" Problem

swuehl — Sat, 10 Sep 2011 20:52:58 GMT

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.

Re: List Box "And Mode" Problem

phaneendra_kunc — Sat, 10 Sep 2011 21:38:05 GMT

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.

Re: List Box "And Mode" Problem

swuehl — Sun, 11 Sep 2011 13:27:15 GMT

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

Re: List Box "And Mode" Problem

phaneendra_kunc — Sun, 11 Sep 2011 15:27:56 GMT

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

Re: List Box "And Mode" Problem

phaneendra_kunc — Sun, 11 Sep 2011 17:11:48 GMT

Strefan,

Can we have link between BrandSelect and Market.

so if i pick a Market A then i wan to see the associated Brands in the BrandSelect List box.

Thanks,

Phani

Re: List Box "And Mode" Problem

swuehl — Sun, 11 Sep 2011 23:00:15 GMT

Phani221984,

I think that is not easy:

I believe you can't just link the Market or Brand to your AND mode table (because you can have only two fields).

So BrandSelect reflects only implicitely the Market, via Brand-Account (i.e. Market determines the available Brands and implicitely the Accounts, but I think that is not what you need).

I tried a little bit with a trigger on selection, but was missing a method to set the selection state of BrandSelect explicitely to excluded ( I think this is what you want - BrandSelect should be set excluded according to the Brands excluded by Market selection. But I don't even think that this is easily possible, it will probably break QVs logic.

Maybe I am missing some other solution. but I am stucked for now.

Regards,

Stefan