Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
pedro_freire
Contributor III
Contributor III

Set Modifier element list logical aggregation

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

5 Replies
sunny_talwar

May be this:

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

Not applicable

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
MVP
MVP

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
MVP
MVP

Another option could also be

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

pedro_freire
Contributor III
Contributor III
Author

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