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

[#2]

If you want to intersect the two sets in your sum expressions above it would be something like....=SUM({<[key_Calendar] = {">=$(vP1Start) <=$(=vP1End)"}>*<[key_Calendar] = {">=$(vP2Start) <=$(=vP2End)"}>} [Net_USD]).
Take a look here for some additional information...Set Analysis - Intersection

[#3]

To be precise, I will need to do this.

For the entire dataset with many products id's

Reduce the dataset to only the portion of it where products were sold in both periods

If product ID sold in both P1 and P2, then show P1 and P2 sales, if not, show nothing in P1 and P2.

Then, I will compute something on the remaining dataset like sales P2 minus (Units P2 times price P1)

I will need to expand this to be selectable by the user as product or Products plus region or Product plus customer but for now, I just want to do it for products.

[ramoncova06]

this will  give you the intersection

SUM(

{

<[key_Calendar] = {">=$(vP1Start) <=$(=vP1End)"}>

*

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

}

[Net_USD])

[hariprasadqv]

Rammon's approach is right of my openion.

[#6]

Hi, Actually, it is not working because this would merely give me the intersection of the calendar keys and they never intersect because the two periods are never overlapping. Let me show a table with what I try to do.

The output must include only sales were the products where sold in both periods. Intersection of products.

The datasets include other dimensions that are ignored in this intersection logic.
In a second step, i will have to do something similar for things like "the product must have been sold in that region in both periods". So i need to find a way to tell the expression  what I am comparing.

[ramoncova06]

I have not tested this, but in that case adding the second data set into the first set analysis should do it

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

I am still a little concerned  over the [key_Calendar] since it seems to be the same for both date ranges, so I assumed that the dates did overlap

[#8]

I am sorry but this is not what I need.

First I need to recompute both P1 and P2 sales only for Product_ID sold in both periods, regardless of the level of aggregation shown in the front end
Then I will needs to compute P1 and P2 sales only for the combination of Product_ID plus Customers where sales did take place in both periods. Then I will have to do the same where the product_ID was sold in the same region in both periods.

It is because we want to compute the impact on revenue of price change period on period but obviously this computation must be done apple to apple. The analysis can show any level of aggregation, the base data at the lowest grain is used to make the computation so we can sum the individual impacts.

An idea would be to build a distinct list of Product_id sold in both P1 and P2 then compute P1 and P2 sales only for that list of product_ID,s Looks like I try to do something quite difficult here, and I am newbie.

[#9]

This problem is holding me back big time. Lets look at the bellow simple math
if([BB USD P1] * [BB USD P2] = 0, 0, [BB USD P1])

Where [BB USD P1] is given by the expression SUM({<[key_Calendar] = {">=$(vP1Start) <=$(=vP1End)"}>} [Net_USD])

Same for P2.

This is actually what I need however that works only if the Product_ID is in the chart.
If I display the chart with a higher level aggregation, it would not work.
So may be I can force this to work at the Product_ID Level even though I display the chart without Product_ID?

If this can be done, it will solve my problem.

[Anonymous]

"So may be I can force this to work at the Product_ID Level even though I display the chart without Product_ID?"

If you have an expression that works on the Product_ID level, try this:

sum(aggr(<Expression that works for Product_ID>, Product_ID))