How to get the intersection of two sets?

[#1]

Hello,

I have two sets based on one data extract. These sets are selected by the user who wants to compare a period to another. So far so good.
SUM({<[key_Calendar] = {">=$(vP1Start) <=$(=vP1End)"}>} [Net_USD])
and

SUM({<[key_Calendar] = {">=$(vP2Start) <=$(=vP2End)"}>} [Net_USD])

Now, I need to build a derived value for Net_USD where I retain only the intersection of the two sets relative to a dimension, i.e.
Products. The Product_ID must be present in both sets (periods) for the value to be displayed so if Product_ID 1 is sold in period 1 and not in period 2, I do not see any value and if product_ID 2 is sold in both period 1 and period 2 then I see a value.
I am a perfect beginner in QlickView so excuse me for asking simple questions like that, I need to come-up to speed, then I can help.

Philippe

[ramoncova06]

did you try adding the set analysis to the if ?

[#3]

I am still stuck on that issue.
Sum(Aggr(if([BB USD P1] * [BB USD P2] = 0, 0, [BB USD P1]), Product_ID))
returns 0
I have no idea what to try next.

[ramoncova06]

can you share your qvw, one solution might be grouping them in the script

[Anonymous]

Sum(Aggr(

sum(if([BB USD P1] * [BB USD P2] = 0, 0, [BB USD P1]))

, Product_ID))

Most likely the bold part is enough

[#6]

OK, I could finally make it work. What I found is that it would not work when I was trying to use expressions in my expression however by replicating the entire calculation in the expression, then it suddenly worked.
On a side note, You really have to make sure that you are consistent between the dimensions displayed in the chart and the dimension used to aggregate your values.

Thanks you very much to all of you for your help. It really made the difference.

Sum(Aggr(if(SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>} [BB_Net_USD])

* SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP2Start) <=$(=vP2End)"}>} [BB_Net_USD]) = 0, 0,

SUM({<[key_MRSD_Week_Delq_Grouped_CurrentWeek] = {">=$(vP1Start) <=$(=vP1End)"}>} [BB_Net_USD])), Product_ID, Business_Class_Cd))