Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be like this
FirstSortedValue(Expense, SubExpDate)
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))))
I won't know and understand much just by looking at your expression... I would suggest you to share a sample
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 |