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]

It depends on how you want your selections to influence your results.

You can either right click on your BRDESC list box and select 'Select Excluded'.

OR

You can build a new list box with an expression: =If(BRDESC = 'LIGHT','LIGHT','NOT LIGHT').

OR

You can create the condition in the expression on the Amount straight table: =sum (If(BRDESC <> 'LIGHT',RSQTY))

[#3]

Thank you for your quick reply, but I believe your answer only gives me a result that provides me with a list of records that didn't buy LIGHT but bought at least one of the other brands. I am interested in generating a result set that provides me with a list of retail accounts that didn't buy any product at all. Essentially providing me with a list of retail accounts where our product has never been sold into.

If you could offer some help, it would be greatly appreciated.

Thanks,

[Anonymous]

Mike,
If I understand correctly what you need (as a rule I do not download files), a calculated list box with set analysis expression should help:
=aggr(only({1-$} Account),Account)

[#5]

I would insert a statement like this:

count(distinct(if(len(BRDESC)=0,[Doing Business As])))

[#6]

Hi DK_ASN,

Thanks for offering me your advice, however the solution doesn't seem to work, or I'm just not understanding what it's doing.

In my example, I'm trying to figure out how to generate an account list of customers who have never purchased one of our products (BRDESC). Using QV, it is easy to generate account lists of customers who have purchased a product or who have purchased all other products but the one selected (i.e. using the Select Excluded option when right clicking).

I'm struggling with generating an account list or a count of customers who have not purchased anything at all.

[#7]

Hi again

Please take a look at the enclosed where there is a table showing customers who have never bought anything at all. I have also added your Key1 in the table as that makes a difference in number of customers.

[#8]

Hi Ninja,

Thanks for your assistance. One thing that doesn't make sense to me in your qv document is that the time dimension doesn't work with the non-buying customer list. If I select a month, no accounts show up in the exception list and this is not true of the data or of the business. Can you offer some insight?

Thanks,

[#9]

This one seems like it should be easier than it is. Fortunately, you've posted the data, so I was able to try multiple solutions.

I've attached a sample of what I have come up with. So far, it only works when one and only one product is selected. I am using nested ifs to get the list of those that have purchased nothing and the list of those that have purchased something, but not the selected product.

The list of those who have purchased nothing should be:

If(Count({1}DISTINCT BRDESC)=0,1)

To get both sets, I used:

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

It seems like you should be able to modify this to work with 0 or more than 1 selection, but I got stuck.

[#10]

Hi Mike

Unless I have misunderstod your latest post completely regarding the fact that the time dimension doesn't work with the non-buying customer list, it is because time dimension is the time of purchase it seems.

So when you select e.g. January, you choose to see who have bought products in January and what were these products. As the customers in your list of non-buying customers never have bought anything the list will be empty.