topic Re: Counting Customers with Multiple Products in QlikView

Counting Customers with Multiple Products

pauledrich — Thu, 29 May 2014 12:06:24 GMT

Hi and thanks for the help I have searched Help & the community.

My objective is to create a Pivot table which will show a variety of purchasing combinations which Count the transactions completed by each customer in the year the transaction took place, my problem is that I cant get the syntax of the expression correct.

I have a transaction table with multiple products, Fields are - TransID, CustID, TransDate, ProductType I have year as my dimension.

So if Customer 1 had purchased product A (2014) & B (2013) and customer 2 had bought product A (2014) only customer 1 would be included in the count.

My expression Count ({$<ProductType={A,B}>}CustID) returns Product B sales - Even Explicit Count({$<ProductType = p {A,B}>CustID) doesn't return what I expected.

Thanks again.

Re: Counting Customers with Multiple Products

pauledrich — Thu, 29 May 2014 12:25:55 GMT

Any Help Appreciated

Re: Counting Customers with Multiple Products

Thu, 29 May 2014 12:28:12 GMT

In a pivot table you could just use CustID as a dimension and then use count(TransID) as an expression

Re: Counting Customers with Multiple Products

Anonymous — Thu, 29 May 2014 12:43:21 GMT

Hello!

You could try using a calculated dimension like this one:

=Aggr(if(Count(ProductType) > 1, CustID), CustID)

Cheers.

Re: Counting Customers with Multiple Products

pauledrich — Thu, 29 May 2014 12:51:10 GMT

Thank Bruno & Robert

I used the Aggr in the expression previously which as you would expect does calculate customers correctly - however the introduction of year just doesn't fit my needs.

I want to show only the count of customers with the dimension of year who have purchased products A & B only not any other combination.

Re: Counting Customers with Multiple Products

Anonymous — Thu, 29 May 2014 13:00:29 GMT

Then we could try using set analysis in our calculated dimension:

=Aggr(if(Count({$<ProductType = {'A', 'B'}>} Total Distinct ProductType) > 1, CustID), CustID)

This expression will return only the costumers who have purchased both products A and B.

Can you upload a sample app so we can see the issue about the year dimension?