Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
Nope - It didnt work.It returned blank.
Do you have a sample you can share?
sunindia Please find attAched Excel and QVW.Thank you very much.
Do you want to see it against all the dimensions?
I want it againt Lease ID,Subspace ID,Expense ID
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]))
Ya,but what should I do If I have to add more dimensions ?Thank you
Which dimensions are you looking to add?