Intersection of stores selling company products vs. competitor products

[ellenblackwell]

Hello,

I need to calculate market Sales$ for my client's products and competitor products in only the stores which sell both products. So, first I have to identify which stores sell both products. Then, I have to sum the sales of only those store; but, I can't get the intersection syntax correct in my set analysis. Any help would be greatly appreciated.

Example of data

DateOfSale	StoreID	Product	FLAG_IsOurProduct	Sales$
5/1/2016	123	A	1	2
5/2/2016	123	C	0	3
5/3/2016	456	B	1	2
5/4/2016	456	C	0	3
5/5/2016	789	C	0	2
5/6/2016	123	A	1	2.5
5/7/2016	123	B	1	3
5/8/2016	123	C	0	2.5
5/9/2016	456	C	0	3
5/10/2016	789	C	0	3
5/10/2016	xyz	B	1	2

Expected result is the market Sales$ in stores which sell both our and competitor products.

Distinct number of stores which sell both our products and the competitor's product = 2 (Store IDs 123 and 456)

  Market Sales$ for stores 123 and 456 = $21.00.

Thank you!

[sunny_talwar]

How is store 456 selling both products? I don't see 456 selling A in the example above? So does that mean sum only sales from StoreID 123?

[swuehl]

Maybe

=Sum({<StoreID = p({<[FLAG_IsOurProduct] = {1} >}) *p({<[FLAG_IsOurProduct] = {0} >}) >} [Sales$])

or

=Sum({<StoreID = {"=Count(DISTINCT [FLAG_IsOurProduct]) = 2"} >} [Sales$])

edit:

And please refrain from posting multiple times the same request, it just makes it hard to follow a discussion:

Intersection of stores with sales of company and competitor products

[Qrishna · Accepted solution]

i've same suggestions for you in other thread too.

Hi Ellen,

Swuehl has some good suggestions.

Also, you can try using the below expression

=sum({$<StoreID = {"=concat(distinct {$<FLAG_IsOurProduct={0}>}StoreID,',')"}> * <StoreID = {"=concat(distinct {$<FLAG_IsOurProduct={1}>}StoreID,',')"}  >}Sales$)

[ellenblackwell]

Sorry for the late response. Product A and B are both 'our products' (FLG_IsOurProduct=1). Store 456 isn't selling Product A, but they did sell Product B on 5/3/16.