Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Creator II

## Intersection of stores with sales of company and competitor products

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!

5 Replies
Specialist

hi,

clear explanation needed whats ur competitor product name.

Not applicable

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:

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

Perhaps like this:

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

talk is cheap, supply exceeds demand
MVP

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

Intersection of stores selling company products vs. competitor products

(seems like I am already got confused )

Specialist III

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

Community Browser