Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try a calculated dimension like
=aggr(FirstSortedValue(EventStatus, -EventDate), RecordID)
and as expression
=count(RecordID)
See attached qvw