Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this:
Count(DISTINCT {<Product ={'A'}>*<Product = {'B’}>} StoreID)
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!
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:
Another option could also be
Count({<StoreID = {"=Count({<Product ={'A',’B’}>}DISTINCT Product)=2"} >} DISTINCT StoreID)
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