topic Filter on Customers with No Sales in QlikView

Filter on Customers with No Sales

pkelly — Fri, 09 Jul 2010 14:34:28 GMT

I have two tables namely Customer and Sales Invoice

These are joined by a field called %CustKey

I have a table which shows Customer Code and Customer Name

I have various list box filters from the sales invoice table which, when selected, show me the customers who have had sales.

I have been asked if it is possible for me to add a filter whereby I can select customers who have not had sales.

This could be either be used as a stand alone filter in conjunction with another list box filter.

For example, If I was to filter on financial year 2009 (which i get from my sales invoice table) and select "no sales", I would hope to get a list of cuctsomers who had no sales in the financial year 2009.

Any help on how to achieve this greatly appreciated.

Regards

Paul

Filter on Customers with No Sales

pkelly — Mon, 12 Jul 2010 09:09:50 GMT

Anyone any ideas?

Filter on Customers with No Sales

Mon, 12 Jul 2010 11:26:21 GMT

Hi Paul,

I don't know how much this is going to work.

In a straight table Dimension you can have Calculated Dimension i.e.,

If( Not Isnull(Sales),Customers).

I think this will display Only customer who don't have null value in sales.
Vice-versa you can do the same.
If this is not your expected, i'm sorry.

Regards,
Srini

Filter on Customers with No Sales

suniljain — Mon, 12 Jul 2010 12:06:19 GMT

You Can try it also at modelling level . it reduce complexity at presentation level.

Load

Customer,

Sum(Sales) as Sales

From Sales_Table Group by Customer;

at the design level you can filter it by if(sales =0, customer)

Regards,

Sunil Jain

Filter on Customers with No Sales

pkelly — Mon, 12 Jul 2010 14:38:45 GMT

Not sure if the above will get me where I want to be...

I have attached an example QlikView document with my interpretation of the above included.

I have also included a section which hopefully shows more clearly what i am trying to achieve.

Regards

Paul

Filter on Customers with No Sales

Mon, 12 Jul 2010 14:59:07 GMT

Hi,

i have updated your qvw, check it.

Filter on Customers with No Sales

pkelly — Mon, 12 Jul 2010 15:14:22 GMT

Thanks for the quick response...

I can see that you have added two tables to the bottom "Not Null New" and "Null New".

"CustomerC" is not listed in any of them and there is no filter for Sales "yes" / "no".

It may be that I am not explaining what I am trying to achieve correctly.

The user will have a table listing all of our customers (over 21 thousand).

They will have various filters that they can select (I have shown a couple of the example).

We would like one of these to be customer has had a sales yes / no (this is an optional filter).

If they click No - the customer list will update to show the customers who do not have a sale.

Our intentiond is that they would then export this table to Excel and use for mail merge purposes.

Apologies if I have misinterpreted what you have sent but it does not appear to be doing what we would like.

Thank you for taking the time to look at this for me, it is greatly appreciated.

Filter on Customers with No Sales

Tue, 13 Jul 2010 07:38:27 GMT

Hi Paul,

I have tried this with the help of List box and Straight Table.

And in the sheet, i have displayed it.

Based on the value you select from the list box, the straight table will display the customer and value.

You can also modify i.e., Always one value selected in the list box and rename as per the usability.

Filter on Customers with No Sales

pkelly — Tue, 13 Jul 2010 07:49:13 GMT

Thanks.....This looks exactly what I am looking for....

Thank you for your time and effort - it is greatly appreciated....

Best Regards

Paul