Sales only for products where a customer received a sample

[fzalexanderjohn]

We often give customers samples of our products. What I want to show is if these samples result in greater sales or not.

So what I need to do is show the sum(sales) only for the product group that the particular customer received a sample from (in the past).

I tried various things but I can't find a way to solve this. I can't find a way to tell Qlik to "keep the connection" between customer and received a sample of this product group.

Revenue	Customer	Product	Productgroup	Date	Sample
-10	Karl	A123	A	01.01.2019	1
50	Karl	A122	A	01.03.2019	0
-50	Karl	B110	B	01.04.2019	1
150	Karl	D100	D	02.04.2019	0

For example in this table Karl should have a revenue based on samples of -10 because he did not receive a sample of Productgroup D. He did receive a sample from Productgroup A and B though, so those products should count.

I provided a Test-App with sample data. The correct value should be 760.

Thanks!

[sunny_talwar]

Which rows are you summing to get 760 for Karl?

[fzalexanderjohn]

That is the correct sum for the test app. I have more data in there. In the table above the correct sum should be -10.

[sunny_talwar]

But how is this calculated in the same app? Which rows are summed to get 760?

[fzalexanderjohn]

Revenue	Customer	Product	Productgroup	Date	Sample	Calculated
-10	Karl	A123	A	01.01.2019	1	X
50	Karl	A122	A	01.03.2019	0	X
-50	Karl	B110	B	01.04.2019	1	X
150	Karl	D100	D	02.04.2019	0
100	Karl	A123	A	10.10.2019	0	X
400	Karl	B110	B	11.10.2019	0	X
300	Karl	A124	A	31.12.2019	0	X
-10	Alex	A123	A	01.01.2019	1	X
80	Alex	A122	A	01.03.2019	0	X
100	Alex	B110	B	11.10.2019	0
150	Alex	D100	D	02.04.2019	0
-100	Alex	A123	A	10.10.2018	1	X
500	Peter	A123	A	01.01.2019	0

If you sum those rows that are marked with calculated, it should be 760.

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(If(Sum(Sample) > 0, Sum(Revenue)), Customer, [Produkt.Productgroup]))

[fzalexanderjohn]

I don't understand why this works, but it does. Thanks!