Skip to main content

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 
Search instead for 
Did you mean: 
ellenblackwell
Partner - Contributor III
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

   

DateOfSaleStoreIDProductFLAG_IsOurProductSales$
5/1/2016123A12
5/2/2016123C03
5/3/2016456B12
5/4/2016456C03
5/5/2016789C02
5/6/2016123A12.5
5/7/2016123B13
5/8/2016123C02.5
5/9/2016456C03
5/10/2016789C03
5/10/2016xyzB12

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
krishna_2644
Specialist III
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$)

Capture1.PNG

View solution in original post

4 Replies
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
MVP
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

krishna_2644
Specialist III
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$)

Capture1.PNG

ellenblackwell
Partner - Contributor III
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.