Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try this
Sum(Aggr(If(Sum(Sample) > 0, Sum(Revenue)), Customer, [Produkt.Productgroup]))
Which rows are you summing to get 760 for Karl?
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.
But how is this calculated in the same app? Which rows are summed to get 760?
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.
Try this
Sum(Aggr(If(Sum(Sample) > 0, Sum(Revenue)), Customer, [Produkt.Productgroup]))
I don't understand why this works, but it does. Thanks!