Filtering records in an expression by the max date

Tue, 13 Nov 2012 19:46:58 GMT

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!

Re: Filtering records in an expression by the max date

swuehl — Tue, 13 Nov 2012 20:12:07 GMT

Try a calculated dimension like

=aggr(FirstSortedValue(EventStatus, -EventDate), RecordID)

and as expression

=count(RecordID)

Re: Filtering records in an expression by the max date

Gysbert_Wassenaar — Tue, 13 Nov 2012 20:18:27 GMT

See attached qvw

topic Re: Filtering records in an expression by the max date in QlikView

Filtering records in an expression by the max date

Re: Filtering records in an expression by the max date

Re: Filtering records in an expression by the max date