Latest record per calendar selection

[zekazak]

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

[swuehl · Accepted solution]

Try creating a straight table chart with dimension Order, then create two expressions:

=Timestamp(max([Event Date]))

and

=FirstSortedValue(Status, -[Event Date])

[zekazak]

Yep, It works. Thank you.