QlikView Challenge (Products Purchased and Not Purchased Accounts)

[phaneendra_kunc]

Hi All,

I need 2 list boxes. one with Purchased Products and another with Not purchased products. (2 list boxes is not necessary if you have better solution)

In the grid i want ot see the Accounts based on the selection i will made from the above list boxes.

So if i pick  "Purchasing Brands A and B from list box 1 "  and "Not Purchasing Brand E from list box 2" then i want to see only Accounts 2,4 with out loosing the brands on top.

Thanks in advance.

Phani

[swuehl]

Hi phani221984,

maybe like attached?

My approach is to use two separate listboxes created from Brand field, one to select the AND fields, and one for the NOT fields.

Then I use variables to build strings for the p() functions using intersection and XOR set operators similar to the one I stated here.

The rest is pretty straight forward.

Regards,

Stefan

[phaneendra_kunc]

Hi Stefan,

This looks good.

Also if possible can i have this too..

Will the Top Brand listbox be able to control the Brands we see on the Top of the grid.

If i pick Brand 1, 3 and 5 i want to see only those brands on top.

Thanks

Phani

[swuehl]

Hi phani221984,

I was just about posting a new version, so I haven't looked into your latest upload.

But maybe this is already doing what you want:

Changes:

- Table is reflecting selection in Brand list box.

- Fixed some issues with the AND / NOT selection (changed variables)

- AND / NOT list boxes do reflect the Market selection ( I added a link to Market for each box in the data model)

Regards,

Stefan

[phaneendra_kunc]

Hi Stefan,

This looks Great. it is working as expected.

But i got into another problem when i include my Month in to the fact.

if i pick month = 'CM'  in the below App. it is showing "Cisco" Brand even though i made a selection in BRand Not select List Box.

See attached for my excel and App.

Thanks

Phani

[swuehl · Accepted solution]

Hi,

I think you have to change the variable vBrandSelect to:

='(Account ' & vAndSelect &') * (Account '& vNotSelect &')'

See attached sample.

Regards,

Stefan

[phaneendra_kunc]

Thank you so much that solved my problem,

[phaneendra_kunc]

Hi Stefan,

Though this was ended, but i have some issue with this concept.

Tried little harder but im ending up with complex expressions to acheive this. So thought of asking my "Master".

so basically everything is working if the accounts in the below example is having data. But is is not working if any of the account has "sales as 0". This might happen because the same account may have data in Previous Month.

so in the below exapmle even though after selecting "Kodak" as Purchased Brand still im seeing zero's.

which i dont want to see.

See attached for the Files.

Let me know you though on this.

Thanks Phani

[swuehl]

Hi Phani,

I think I have not fully understood your issue (it has been two weeks since we had this discussion and my mind already forgot almost everything).

If you put this as expression in the upper right chart:

=sum( {<Account = p({<Account = {"=sum({<Brand={'Kodak'}>} Sales)>0"} >})*Account >} Sales)

do you get a result you expect (this is for Purchased Brand Kodak only right now)?

If no, I need some sleep. If yes, I need some sleep, but in the meanwhile, maybe you could try to incorporate the

p(...) phrase of above expression into the vAndSelect Variable. I think it is possible, but since we use concat function to parse the GetFieldSelection in, we must take care about the proper formatting (i.e. make proper usage of the delimiter and so on). The quotation mark has to be coded via chr() funktion, I believe.

Hope this helps,

Stefan

Edit:

Ok, had enough sleep, try this as expression for variable vAndSelect:

=if(GetSelectedCount(BrandAndSelect),' * (p({<Account = {"=sum({<Brand= {'&chr(39)  &

    concat(distinct BrandAndSelect, chr(39)&'}>} Sales)>0"} >})* p({<Account = {"=sum({<Brand= {'&chr(39) )

     & chr(39)&'}>} Sales)>0"} >}) )' ,'')

[phaneendra_kunc]

Hi Stefan,

Yours is perfectly working for purchased.

I tried same logic for Not Purchased too. But not working.

So here we have to consider 2 type of accounts for Not Purchased. One with "Zero   sales", another one that is not even associated to that Brand.

i copied above Expression and modified: let me know what's wrong with this.(im considering only Zero sales, dont know how to keep the second condition)

=if(GetSelectedCount(BrandNotSelect),' / (p({<Account = {"=sum({<Brand= {'&chr(39)  &

    concat(distinct BrandNotSelect, chr(39)&'}>} Sales)<=0"} >})+ p({<Account = {"=sum({<Brand= {'&chr(39) )

     & chr(39)&'}>} Sales)<=0"}>}) )' ,'')

So for the below attached for accounts

200966315   --- Not even associate to the Brand Sony ( Basically Null)

200966324 --- Associated but it is having "Zero slaes" for Sony

so if i click on Brand Not select " Sony" then i want to see the above 2 accounts. Which iam not with the above logic.

Thanks,

Phani