Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
nigel987
Creator II
Creator II

Count only if ..

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!

27 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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


talk is cheap, supply exceeds demand
nigel987
Creator II
Creator II
Author

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?

effinty2112
Master
Master

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.

tresesco
MVP
MVP

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.

nigel987
Creator II
Creator II
Author

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



effinty2112
Master
Master

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

nigel987
Creator II
Creator II
Author

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.

nigel987
Creator II
Creator II
Author

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.

anlonghi2
Creator II
Creator II

Hi Nihal,

please try with attached qvw

Let me know

Best regards

Andrea

anlonghi2
Creator II
Creator II

Nihal,

if you need counters please look at new qvw version

Best regards

Andrea