Count of different products per customer account

[TheresaB_4]

Hi Everyone, 

I need some help with this one. I have tried a few other solutions but can they do not work. I know it might be simple but I can not seem to get it right.

A little background.

You have customer_accounts and some have productA or ProductB ot both products.
I want to show a pivot with distinct customer nr, in 3 different categories. Count of product A, or count of product B or Both
Then I can say 30 customers have both

20 has only product A

10 has Product B

I have a customer table with customer Name, CustomerAccount
product table with ProductID, ProductDescription
Something like this

I was thinking of first doing a measure then a count.

But I can not seam to get the measure right either
=if(ProductDescription='ProductA' and ProductDescription = 'ProductB' ,  'Dual' , 'Single')

[rwunderlich · Accepted solution]

I think

if (Count({<ProductDescription={"ProductA", "ProductB"}>}
Distinct ProductDescription) > 1, 'Dual', 'Single)

-Rob

[TheresaB_4]

Thank you Rob, it works now.

[TheresaB_4]

Can I do this in the Script editor as well?

[TheresaB_4]

If I want to use this solution as a Dimension rather than a Measure, can I do it.
if (Count({<ProductDescription={"ProductA", "ProductB"}>}
Distinct ProductDescription) > 1, 'Dual', 'Single)

[rwunderlich]

The syntax for doing this in the script editor would be different. Assume you have already loaded your data into Qlik table named "Facts", the approach may be something like this:

Left Join(Facts)
LOAD
  CustomerAccount,
  if(Count(DISTINCT ProductDescription)> 1, 'Dual', 'Single') as ProductCount
Resident Facts
Where Match(ProductDescription, 'ProductA', 'ProductB')
Group By CustomerAccount;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[rwunderlich]

Yes. Try it.