topic Re: Counting articles separately in QlikView

Counting articles separately

Tue, 01 Sep 2015 08:22:46 GMT

Customers can order the Articles JACK01, JACK02, JACK03, JACK04 seperatly. I want to make an overvieuw of the amount of customers who have ordered all 4 of the articles. When I make a substringcount of the below expression, it only shows ‘1’ Are there any ideas what to do? Thanks a lot!

= SubStringCount( if(sum({$}(ORDER_QTY))>0, '1')

+ if(sum({$}(ORDER_QTY))>0, '1')

+ if(sum({$}(ORDER_QTY))>0, '1') ,4 )

Re: Counting articles separately

lironbaram — Tue, 01 Sep 2015 08:32:38 GMT

you can try something like

sum(aggr(if(count({<ORDER_QTY={">0"}>}distinct Article)=4,1,0),ClientID))

Re: Counting articles separately

hic — Tue, 01 Sep 2015 08:39:50 GMT

I would do something similar to

Count({$<CustomerID=

P({$<Article={JACK01}>} CustomerID)*

P({$<Article={JACK02}>} CustomerID)*

P({$<Article={JACK03}>} CustomerID)*

P({$<Article={JACK04}>} CustomerID)

distinct CustomerID)

I.e. use set analysis to pick out the possible customers. The four P() functions return the possible customers for each of the articles, and these are then "multiplied" with each other to form the intersection.

HIC

Re: Counting articles separately

Tue, 01 Sep 2015 08:59:12 GMT

Thank you very much! This answered worked flawlessly.