Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Count({$<CustomerID=E({<ProductType-={'Car'}>}CustomerID), ProductType={'Car'}>} Distinct CustomerID)
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?
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.
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.
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
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
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.
Hi,
thanks to tresescos suggestion I could calculate the following counts:
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)
count users who bought car, house and food
Count({$<ProductType={'food','car','house'},CustomerID={"=Count(Distinct ProductType)=3"}>} Distinct CustomerID)
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
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.
Hi Nihal,
please try with attached qvw
Let me know
Best regards
Andrea
Nihal,
if you need counters please look at new qvw version
Best regards
Andrea