# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Intersection of stores selling company products vs. 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 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!

1 Solution

Accepted Solutions
Specialist III

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

4 Replies
MVP

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?

MVP

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

Specialist III

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

Partner - Contributor III
Author

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.

Community Browser