Count only if ..

[nigel987]

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!

[Anonymous]

your syntax is ok...

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

what error you are getting??

[nigel987]

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.

[#4]

Hi nihal your syntax is correct,

what error your getting

[Anonymous]

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

[nigel987]

I've attached a sample file.

[effinty2112]

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!

[tresesco]

Try:

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

[nagarjuna_kotha]

Hi Nihal,

Can you tell me your expected output?

Regards,

Nagarjuna

[effinty2112]

Hi Nihal,

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

Cheers