topic Re: Set Modifier element list logical aggregation in QlikView

Set Modifier element list logical aggregation

pedro_freire — Wed, 25 Nov 2020 16:16:04 GMT

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 — Tue, 13 Sep 2016 15:35:22 GMT

May be this:

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

Tue, 13 Sep 2016 21:51:28 GMT

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 — Tue, 13 Sep 2016 22:02:54 GMT

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:

swuehl — Tue, 13 Sep 2016 22:05:36 GMT

Another option could also be

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

pedro_freire — Mon, 27 Mar 2017 12:56:26 GMT

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?

Thanks

Pedro Freire