Set Analysis help

[Joaquin_Lazaro]

Hello:

I've this data:

Offer No.	Order	Status	Date D/M/Y	Amount
1	1	Requested	01/01/2013	0
1	2	Delivered	01/02/2013	200
1	3	Accepted	01/05/2013	200
2	1	Requested	01/01/2013	0
2	2	Delivered	01/02/2013	1000
2	3	Lost	01/05/2013	1000
3	1	Requested	01/01/2013	0
3	2	Delivered	01/02/2013	1000
3	3	Delivered	01/05/2013	800
3	4	Delivered	01/07/2013	750
3	5	Accepted	01/08/2013	750

and this expressions:

sum( { < Status = {'Delivered'} > }  Amount )

sum( { < Status = {'Accepted'}  > }  Amount )

sum( { < Status = {'Lost'} > }  Amount )

the results are the expected.

The problem is that I need to change the "Delivered" expresion to get only the last version of all status of each order.

Now for the Offer No 3, delivered amount is 2550 but the correct sum is 750

Please, could you help me to correct this expression: sum( { < Status = {'Delivered'} > }  Amount )

Thank you

[swuehl · Accepted solution]

Try

FirstSortedValue({<Status = {'Delivered'}>}Amount, -[Date D/M/Y])

in a table with dimension  Offer No. or, if you need a total line for that expression:

sum(aggr(

     FirstSortedValue({<Status = {'Delivered'}>}Amount, -[Date D/M/Y])

,[Offer No.]))

[Joaquin_Lazaro]

Thank you very much, swuehl

I just used both with and without totals