topic Re: Count only if .. in QlikView

Count only if ..

nigel987 — Fri, 27 Nov 2015 11:49:01 GMT

Hi,

please help, this seems so basic but I still can't figure out how to do it.

I have several customers with a CustomerID. The customers can by certain products, which have a ProductType (e.g. Car, House, Food, etc).

How can I count the customers, who have bought ONLY(!) a car, and no house, food, etc...?

The closest I get is this (but I also counts customers which bought a car and a house:

Count({$<ProductType={'Car'}>} Distinct CustomerID)

Thanks for your help!

Re: Count only if ..

Anonymous — Fri, 27 Nov 2015 12:00:32 GMT

your syntax is ok...

=Count({$<ProductType={'Car'}>} Distinct CustomerID)

what error you are getting??

Re: Count only if ..

nigel987 — Fri, 27 Nov 2015 12:01:40 GMT

Hi, there is no error. But it counts all users which ordered a car. I want to count users which ONLY ordered a car, but no other product type, etc.

Re: Count only if ..

Fri, 27 Nov 2015 12:11:45 GMT

Hi nihal your syntax is correct,

what error your getting

Re: Count only if ..

Anonymous — Fri, 27 Nov 2015 12:15:55 GMT

With this set {$<ProductType={'Car'}>}, you would be getting Car only

Re: Count only if ..

nigel987 — Fri, 27 Nov 2015 12:21:26 GMT

I've attached a sample file.

Re: Count only if ..

effinty2112 — Fri, 27 Nov 2015 12:21:44 GMT

Hi Nihal,

This is a really good question. Try

=Concat(DISTINCT {$<[CustomerID] =E({<ProductType -= {'Car'}>})>}[CustomerID],', ')

This should give you a concatenated string of all the CustomerIDs who bought only a car. If a customer bought a ProductType of anything other than 'Car' these orders would be captured by the set modifier <ProductType -= {'Car'}> and a CustomerID is excluded if an instance of it occurs in this set.

Hope it works!

Re: Count only if ..

tresB — Fri, 27 Nov 2015 12:31:15 GMT

Try:

=Count({$<ProductType={'Car'}, CustomerID={"=Count( Distinct ProductType)=1"}>} Distinct CustomerID)

Re: Count only if ..

nagarjuna_kotha — Fri, 27 Nov 2015 12:34:46 GMT

Hi Nihal,

Can you tell me your expected output?

Regards,

Nagarjuna

Re: Count only if ..

effinty2112 — Fri, 27 Nov 2015 12:36:39 GMT

Hi Nihal,

In my reply I concatenated up the CustomerID, of course you can just count them rather than list them.

Cheers

Re: Count only if ..

Gysbert_Wassenaar — Fri, 27 Nov 2015 12:44:26 GMT

Count({$<CustomerID=E({<ProductType-={'Car'}>}CustomerID), ProductType={'Car'}>} Distinct CustomerID)

Re: Count only if ..

nigel987 — Fri, 27 Nov 2015 12:48:30 GMT

Hi Andrew, Hi Gysbert,

I am currently testing your approaches and they seem promising.

I am just trying to extend your formulas to get in a next step:

count of customers which have only (a car AND a house).

how would I do that?

Re: Count only if ..

effinty2112 — Fri, 27 Nov 2015 12:49:27 GMT

Hi Nihal,

This thread shows that there are sometimes different ways to approach the logic. My expression gives the same answer as tresesco but the approach differs.

tresesco's expression says let's count customers that have bought a car and that have only bought one ProductType.

My expression says count up the customers but first exclude those who have ever bought a ProductType that was not a Car.

The good news is that if one way of approaching a problem has you struggling to write a code or expression then a rethink of the logic behind the question can lead you to writing a solution using functions and set operators you're more comfortable with.

Re: Count only if ..

tresB — Fri, 27 Nov 2015 12:52:36 GMT

Then,

=Count({$<ProductType={'Car', 'House'}, CustomerID={"=Count( Distinct ProductType)=2"}>} Distinct CustomerID)

Check my above reply and compare, you would get the logic I hope.

Re: Count only if ..

nigel987 — Fri, 27 Nov 2015 12:56:13 GMT

Hi Andrew,

thank you for the explanation. I think I better understand the different solutions now and both do not seem to fit. I should have explained the entire problem form the very beginning. What I am trying to do, is to group my users like in a VENN diagram and count the different units.

So something like: (if I have the productType car, house and food)

count users who bought only a car

count users who bought only a house

count users who bought only food

count users who bought only car and house

count users who bought only car and food

count users who bought only house and food

count users who bought car, house and food

Re: Count only if ..

effinty2112 — Fri, 27 Nov 2015 13:06:16 GMT

Hi Nihal,

For your revised question the tresesco's expression is much easier to revise to give the answer. His expression now says count customers who have only bought a car or a house but have bought two distinct ProductTypes.

For my expression it's much more difficult to state the new logic in words even before rewriting it.

Cheers

Re: Count only if ..

nigel987 — Fri, 27 Nov 2015 13:09:31 GMT

That's what I'm doing. I'm taking each sample and testing the result it delivers.

I was just giving Andrew the full picture of the problem. I did NOT expect him to give me the code for each use case in the Venn diagram... if that's what you think.

Re: Count only if ..

nigel987 — Mon, 30 Nov 2015 16:26:03 GMT

Hi,

thanks to tresescos suggestion I could calculate the following counts:

Group with one product only

count users who bought only a car

Count({$<ProductType={'car'},CustomerID={"=Count(Distinct ProductType)=1"}>} Distinct CustomerID)

count users who bought only a house

Count({$<ProductType={'house'},CustomerID={"=Count(Distinct ProductType)=1"}>} Distinct CustomerID)

count users who bought only food

Count({$<ProductType={'food'},CustomerID={"=Count(Distinct ProductType)=1"}>} Distinct CustomerID)

Group with all three products

count users who bought car, house and food

Count({$<ProductType={'food','car','house'},CustomerID={"=Count(Distinct ProductType)=3"}>} Distinct CustomerID)

Group with only two products

count users who bought only car and house

Count({$<ProductType={'Car', 'House'}, CustomerID={"=Count( Distinct ProductType)=2"}>} Distinct CustomerID)

This does not work, as it counts users with two different productTypes, but one of them can be either car or house. So I get those with

car and house,
car and food,
house and food.

Does anyone know how I can state something like:

count users with two distinct productTypes (CustomerID={"=Count( Distinct ProductType)=2"})

but these product types must be car and house (and NOT food)?

Many thanks for any advice.

Re: Count only if ..

anlonghi2 — Mon, 30 Nov 2015 18:04:01 GMT

Hi Nihal,

please try with attached qvw

Let me know

Best regards

Andrea

Re: Count only if ..

anlonghi2 — Mon, 30 Nov 2015 18:20:34 GMT

Nihal,

if you need counters please look at new qvw version

Best regards

Andrea