Creating a new field in the script using massive calculations from several fields

[alec1982]

Hi,

I have the following table that shows a schedule of payements

ID     Payement Date     Amount     Most Recent payement

1     3/1/2012                  800           1000

1     5/1/2012                  1000         1000  

1     6/1/2012                  1000         1000

1     10/1/2012                 1000        1000  

2     1/1/2010                   300           500

2     6/1/2012                   500           500

2     8/1/2012                    500          500

3     4/1/2011                    300         1000

3     5/1/2012                   1000        1000

3     12/1/2012                  1000       1000

I need to calculate a new field to show the most recent payement.(The red field ).

the calculation should look at today's date and compare it to the the date field(Month/Year) and then put the amount of that row in a new field next to the ID.

I Am not sure how to do it..and i have' been thinking about it all day.

I appreciate any help..

Thxs,

[alec1982]

In other words,

I don't mind having a table with

ID     Date          Most recent Payement

1      6/1/2012    1000

2      6/1/2012     500

3      5/1/2012     1000

Thxs,

[Jason_Michaelides]

Does the answer need to be based on current selections?

[alec1982]

Hi,

Not necessarily.

If i could get it without current selection then it is fine.

I also have a field that i would like to filter by.

The filter field name is xyz and the value that i am looking to filter by is A.

Thanks so much for your help,

Alec

[Jason_Michaelides]

If that filter is user-applied then you can't do it in the script.

Have you tried using FirstSortedValue()? I think you'll need to use Aggr() in the second parameter.

Jason

[alec1982]

The filter is not user- applied.

Not sure how to do that.

Thxs,

[Jason_Michaelides]

Try:

LOAD

   ID,

   Date,

   LastValue(Payment) AS LastPayment

Resident PreviousTable

GROUP BY ID,Date

ORDER BY ID, Date;

Hope this helps,

Jason

[Jason_Michaelides]

Actually I think that's slightly wrong:

LOAD

   ID,

   LastValue(Date) AS Date,

   LastValue(Payment) AS LastPayment

Resident PreviousTable

GROUP BY ID

ORDER BY ID, Date;

[alec1982]

Hi,

Last value will bring the last date value which is 10/1/2012 in ID 1

I am looking for the most recent which should bring 6/1/2012.

Thxs

[Jason_Michaelides]

ORDER BY ID, Date DESC