Max of date

[#1]

Lease ID	SubID	Subspace Expiration Date	ExpID	Amount	Expense Type	Amount* Frequency	pay_frequency
				2459.62		29515.44
446	510	5/9/2016	3338	1016.37	Base Rent Post Free Rent	12196.44	Monthly
446	669	5/9/2017	4825	1016.37	Base Rent Post Free Rent	12196.44	Monthly
446	510	5/9/2016	3340	213.44	Occupancy Tax / Sales Tax	2561.28	Monthly
446	669	5/9/2017	4827	213.44	Occupancy Tax / Sales Tax	2561.28	Monthly

=Sum(Aggr((Max([Amount of Expense]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1)),SubID,[Expense Type],ExpID))

For the above expression is working now iI have to add the if statement to pick only the max of date.So ex : it should pick the date 5/9/2017.

How can I add that If statement to the above expression.

Thank you very much.

[#2]

Expense Type and also I want to use this script in the Text object so aggr Dimension will not work right ?

[#3]

Sum(Aggr(Max([Amount]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1), [Subspace ID],[Expense Type],[Expense ID]))

[sunny_talwar]

Try this Expression (in both your chart and text box object:

=Sum({<[Expense ID] = {"=Max(TOTAL <[Subspace ID]> [Subspace Expiration Date]) = [Subspace Expiration Date]"}>} Aggr(

  Max({<[Expense ID] = {"=Max(TOTAL <[Subspace ID]> [Subspace Expiration Date]) = [Subspace Expiration Date]"}>}[Amount]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1),

  [Subspace ID],[Expense Type],[Expense ID]))