Hello guys,
I have a problem and I like to illustarte it with some test data. The raw data looks like this:
| Customer | Sales | Date |
| A | 10 | 02.01.2017 |
| A | 20 | 02.03.2018 |
| A | 30 | 03.02.2018 |
| B | 10 | 01.01.2019 |
| B | 20 | 01.02.2019 |
| C | 50 | 01.01.2016 |
| C | 50 | 01.02.2016 |
| C | 50 | 01.01.2017 |
| C | 100 | 01.01.2017 |
Now I want the sum(sales) for the year of the particular customer where he had his last order. So the result would look like so:
| Customer | last year of orders | sum of sales in last year of orders |
| A | 2018 | 50 |
| B | 2019 | 30 |
| C | 2017 | 150 |
In Qlik sense I already tried this for last year of orders:
max([Date])
This works.
sum(
{< Year = {'$(=year(max(Date)))'} >}
Sales)
This does not. It looks like the set analysis is only evaluated once for the entire table and not based on the customer on the table row.
So this is what I get:
| Customer | max(Date) | sum({< Year = {'$(=year(max(Date)))'} >}Sales) |
| A | 2018 | 0 |
| B | 2019 | 30 |
| C | 2017 | 0 |
