Expression/Logic help please

[apthansh]

ID SubID SubExpDate      Expense

1   AB      4/30/2016          5000

1   BC      4/30/2017          6000

Here one ID has multiple SubID's but I only want to calculate the Expense for ID where SubExpDate has the most recent date.

Here sum(Expense) should be 5000 and disregard the SubID BC.

Can we do that ?

Thank you very much.

[sunny_talwar]

May be like this

FirstSortedValue(Expense, SubExpDate)

[apthansh]

Thank you Sunny...but this is how my expression is

(Sum(if([Lease Type] = 'Our Subtenant',

   Aggr((Max([Amount of Expense]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1)),%SubspaceKey,[Expense Type],%ExpenseKey))*-1))

+

(Sum(if([Lease Type] <> 'Our Subtenant',

   Aggr((Max([Amount of Expense]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1)),%SubspaceKey,[Expense Type],%ExpenseKey))))

[sunny_talwar]

I won't know and understand much just by looking at your expression... I would suggest you to share a sample

[apthansh]

Agree...let me put it in another way...

Can I add a logic - if 1 id has Multiple SUB ID's and among those sub Id's if one expires in the next 12 months and other is Starting within the next 12 month can I pull the second record.Here i WANT TO PULL THE sUB iD 845.How can I put it in script level..

ID	status	Sub_ID	Start	Expire
441	Active	505	11/22/2010	4/30/2017
441	Active	845	5/1/2017	5/31/2021