Intersection of stores with sales of company and competitor products

[bullish35]

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 stores; 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!

[ajsjoshua]

hi,

clear explanation needed whats ur competitor product name.

[#3]

Hi Ellen,

Getting this type of logic in set analysis might be a tricky thing to do an easier approach would be to create an additional field in your model with a flagging for Stores which sell both companies products and then use that in your set analysis.

Example:

Load

     StoreID,

     if(avg(FLAG_IsOurProduct)>0 and avg(FLAG_IsOurProduct)<1,1,0) as Flag_StoreSellingBothProduct

Resident Table

Group by StodeID;

Then using expression such as sum( {<Flag_StoreSellingBothProduct={1}>} Sales$ )

-AJ

[Gysbert_Wassenaar]

Perhaps like this:

sum({<StoreID={"=count({<FLAG_IsOurProduct={1}>}distinct Product)=2"}*P({<FLAG_IsOurProduct={0}>}StoreID)>}Sales$)

[swuehl]

As far as I understood, you want to filter on stores where both flags appear:

Maybe

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

or

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

as suggested also in your other thread

Intersection of stores selling company products vs. competitor products

(seems like I am already got confused )

[Qrishna]

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$)