Hi all,
I have a question regarding set analysis within set analysis. Hope that you can help me.
The data I am using has amounts per customer, stamped every month. For instance:
| CUSTOMER | MONTH | AMOUNT |
|---|
| C-01 | 201507 | 6 |
| C-01 | 201508 | 12 |
| C-01 | 201509 | 18 |
| C-02 | 201512 | 100 |
| C-03 | 201511 | 20 |
| C-03 | 201512 | 40 |
I would like to have a table that shows customer and the last amount, given a certain month. For instance, if I choose 201512:
| CUSTOMER | AMOUNT |
|---|
| C-01 | 18 |
| C-02 | 100 |
| C-03 | 40 |
I have come up with this solution:
=Sum({$<Indicator={$(vIndicator)},Type={'Realisation'},YEARMONTH={"$(=Max({$<Indicator={$(vIndicator)},Type={'Realisation'},YEAR={$(vTHIS_YEAR)},MONTH={'<=$(vTHIS_MONTH)'}>}YEARMONTH))"}>}AMOUNT)
Problem with that is that it only shows values for the customers that have 201512 as the last available month:
| CUSTOMER | AMOUNT |
|---|
| C-01 | 0 |
| C-02 | 100 |
| C-03 | 40 |
This makes sense, since I don't break down to customer. However, I don't know how and where to add the customer to the expression.
Can you please help me find the right syntax for the last step?
Regards, Janneke.
