Creating Exception List in QV

[#1]

Hello,

I have attached a sample QVW document with my post. I want to do something that I believe is really simple but don't know how to do because i'm still new to the qlikview technology. In the attached Test.QVW file you can see that there is a list of Retail Accounts that purchased the LIGHT product. I want to know how can I do the reverse and create a chart that lists all the Retail Accounts that DID NOT purchase LIGHT (or whatever product I select).

Can someone please assist me with this?

Thanks in advance,

[#2]

Hi DK_ASN,

The solution offered on this thread is close and I don't think I was clear on my description of my problem. The time dimension is important to what I hope to achieve. For our business I would like to know all the customers that didn't buy any product. They may have purchased at some point in time or never at all. For example, If I select a time period of January 2010. I would like to see the list of customers that didn't buy any product in that month. They may have purchased in other months, or never purchased any product at all.

Right now I can do the following:

1. I can determine at any point in time, which customers have bought a product but not others. I achieve this by using the "select excluded" option

2. With NMiller's help (ninja), i am now able to generate a list of customers who never bought any product at all but it does not take into account the time I am filtering for.

Both these solutions does not help me to answer a question like:

"How many accounts didn't buy any of our products in January 2010".

(They may have bought our product/products at other times, but I'm only interested to see those that didn't buy any of our products in Jan 2010).

or

"How many accounts didn't buy our 6pk Mike's Hard Lemonade product in January 2010".

(They may have bought other products in Jan 2010 (or some other time), or they may have bought none of our products at all in Jan 2010 (or some other time))

[#3]

Ok, here is a pa

rticularly terrible expression that should take into account the BRDESC, YEAR and MONTH:

If(Count(
{1<YEAR={'$(=GetFieldSelections(YEAR))'},
MONTH={'$(=GetFieldSelections(MONTH))'}>}
DISTINCT BRDESC)=0, 'Nothing',
If(Count(
{1-<BRDESC={'$(=GetFieldSelections(BRDESC))'},
YEAR={'$(=GetFieldSelections(YEAR))'},
MONTH={'$(=GetFieldSelections(MONTH))'}>}
DISTINCT BRDESC)>0, 'Not Selected')
)

Use it in place of the expression on the second tab of my last example. This only works for one selection (and only one selection) on those fields. I don't think this is the proper way to do it though. I thought there was a way to ignore selections using {1}, but then respect certain selections (it's not <Year=>). Finding the right way may get rid of the requirement of having one and only one selection.

[#4]

Hi Ninja,

I'm still struggling with this. For example (in the qvw that isattached):

There is an Applebees - Aubern (on 1441 D ST NE, Auburn) that purchased 1 case of Lemonade in March 2010. This same restaurant did not buy anything in Feb 2010.

Using your code, I would expect that based on the following selection criteria

Doing Business As: APPLEBEES-AUBURN
BRDESC = Lemonae
YEAR = 2010
MONTH = Feb

this account should show up under 'NOTHING'

If the selection critera read

Doing Business As: APPLEBEES-AUBURN
BRDESC = Lemonae
YEAR = 2010
MONTH = MARCH

this account should show up under 'NOT SELECTED'

The code you gave me results in this account not showing up at all.