Aggregate using latest value

[#1]

Hi..

Is there a way to do aggregation using the latest value. I have a table list statues of orders by date. what I need is to count the number of orders at the end of the selected period according to their latest status.

So Suppose I have this table

Order_no	Status	Date
1	Received	01/01/2016
1	Under process	02/01/2016
1	Completed	02/01/2016
2	Received	02/01/2016
2	Under process	02/01/2016
2	Under process	03/01/2016
3	Received	02/01/2016
3	Completed	03/01/2016

if my date filter selected all dates less than or equal 03/01/2016 I would expect to get

Received 0>>>>> (for non)

Completed 2 >>>>> (for 1 and 3)

Under process 1 >>>>> (for 2)

however

if my date filter selected all dates less than or equal 02/01/2016 I would expect to get

Received 2>>>>> (for 2 and 3)

Completed 1 >>>>> (for 2)

Under process 2 >>>>> (for 1 and 2)

I have tried to use this code but it gave me an error

=Count({$<Date = {'$(=aggr(max(Date),Order_no))'} >} distinct Order_no)

any help from you

[MayilVahanan]

Hi,

Are you looking like this?

[#3]

sorry I cannot open the file as I have the free version right now. could you please post the script over here.