Filtering records in an expression by the max date
Hi all. So I have a table of historical events (tickets) that have a many-to-one relationship with my main table of records. I want to do a simple pie-chart distribution of the status of the events, but I only want to include the most recent event for each record. So my table looks like this:
LOAD * INLINE
[RecordID, EventID, EventStatus, EventDate
1, A, Open, 1/1/2012
1, B, Closed, 1/1/2011
1, C, Cancelled, 1/1/2010
2, D, Closed, 1/1/2012
2, E, Cancelled, 1/1/2011
2, F, Closed, 1/1/2010
3, G, Closed, 1/1/2012
3, H, Closed, 1/1/2011
3, I, Closed, 1/1/2010];
So I want my pie chart to only take the most recent event for each RecordID. So I would expect the pie chart to display an Open slice with a count of 1, and a Closed slice with a count of 2.
Can anyone help me with the expression for this chart? Thanks!
