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!

[tresesco · Accepted solution]

Try:

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

[rwunderlich]

I think the pattern is this. Select the Customers who bought the target Product(s) and subtract Customers who bought  anything other than the target product(s).

Customers who bought only a Car:

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

- <CustomerID=P({<ProductType=-{'Car'}>})>}

Distinct CustomerID)

"And" in set analysis is the * operator. Customers who bought only Car and House.

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

* <CustomerID=P({<ProductType={'House'}>})>

- <CustomerID=P({<ProductType=-{'Car', 'House'}>})>}

Distinct CustomerID)

Customers who bought only Car and House and Food:

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

* <CustomerID=P({<ProductType={'House'}>})>

* <CustomerID=P({<ProductType={'Food'}>})>

- <CustomerID=P({<ProductType=-{'Car', 'House', 'Food'}>})>}

Distinct CustomerID)

Perhaps there is an easier syntax, but it escapes me. I'll ping a couple of experts to see if they have a better solution,

I think tresesco suggestion of intersecting with the Distinct Product count may work as well.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[tresesco]

Now combining yours and mine, I guess a relatively better solution could be:

=Count({<CustomerID={"=Count( Distinct ProductType)=2"}>

- <CustomerID=P({<ProductType=-{'Car', 'House'}>})>}

Distinct CustomerID)

[rwunderlich]

Seems much cleaner. And I can't think of a circumstance where it wouldn't work, WIll be curious to hear from the OP if it works in the real app.

-Rob

[Oleg_Troyansky]

I think Rob's suggestion certainly works. The only easier solution (at least, for one product only) would be to use an Advanced Search on the Customer field and request that the concatenation of all Products equals to the desired product name. For example, something like this:

=Count({<CustomerID={"=concat( Distinct ProductType, '|')='Car'"}>} Distinct CustomerID)

For two products, the solution could be a bit more complex - it needs to take into account the specific sorting of the Product Types:

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

Check out my new book QlikView Your Business for advanced examples of using Set Analysis in QlikView.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...

[rwunderlich]

I like your solution Oleg. Using ProductType as the SortWeight ensures alphabetical order so it seems pretty elegant to me.

-Rob

[nigel987]

Hi tresesco, Rob and Oleg,

many thanks for all your valuable input. This community is really great!

All of the suggested solutions work and deliver the same result. I've crosschecked with Excel and the results are correct. I just needed minor tweeks, e.g. the minus sign had to be before the = (e.g. ProductType-={'Car', 'House'})

I've decided to go with tresesco's solution, mainly because the expression is shorter and thus easier to maintain.

The second solution is more generic and could also be used for the cases where you have more than 3 products (partitions in the Venn diagram).

Here's a summary of all the solutions (for those who are interested):

Count users who bought 1 certain product:

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

you can also use

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

                 <CustomerID=P({<ProductType-={'Car'}>})>}

  Distinct CustomerID)

Count users who bought 3 products (out of three!):

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

you can also use

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

                 <CustomerID=P({<ProductType={'House'}>})>*

                 <CustomerID=P({<ProductType={'Food'}>})> -

                  <CustomerID=P({<ProductType-={'Car', 'House', 'Food'}>})>}

  Distinct CustomerID)

Count users who bought 2 certain products only:

=Count({<CustomerID={"=Count( Distinct ProductType)=2"}> -

              <CustomerID=P({<ProductType-={'Car', 'House'}>})>} Distinct CustomerID)

you can also use

=Count({$<CustomerID=P({<ProductType={'House'}>}) *

                                        P({<ProductType={'Car'}>})>*

                 <CustomerID={"=Count( Distinct ProductType)=2"}>} Distinct CustomerID)

you can also use

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

                 <CustomerID=P({<ProductType={'House'}>})> -

                 <CustomerID=P({<ProductType-={'Car','House'}>})>}

  Distinct CustomerID)

KR, Nigel

[nigel987]

Hi anlonghi2, Hi Oleg,

thanks for the suggested solutions. Both are similar. This seems to be also a nice way to solve this issue!

@Oleg: I will try that book! Thanks for the recommendation.

KR,Nigel