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.

[sunny_talwar]

What if you do this:

FirstSortedValue(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), -[Subsapce Expiration Date])

[#3]

Nope - It didnt work.It returned blank.

[sunny_talwar]

Do you have a sample you can share?

[#5]

sunindia Please find attAched Excel and QVW.Thank you very much.

[sunny_talwar]

Do you want to see it against all the dimensions?

[#7]

I want it againt Lease ID,Subspace ID,Expense ID

[sunny_talwar]

This?

Dimensions

1) =[Lease ID]

2) =[Subspace ID]

3) =Aggr(If(Max(TOTAL <[Subspace ID]> [Subspace Expiration Date]) = [Subspace Expiration Date], [Expense ID]), [Subspace Expiration Date], [Expense ID])

Expression

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]))

[#9]

Ya,but what should I do If I have to add more dimensions ?Thank you

[sunny_talwar]

Which dimensions are you looking to add?