Hello all,
I've got the situation where I need to get the latest status of the order depending on a calendar dimension selected.
My source table look like this:
| Event Date | Order | Status |
|---|
| 01/01/2013 12:00 | 1111 | Status1 |
| 01/01/2013 13:00 | 1111 | Status2 |
| 02/01/2013 12:00 | 1111 | Status3 |
| 02/01/2013 13:00 | 1111 | Status4 |
Then I have a calendar which is linked to Event_Date (Day, Month, Year).
So if user picks the day of 01/01/2013 the result should be like this in the separate table object:
| Event Date | Order | Status |
|---|
| 01/01/2013 13:00 | 1111 | Status2 |
if user select a month 01/2013, the result should be like this:
| Event Date | Order | Status |
|---|
| 02/012013 13:00 | 1111 | Status4 |
The same result should be also when user doesn't select anything. Just the latest status should be displayed.
My current formula is like this but it works only when I select one specific order id. Once I deselect the order_id to display the data for all orders I'm receiving just null.
=aggr(only({<[Event Date]={"$(=aggr(max(date([Event_Date],'YYYY-MM-DD hh:mm:ss')),Order))"}>} [Status]),Order)
Any help would be much appreciated. Thank you in advance.
~Sergejs
