topic Creating Exception List in QV in QlikView

Creating Exception List in QV

Wed, 21 Apr 2010 04:45:32 GMT

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,

Creating Exception List in QV

Wed, 21 Apr 2010 04:52:42 GMT

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'.

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

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

Creating Exception List in QV

Wed, 21 Apr 2010 05:27:47 GMT

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,

Creating Exception List in QV

Anonymous — Fri, 23 Apr 2010 17:08:08 GMT

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)

Creating Exception List in QV

Mon, 26 Apr 2010 10:28:14 GMT

I would insert a statement like this:

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

Creating Exception List in QV

Mon, 26 Apr 2010 16:47:36 GMT

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.

Creating Exception List in QV

Tue, 27 Apr 2010 06:41:24 GMT

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.

Creating Exception List in QV

Wed, 05 May 2010 20:03:32 GMT

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,

Creating Exception List in QV

Wed, 05 May 2010 20:45:36 GMT

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.

Creating Exception List in QV

Thu, 06 May 2010 07:03:55 GMT

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.

Creating Exception List in QV

Thu, 06 May 2010 17:06:31 GMT

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).

"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))

Creating Exception List in QV

Thu, 06 May 2010 19:58:14 GMT

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.

Creating Exception List in QV

Mon, 17 May 2010 23:52:31 GMT

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.