Set Modifier element list logical aggregation

[pedro_freire]

I have the following challenge:

The expression bellow will count distinct InvoiceIDs that include, either product A OR Product B.

Count(DISTINCT {<Product ={'A',’B’}>} InvoiceID)

If I want to know how many stores have sold Product A or Product B, I will have a similar expression:

Count(DISTINCT {<Product ={'A',’B’}>} StoreID)

What is the simpler and most performing way to know how many stores have sold Product A AND Product B simultaneously?

Pedro Freire

[sunny_talwar]

May be this:

Count(DISTINCT {<Product ={'A'}>*<Product = {'B’}>} StoreID)

[#3]

Count(distinct if(Product = 'A' AND Product = 'B'),([StoreID]))

OR try creating a new calculated dimension "Both" determining if a store sold both product A and B and then try the following

Count(distinct if(Product = 'Both'),([StoreID]))

Hope this answers your question!

[swuehl]

This should only need to calculate the intersection on StoreID field values, not for the complete record set:

Count({<StoreID = p({<Product ={'A'>})*p({<Product ={’B’}>}) >} DISTINCT StoreID)

or maybe create a data model that would support a list box for a Product field in AND-Mode and then use this in your set analysis:

Qlik Tips: AND Mode in Set Analysis

[swuehl]

Another option could also be

Count({<StoreID = {"=Count({<Product ={'A',’B’}>}DISTINCT Product)=2"} >} DISTINCT StoreID)

[pedro_freire]

Hello Stefan,

I guess this might be a litle bold on my part, but stalwar1 mention you are my only hope!

I wonder if I can dare you for another challange?

Evaluate Chart Expression at line level - Part3‌

Thanks

Pedro Freire