topic Set analysis - using count distinct in QlikView

Set analysis - using count distinct

zagzebski — Tue, 21 May 2013 18:32:25 GMT

I need to be able to count customers who we have sales to for at least of 2 of our products, BUT in a chart show that customer, the product and the amount of sales:

Raw Data:

Customer Product Sales

A 1 $10

A 2 $15

A 2 $20

B 1 $15

C 2 $20

C 3 $25

OBJECT should look like (only showing groups with sales of at least two products and summarizing by customer by product) NOTE: Customer B is not in chart because it didn't have sales to a least 2 products

Customer Product Sales

A 1 $10

A 2 $35

C 2 $20

C 3 $25

Re: Set analysis - using count distinct

Tue, 21 May 2013 18:39:41 GMT

try this

load

Customer,

Product,

sum(Sales) as Sales_new

from table

group by Customer, Product

where not match(Customer,'B');

Re: Set analysis - using count distinct

zagzebski — Tue, 21 May 2013 18:42:00 GMT

Couple of issues -

1. Can this be done in an expression and not a script?

2. Where does the script identify count the number of products?

Re: Set analysis - using count distinct

Tue, 21 May 2013 19:42:49 GMT

This may work for you.

sum({$<Customer = {'=COUNT(DISTINCT Product) > 1'}>} Sales )

Re: Set analysis - using count distinct

zagzebski — Tue, 21 May 2013 20:34:06 GMT

This ended up working thanks.