Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
New 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
Highlighted

Re: Set Modifier element list logical aggregation

May be this:

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

Highlighted
Not applicable

Re: Set Modifier element list logical aggregation

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!

Highlighted
MVP
MVP

Re: Set Modifier element list logical aggregation

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

Highlighted
MVP
MVP

Re: Set Modifier element list logical aggregation

Another option could also be

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

Highlighted
New Contributor III

Re: Set Modifier element list logical aggregation

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